update触发器USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'trTitles_Upd' AND type = 'TR') DROP TRIGGER trTitles_Upd GO --创建触发器(如果更新的是price,则显示相应信息) create TRIGGER trTitles_Upd ON titles FOR update asDECLARE @chvMsg varchar(255) ,@chvTitleID varchar(6) ,@mnyOldPrice MONEY,@mnyNewPrice MONEYDECLARE cuPriceChange CURSOR FOR SELECT d.title_id, d.price, i.price FROM deleted d INNER JOIN inserted i ON d.title_id = i.title_id IF update(price) begin OPEN cuPriceChange FETCH NEXT FROM cuPriceChange INTO @chvTitleID, @mnyOldPrice, @mnyNewPrice
WHILE (@@fetch_status <> -1)
begin SELECT @chvMsg = 'The price of title ' + @chvTitleID + ' has changed from' + ' ' + CONVERT ( VARCHAR(10), @mnyOldPrice) + ' to ' + CONVERT ( VARCHAR(10), @mnyNewPrice) + ' on ' + CONVERT ( VARCHAR(30), getdate()) print @chvMsg FETCH NEXT FROM cuPriceChange INTO @chvTitleID, @mnyOldPrice, @mnyNewPrice Set @chvMsg = '' end deallocate cuPriceChange end --更新price update titles set price=20 where price<11 --更新royalty update titles set royalty=20 where royalty is null
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trTitles_Upd' AND type = 'TR')
DROP TRIGGER trTitles_Upd
GO
--创建触发器(如果更新的是price,则显示相应信息)
create TRIGGER trTitles_Upd ON titles
FOR update
asDECLARE @chvMsg varchar(255) ,@chvTitleID varchar(6) ,@mnyOldPrice MONEY,@mnyNewPrice MONEYDECLARE cuPriceChange CURSOR
FOR
SELECT d.title_id, d.price, i.price
FROM deleted d INNER JOIN inserted i ON d.title_id = i.title_id
IF update(price)
begin
OPEN cuPriceChange
FETCH NEXT FROM cuPriceChange INTO @chvTitleID, @mnyOldPrice, @mnyNewPrice
WHILE (@@fetch_status <> -1)
begin
SELECT @chvMsg = 'The price of title ' + @chvTitleID
+ ' has changed from'
+ ' ' + CONVERT ( VARCHAR(10), @mnyOldPrice)
+ ' to ' + CONVERT ( VARCHAR(10), @mnyNewPrice)
+ ' on ' + CONVERT ( VARCHAR(30), getdate())
print @chvMsg
FETCH NEXT FROM cuPriceChange INTO @chvTitleID, @mnyOldPrice, @mnyNewPrice
Set @chvMsg = ''
end
deallocate cuPriceChange
end
--更新price
update titles set price=20 where price<11
--更新royalty
update titles set royalty=20 where royalty is null