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
|