Delete trigger issue with ON DELETE CASCADE in sql server

Question

I have two Tables in Sql server

Table A: Id UNIQUEIDENTIFIER NOT NULL
Table B: Id UNIQUEIDENTIFIER NOT NULL
         AId UNIQUEIDENTIFIER NOT NULL,ON DELETE CASCADE (This is Id of Table A)

(Notice B has column AId that references A with ON DELETE CASCADE option). B also has a delete trigger like

CREATETRIGGER B_Delete_Trigger
ON [dbo].[B]
FOR DELETE
AS
SELECT * FROM DELETED AS B INNER JOIN A ON B.AId=A.Id

Now when I perform delete operation on Table B, above trigger returns rows, but when on Table A nothing is returned.

As much I know SQL Server first delete records from Child Table(B in this case) and then from parent(A in this case), so above trigger should return some record in both cases.

Please, tell me how to access parents records, Or my observation is wrong.

Answer 1

Actually when you call this trigger on table A. The execution sequence is

1. DELETE Record from child table B.
2. DELETE Record from Parent table A.
3. Trigger call

And SELECT * FROM DELETED contains only data from A table.