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

Cascade Update Triggers

   <source lang="sql">

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

CREATE TRIGGER FOR INSERT, UPDATE

   <source lang="sql">

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

create TRIGGER for update

   <source lang="sql">

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

The trigger rolls back any T-SQL that changes the advance column.

   <source lang="sql">

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