Table Creation Script for Parent and Child with FOREIGN KEY Constraint and INSTEAD OF Trigger : Create Table « Table « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Table
3. Create Table

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 REFERENCES Parent (ID) ON DELETE CASCADE
3> )
4> GO
1> CREATE TRIGGER trd_Parent ON Parent INSTEAD OF DELETE
2> AS
3> IF @@ROWCOUNT = 0
4>   RETURN
5> PRINT 'Inside Parent trigger.'
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> --INSTEAD OF Trigger that Duplicates DELETE Action
2> ALTER TRIGGER trd_Parent ON Parent INSTEAD OF DELETE
3> AS
4> IF @@ROWCOUNT = 0
5>   RETURN
6> PRINT 'Inside Parent trigger.'
7> DELETE P
8> FROM
9>     Parent P
10>    JOIN
11>      deleted D ON D.ID = P.ID
12>  GO
1> -- AFTER Trigger on Child Table
2> CREATE TRIGGER trd_Child ON Child AFTER DELETE
3> AS
4> IF @@ROWCOUNT = 0
5>   RETURN
6> PRINT 'Inside Child trigger.'
7> GO
1>
2> drop table child
3> drop table parent
4> GO
1>