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

Материал из SQL эксперт
Версия от 13:24, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

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

   <source lang="sql">

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</source>


CREATE TRIGGER FOR DELETE

   <source lang="sql">

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</source>


CREATE TRIGGER FOR INSERT, UPDATE, DELETE

   <source lang="sql">

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</source>


Trigger for delete and deleted table

   <source lang="sql">

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></source>