DECLARE 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