SQL Server/T-SQL Tutorial/Trigger/Trigger for Delete

After its data has been deleted, the trigger deletes corresponding rows from the sales and discounts tables.

4>
5> CREATE TABLE stores(
6>    stor_id        char(4)           NOT NULL,
7>    stor_name      varchar(40)           NULL,
8>    stor_address   varchar(40)           NULL,
9>    city           varchar(20)           NULL,
10>    state          char(2)               NULL,
11>    zip            char(5)               NULL
12> )
13> GO
1> insert stores values("1","B","567 Ave.","Tustin",   "CA","92789")
2> insert stores values("2","N","577 St.", "Los Gatos","CA","96745")
3> insert stores values("3","T","679 St.", "Portland", "OR","89076")
4> insert stores values("4","F","89  St.", "Fremont",  "CA","90019")
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE discounts(
4>    discounttype   varchar(40)       NOT NULL,
5>    stor_id        char(4) NULL              ,
6>    lowqty         smallint              NULL,
7>    highqty        smallint              NULL,
8>    discount       dec(4,2)          NOT NULL
9> )
10> GO
1>
2> insert discounts values("Initial Customer",  NULL,   NULL, NULL, 10.5)
3> insert discounts values("Volume Discount",   NULL,   100,  1000, 6.7)
4> insert discounts values("Customer Discount", "8042", NULL, NULL, 5.0)
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>     CREATE TRIGGER myTrigger ON stores
3>     FOR DELETE
4>     AS
5>     DECLARE @intRowCount int
6>     SELECT @intRowCount = @@RowCount
7>     IF @intRowCount > 0
8>         BEGIN
9>             DELETE sales
10>                 WHERE stor_id IN (SELECT stor_id FROM deleted)
11>             DELETE discounts
12>                 WHERE stor_id IN (SELECT stor_id FROM deleted)
13>     END
14>     GO
1>
2>
3> drop TRIGGER myTrigger;
4> drop table stores;
5> drop table discounts;
6> GO

CREATE TRIGGER FOR DELETE

2>
3> CREATE TABLE Customers (
4>      CustomerID nchar (5) NOT NULL ,
5>      CompanyName nvarchar (40) NOT NULL ,
6>      ContactName nvarchar (30) NULL ,
7>      ContactTitle nvarchar (30) NULL ,
8>      Address nvarchar (60) NULL ,
9>      City nvarchar (15) NULL ,
10>     Region nvarchar (15) NULL ,
11>     PostalCode nvarchar (10) NULL ,
12>     Country nvarchar (15) NULL ,
13>     Phone nvarchar (24) NULL ,
14>     Fax nvarchar (24) NULL
15> )
16> GO
1>
2> INSERT Customers VALUES("1","A","Maria",    "Sales",  "Str. 57", "Berlin"    ,NULL,"12209", "Germany","111-1111111","111-1111111")
3> INSERT Customers VALUES("2","M","Joe",      "Owner",  "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL)
4> INSERT Customers VALUES("3","H","Thomas",   "Sales",  "Sq.  111","London"    ,NULL,"1D00P", "UK",     "(444) 444-4444","(444) 444-4444")
5> INSERT Customers VALUES("4","B","Berg",     "Order",  "Blv    8","Toronto"   ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55")
6> INSERT Customers VALUES("5","S","Moos",     "Sales",  "Fort  57","New York"  ,NULL,"68306", "Germany","6666-66666","6666-77777")
7> INSERT Customers VALUES("6","F","Cite",     "Manager","24      ","Dalles"    ,NULL,"67000", "France", "88.60.15.31","88.60.15.32")
8> INSERT Customers VALUES("7","C","Sommer",   "Owner",  "Araq, 67","Paris"     ,NULL,"28023", "Spain",  "(91) 555 22 82","(91) 555 91 99")
9> INSERT Customers VALUES("8","P","Leb",      "Owner",  "12      ","Beijing"   ,NULL,"13008", "France", "91.24.45.40","91.24.45.41")
10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745")
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 rows affected)
1>
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>
2>    CREATE TRIGGER CustomerHasOrders
3>       ON Customers
4>       FOR DELETE
5>    AS
6>       IF EXISTS
7>          (
8>           SELECT "True"
9>           FROM Deleted d
10>           JOIN Orders o
11>              ON d.CustomerID = o.CustomerID
12>          )
13>
14>          BEGIN
15>             RAISERROR("Customer has Order History. Delete failed!", 16,1)
16>             ROLLBACK TRAN
17>          END
18> GO
1>    DELETE Customers
2>       WHERE CustomerID = "WHITC"
3> GO
(0 rows affected)
1>
2>
3> drop TRIGGER CustomerHasOrders;
4> GO
1>
2> drop table Customers;
3> GO
1> drop table orders;
2> GO

CREATE TRIGGER FOR INSERT, UPDATE, DELETE

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 and deleted table

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>