1. In the case where the book below the current average price is increased by 50%
2 For books above or equal to the current average price, it is 25%.
code show as below:
Declare Title_Update Cursor
For select title_id, Price from Titles
For update
Go
Local variable
Declare @AVG_PRICE MONEY, @ title_id tid, @ price Money
Open title_update
Begin TRAN
Calculate average book price
SELECT @ avg_price = avg (price) from titles Holdlock
Fetch title_update @ title_id, @ price
While @@ sqlstatus! = 2
Begin
IF @@ SQLSTATUS = 1
Begin
Rollback TRAN
Raiserror 21001 "Fetch Failed in Cursor"
Close Title_Update
DEAALLOCATE CURSOR TITLE_UPDATE
Return
end
IF @Price <@AVG_PRICE
Raise a price of 50%
Update Titles set price = price * $ 1.50
Where current of title_update
Else
2.5% off
Update Titles set price = price * $ .75
Where current of title_update
IF @@ error! = 0
Begin
Rollback TRAN
Raiserror 22001 "Update Failed"
Close Title_Update
DEAALLOCATE CURSOR TITLE_UPDATE
Return
end
Fetch title_update @ title_id, @ price
end
Commit
Close Title_Update
DEAALLOCATE CURSOR TITLE_UPDATE
Go
