Trigger for delete using deleted table : Deleted table « Trigger « SQL Server / T-SQL Tutorial

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

5> CREATE TABLE OrderDetails(
6> ID       int NOT NULL,
7> PartID   int NOT NULL,
8> Quantity int NOT NULL);
9> 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> 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>
3> CREATE TABLE Employees
4> (orderid int NOT NULL,
5>  mgrid int NULL,
6>  empname varchar(25) NOT NULL,
7>  salary money NOT NULL);
8> GO
1>
2>
3> CREATE TRIGGER myTrigger ON Employees FOR DELETE
4> AS
5> IF EXISTS(SELECT *
6>           FROM
7>               OrderDetails AS OD
8>             JOIN
9>               deleted      AS D ON OD.id = D.orderid)
10> BEGIN
11>   RAISERROR('The Employee you are trying to delete have related rows
12~             in OrderDetails.  TRANSACTION rolled back.', 10, 1)
13>   ROLLBACK TRANSACTION
14> END
15> GO
1>
2> drop table OrderDetails;
3> GO
1>
2>