After its data has been deleted, the trigger deletes corresponding rows from the sales and discounts tables. : Trigger for Delete « Trigger « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Trigger
3. Trigger for Delete

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