Trigger for delete and deleted table : Trigger for Delete « Trigger « SQL Server / T-SQL Tutorial

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

9> CREATE TABLE Employees
10> (orderid int NOT NULL,
11>  mgrid int NULL,
12>  empname varchar(25) NOT NULL,
13>  salary money NOT NULL);
14> GO
1>
2> CREATE TABLE OrderDetails(
3> ID       int NOT NULL,
4> PartID   int NOT NULL,
5> Quantity int NOT NULL);
6> GO
1>
2> INSERT INTO OrderDetails VALUES(10001, 11, 12)
3> INSERT INTO OrderDetails VALUES(10001, 42, 10)
4> INSERT INTO OrderDetails VALUES(10001, 72, 5)
5> INSERT INTO OrderDetails VALUES(10002, 14, 9)
6> INSERT INTO OrderDetails VALUES(10002, 51, 40)
7> INSERT INTO OrderDetails VALUES(10003, 41, 10)
8> INSERT INTO OrderDetails VALUES(10003, 61, 35)
9> INSERT INTO OrderDetails VALUES(10003, 65, 15)
10>
11> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2> CREATE TRIGGER trg_d_orders_on_delete_cascade ON Employees FOR DELETE
3> AS
4> DELETE FROM OrderDetails
5> FROM
6>     OrderDetails AS OD
7>   JOIN
8>     deleted      AS D ON OD.id = D.orderid
9> GO
1>
2>
3>
4> drop table OrderDetails;
5> GO
1>
2> drop table Employees;
3> GO
1>
2>