Trigger Scripts for Cascading DELETEs : Trigger « Trigger « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Trigger
3. Trigger

3>
4> CREATE TABLE Parent(
5>   ID int NOT NULL PRIMARY KEY
6> )
7> GO
1> CREATE TABLE Child(
2>   ID int NOT NULL PRIMARY KEY
3>                   REFERENCES Parent (ID)
4>                   ON DELETE CASCADE
5> )
6> GO
1> CREATE TABLE GrandChild(
2>   ID int NOT NULL PRIMARY KEY
3>                   REFERENCES Child (ID)
4>                   ON DELETE CASCADE
5> )
6> GO
1> INSERT Parent VALUES (1)
2> INSERT Parent VALUES (2)
3> INSERT Parent VALUES (3)
4> INSERT Parent VALUES (4)
5> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1> INSERT Child VALUES (1)
2> INSERT Child VALUES (2)
3> INSERT Child VALUES (3)
4> INSERT Child VALUES (4)
5> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1> INSERT GrandChild VALUES (1)
2> INSERT GrandChild VALUES (2)
3> INSERT GrandChild VALUES (3)
4> INSERT GrandChild VALUES (4)
5> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1> CREATE TRIGGER trd_Parent ON Parent AFTER DELETE
2> AS
3> IF @@ROWCOUNT = 0
4>   RETURN
5> PRINT 'Inside Parent trigger.'
6> GO
1> CREATE TRIGGER trd_Child ON Child AFTER DELETE
2> AS
3> IF @@ROWCOUNT = 0
4>   RETURN
5> PRINT 'Inside Child trigger.'
6> GO
1> CREATE TRIGGER trd_GrandChild ON GrandChild AFTER DELETE
2> AS
3> IF @@ROWCOUNT = 0
4>   RETURN
5> PRINT 'Inside GrandChild trigger.'
6> GO
1> --Firing the Cascaded DELETE
2> DELETE Parent
3> WHERE
4>   ID BETWEEN 2 AND 3
5>
6> drop trigger trd_parent
7> drop trigger trd_child
8> drop trigger trd_grandchild
9> drop table grandchild
10> drop table child
11> drop table parent
12> GO
Inside GrandChild trigger.
Inside Child trigger.

(2 rows affected)
Inside Parent trigger.