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>