SQL Server/T-SQL Tutorial/Trigger/Trigger for update
Содержание
Cascade Update Triggers
3>
4> CREATE TABLE stores(
5> stor_id char(4) NOT NULL,
6> stor_name varchar(40) NULL,
7> stor_address varchar(40) NULL,
8> city varchar(20) NULL,
9> state char(2) NULL,
10> zip char(5) NULL
11> )
12> 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>
3> CREATE TRIGGER myTrigger ON stores
4> FOR UPDATE
5> AS
6> DECLARE @intRowCount int
7> SELECT @intRowCount = @@RowCount
8> IF @intRowCount > 1
9> BEGIN
10> IF UPDATE(stor_id)
11> ROLLBACK TRANSACTION
12> END
13> ELSE
14> IF @intRowCount = 1
15> BEGIN
16> IF UPDATE(stor_id)
17> BEGIN
18> UPDATE sales
19> SET sales.stor_id = (SELECT stor_id FROM inserted)
20> FROM sales INNER JOIN deleted
21> ON sales.stor_id = deleted.stor_id
22> UPDATE discounts
23> SET discounts.stor_id = (SELECT stor_id FROM inserted)
24> FROM discounts INNER JOIN deleted
25> ON discounts.stor_id = deleted.stor_id
26> END
27> END
28> GO
1>
2> drop TRIGGER myTrigger;
3> drop table sales;
4> drop table discounts;
5> GO
CREATE TRIGGER FOR INSERT, UPDATE
6>
7> CREATE TABLE Customers (
8> CustomerID nchar (5) NOT NULL ,
9> CompanyName nvarchar (40) NOT NULL ,
10> ContactName nvarchar (30) NULL ,
11> ContactTitle nvarchar (30) NULL ,
12> Address nvarchar (60) NULL ,
13> City nvarchar (15) NULL ,
14> Region nvarchar (15) NULL ,
15> PostalCode nvarchar (10) NULL ,
16> Country nvarchar (15) NULL ,
17> Phone nvarchar (24) NULL ,
18> Fax nvarchar (24) NULL
19> )
20> 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> GO
1>
2> CREATE TRIGGER OrderHasCustomer
3> ON Orders
4> FOR INSERT, UPDATE
5> AS
6> IF EXISTS
7> (
8> SELECT "True"
9> FROM Inserted i
10> LEFT JOIN Customers c
11> ON i.CustomerID = c.CustomerID
12> WHERE c.CustomerID IS NULL
13> )
14> BEGIN
15> RAISERROR("Order Must Have Valid CustomerID",16,1)
16> ROLLBACK TRAN
17> END
18> GO
1>
2> drop TRIGGER OrderHasCustomer;
3> GO
1>
2> drop table Customers;
3> GO
1> drop table orders;
2> GO
create TRIGGER for update
5>
6> CREATE TABLE Products (
7> ProductID int NOT NULL ,
8> ProductName nvarchar (40) NOT NULL ,
9> SupplierID int NULL ,
10> CategoryID int NULL ,
11> QuantityPerUnit nvarchar (20) NULL ,
12> UnitPrice money NULL,
13> UnitsInStock smallint NULL,
14> UnitsOnOrder smallint NULL,
15> ReorderLevel smallint NULL,
16> Discontinued bit NOT NULL
17> )
18> GO
1> INSERT Products VALUES(1,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6)
2> INSERT Products VALUES(2,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7)
3> INSERT Products VALUES(3,"R",17,8,"24 - 777 g jars",17,171,0,5,0)
4> INSERT Products VALUES(4,"L",4,7,"5 kg pkg.",10,4,20,5,0)
5> INSERT Products VALUES(5,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0)
6> INSERT Products VALUES(6,"L",23,1,"500 ml",18,57,1,20,0)
7> INSERT Products VALUES(7,"O",12,2,"12 boxes",13,23,0,15,0)
8> go
(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 ProductIsRationed
3> ON Products
4> FOR UPDATE
5> AS
6> IF UPDATE(UnitsInStock)
7> BEGIN
8> IF EXISTS
9> (
10> SELECT "True"
11> FROM Inserted i
12> JOIN Deleted d
13> ON i.ProductID = d.ProductID
14> WHERE (d.UnitsInStock - i.UnitsInStock) > d.UnitsInStock / 2
15> AND d.UnitsInStock - i.UnitsInStock > 0
16> )
17> BEGIN
18> RAISERROR("Cannot reduce stock by more than 50%% at once.",16,1)
19> ROLLBACK TRAN
20> END
21> END
22> GO
1>
2> drop TRIGGER ProductIsRationed;
3> GO
1>
2> drop table Products;
3> GO
The trigger rolls back any T-SQL that changes the advance column.
3>
4>
5> CREATE TABLE titles(
6> title_id varchar(20),
7> title varchar(80) NOT NULL,
8> type char(12) NOT NULL,
9> pub_id char(4) NULL,
10> price money NULL,
11> advance money NULL,
12> royalty int NULL,
13> ytd_sales int NULL,
14> notes varchar(200) NULL,
15> pubdate datetime NOT NULL
16> )
17> GO
1>
2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
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 TRIGGER trTitles_Upd ON titles
4> FOR UPDATE
5> AS
6> IF update(advance)
7> ROLLBACK TRANSACTION
8> RETURN
9>
10> drop TRIGGER trTitles_Upd;
11> GO
1>
2> drop TABLE titles;
3> GO