Mega Code Archive

 

Categories / MSSQL Tutorial / Trigger

 

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