CODEWITHSUNDEEP
sql-servertriggerscascading-deletes
user369182
user369182

Reputation: 2085

Delete trigger issue with ON DELETE CASCADE in sql server

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.

Upvotes: 3

Views: 7535

Answers (4)

Habib
Habib

Reputation: 21

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.

Upvotes: 2

Jed
Jed

Reputation: 153

A "FOR" trigger is equivalent to an "AFTER" trigger. The trigger fires after the delete operation has completed. Therefore, by the time the delete trigger executes for B, the record in table A is already gone. As far as I know, there is no simple way to get access to the record in table A.

Upvotes: 1

Elias Hossain
Elias Hossain

Reputation: 4469

Below items should be noted:

  • In case of Parent-Child relational table, always try to create trigger on parent table to perform ON DELETE CASCADE
  • Child table can't CASCADE with Parent.
  • ON DELETE CASCADE means: when you delete item from Parent table, all items will be deleted from child table with deleted reference.

Hope you've got me, thanks for your time.

Upvotes: 0

Ric
Ric

Reputation: 13248

Shouldn't the on delete cascade be on the parent table?

The purpose of this is that once the parent is deleted, providing there are other tables referencing the parent of course (via FK constraints), then all the child records will be deleted too. It looks like you have it the wrong way round??

MSDN:

http://msdn.microsoft.com/en-us/library/aa933119(v=sql.80).aspx

Upvotes: 0

Related Questions