Using SQL Server Create an INSTEAD OF trigger named Products_UPDATE that error c

Question

Using SQL Server

Create an INSTEAD OF trigger named Products_UPDATE that error checks the updated value of DiscountPercent column of the Products table. If the discount percent is less than 0 or greater than 100, the trigger should throw an error. If the discount percent is greater than 0 but less than or equal to 1, this trigger should update the new discount percent by multiplying it by 100. That way, a discount percent of .2 becomes 20. If the discount percent is greater than 1 but less than 100, the trigger should update the new discount percent normally.

Test this trigger with the following updates

UPDATE Products
SET DiscountPercent = -10.0
WHERE ProductID = 1;

UPDATE Products
SET DiscountPercent = 120.0
WHERE ProductID = 1;

UPDATE Products
SET DiscountPercent = .25
WHERE ProductID = 1;

UPDATE Products
SET DiscountPercent = 50
WHERE ProductID = 1;

Explanation / Answer

DROP TRIGGER IF EXISTS products_update;

CREATE TRIGGER products_update
AFTER UPDATE ON Products
FOR EACH ROW
BEGIN
IF NEW.discount_percent >100 THEN
SIGNAL SQLSTATE 'HY000'
    set message_text =
        'the discount percent cannot be greater than 100';
ELSEIF new.discount_percent < 0 THEN
SIGNAL SQLSTATE 'HY000'
    set message_text =
        'the discount percent cannot be less than 0';
ELSEIF NEW.discount_percent < 2 THEN
SET NEW.discount_percent = (NEW.discount_percent * 100);
END IF;

END;

Explanation:

First condition checks if discount price is greater than 100. If this returns true error will be thrown

second one chcks for price less than 0. If this is true,error will be thrown.

If both the above consdiotions are true it means the price is less than 100 and greater than 0.

Hence, as per problem statement, we have to check if price is greater than 0 and less than 2. This is checked in third condition and if this is true,appropriate calculation of discount price is done..

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.