SQL Server/T-SQL Tutorial/Trigger/Deleted table

count for DELETED

2> CREATE TABLE Orders (
3>      OrderID int IDENTITY (1, 1) NOT NULL ,
4>      CustomerID nchar (5) NULL ,
5>      EmployeeID int NULL ,
6>      OrderDate datetime NULL ,
7>      RequiredDate datetime NULL ,
8>      ShippedDate datetime NULL ,
9>      ShipVia int NULL ,
10>     Freight money NULL DEFAULT (0),
11>     ShipName nvarchar (40) NULL ,
12>     ShipAddress nvarchar (60) NULL ,
13>     ShipCity nvarchar (15) NULL ,
14>     ShipRegion nvarchar (15) NULL ,
15>     ShipPostalCode nvarchar (10) NULL ,
16>     ShipCountry nvarchar (15) NULL
17> )
18> GO
1>    CREATE TRIGGER Order2DependsOnOrders
2>       ON Orders
3>       FOR INSERT, UPDATE, DELETE
4>    AS
5>       DECLARE @Count int
6>       SELECT @Count = COUNT(*) FROM DELETED
7>       IF @Count > 0
8>       BEGIN
9>          IF NOT EXISTS
10>             (
11>              SELECT "True"
12>              FROM Deleted d
13>              LEFT JOIN Orders o
14>                 ON d.OrderID = o.OrderID
15>              WHERE o.OrderID IS NULL
16>             )
17>          BEGIN
18>             RAISERROR("Record Exists In Orders Table. Delete Cancelled.",16,1)
19>             ROLLBACK TRAN
20>          END
21>       END
22>       IF @@ERROR != 0
23>          ROLLBACK TRAN
24>       SELECT @Count = COUNT(*) FROM INSERTED
25>       SELECT "Count is " + CONVERT(varchar,@Count) + " Before Delete"
26>       IF @Count > 0
27>       BEGIN
28>          IF EXISTS
29>             (
30>              SELECT "True"
31>              FROM Inserted i
32>              LEFT JOIN Orders o
33>                 ON i.OrderID = o.OrderID
34>              WHERE o.OrderID IS NULL
35>             )
36>          BEGIN
37>             RAISERROR("Inserted Record Must exist in the Orders Table",16,1)
38>             ROLLBACK TRAN
39>          END
40>       END
41>       IF @@ERROR != 0
42>          ROLLBACK TRAN
43>       GO
1>       drop TRIGGER Order2DependsOnOrders;
2>       GO
1>       drop table Orders;
2>       GO

Trigger for delete using 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>