|
3>
4> CREATE TABLE titles(
5> title_id varchar(20),
6> title varchar(80) NOT NULL,
7> type char(12) NOT NULL,
8> pub_id char(4) NULL,
9> price money NULL,
10> advance money NULL,
11> royalty int NULL,
12> ytd_sales int NULL,
13> notes varchar(200) NULL,
14> pubdate datetime NOT NULL
15> )
16> 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> CREATE TRIGGER myTrigger ON titles
3> FOR UPDATE
4> AS
5> DECLARE @chvMsg VARCHAR(255),
6> @chvTitleID VARCHAR(6),
7> @mnyOldPrice MONEY,
8> @mnyNewPrice MONEY
9> DECLARE myCursor CURSOR
10> FOR
11> SELECT d.title_id, d.price, i.price
12> FROM deleted d INNER JOIN inserted i ON d.title_id = i.title_id
13> IF update(price)
14> BEGIN
15> OPEN myCursor
16> FETCH NEXT FROM myCursor INTO
17> @chvTitleID, @mnyOldPrice, @mnyNewPrice
18> WHILE (@@fetch_status <> -1)
19> BEGIN
20> SELECT @chvMsg = 'The price of title ' + @chvTitleID
21> + ' has changed from'
22> + ' ' + CONVERT(VARCHAR(10), @mnyOldPrice)
23> + ' to ' + CONVERT(VARCHAR(10), @mnyNewPrice)
24> + ' on ' +
25> CONVERT(VARCHAR(30), getdate()) + '.'EXEC master..xp_sendmail 'Colleen', @chvMsg
26> FETCH NEXT FROM myCursor
27> INTO @chvTitleID, @mnyOldPrice, @mnyNewPrice
28> SELECT @chvMsg = ''
29> END
30> DEALLOCATE myCursor
31> END
32> RETURN
33> GO
1>
2> drop trigger myTrigger;
3> GO
1>
2> drop table titles;
3> GO
1>
2>
|