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