Question

MUST BE IN MICROSOFT SQL SERVER MANAGEMENT STUDIO !!* CODING

Create a trigger named Products_UPDATE that checks the new value for the DiscountPercent column of the Products table. This trigger should raise an appropriate error if the discount percent is greater than 100 or less than 0. If the new discount percent is between 0 and 1, this trigger should modify the new discount percent by multiplying it by 100. That way, a discount percent of .2 becomes 20. Test this trigger with an appropriate UPDATE statement.

Answer 1

Table created and data inserted using sql server

EVERALF abo.P LQuery1.sql SELECT * FROM Product 100 % Results Product ID Messages Title TV MOBILE 1 10 Price 4000.00 6000.00 Discount Percent 20.00 30.00 12 N

Trigger Created as below

CREATE TRIGGER Products_UPDATE ON Product AFTER UPDATE AS BEGIN DECLARE @Discount_Percent DECIMAL (9,2) DECLARE @Product_ID int Select @Product_ID = ProductID, @Discount_Percent =DiscountPercent from inserted IF (@Discount_Percent) and (@Discount_Percent) <1 UPDATE Product SET DiscountPercent = @Discount_Percent * 100 where ProductID = @Product_ID IF (@Discount_Percent <0 or @Discount_Percent >100) BEGIN ROLLBACK TRAN RAISERROR ('DiscountPercent must be greater than 0 and less than equal to 100', 16, 1) END END 100 % Messages Command(s) completed successfully.

Updated with discount 0.4 for product id 10 whose value is updated as 40 by the trigger as below

UPDATE Product SET Discount Percent =0.4 WHERE ProductID =10; SELECT * FROM Product 100 % Results ProductID Messages Title TV MOBILE 1 10 Price Discount Percent 4000.00 40.00 6000.00 30.00 2 12

This trigger raise an appropriate error if the discount percent is greater than 100

UPDATE Product SET DiscountPercent =101 WHERE ProductID =10; 100 % Messages Msg 50000, Level 16, State 1, Procedure Products_UPDATE, Line 17 Discount Percent must be greater than 0 and less than equal to 100 Msg 3609, Level 16, State 1, Line 1 The transaction ended in the trigger. The batch has been aborted.

This trigger raise an appropriate error if the discount percent is less than 0.

UPDATE Product SET DiscountPercent -- 1 WHERE ProductID =10; 100 % Messages Msg 50000, Level 16, State 1, Procedure Products_UPDATE, Line 17 Discount Percent must be greater than 0 and less than equal to 100 Msg 3609, Level 16, State 1, Line 2 The transaction ended in the trigger. The batch has been aborted.