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





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.










22.1.Trigger
22.1.1.The syntax of the CREATE TRIGGER statement
22.1.2.exits if the price column has not been updated.
22.1.3.Define variables in a trigger
22.1.4.Update table in a trigger
22.1.5.Check @@ROWCOUNT in a trigger
22.1.6.Rollback transaction in a trigger
22.1.7.RAISERROR in trigger
22.1.8.Disable a trigger
22.1.9.Enable a trigger
22.1.10.Check business logic in a trigger
22.1.11.Check record matching in a trigger
22.1.12.Table for INSTEAD OF Trigger for Logical Deletes
22.1.13.INSTEAD OF INSERT trigger for a table
22.1.14.Trigger Scripts for Cascading DELETEs