In this problem, you are required to create an INSTEAD OF trigger called \'tgr_p

Question

In this problem, you are required to create an INSTEAD OF trigger called 'tgr_priceValidation_2' in Products_copy to perform exactly the same function as tgr_priceValidation does in Q1.

THIS IS QUESTION ONE (below):

Q1:

According to the design of Products (and also Products_copy), products may not always have a unit price, meaning a product is allowed to have NULL value in its Unit Price. Further, there is no limit what UnitPrice values could be, including avery large value like $5,000,000.00 or negative values.Assuming the manager of ClearWater wants to enforce a price range rule that requires every price to be between $0.50 and $500.00. After a simple analysis, we figure out thatonly INSERT and UPDATE operations can possibly violate this rule. If there is no such price range rule, both operations can be executed with any price values, including NULL.

Add a trigger in the Products (and Products_copy) table so it is automatically fired by INSERT and UPDATE to ensure they use only price values within the range. If they don't, the INSERT or UPDATE will be "rolled back" (i.e., reset.)

Explanation / Answer

CREATE TRIGGER tgr_priceValidation_2

BEFORE INSERT,UPDATE

ON Products_copy

FOR EACH ROW

BEGIN

IF NEW.UnitPrice>500.00 THEN

SIGNAL SQLSTATE VALUE '45001'

SET MESSAGE_TEXT = '[table:Products_copy] - unit price column is not valid. Unit Price should be greater than 0.50 and less than 500';

ELSE IF NEW.UnitPrice<0.50 THEN

SIGNAL SQLSTATE VALUE '45001'

SET MESSAGE_TEXT = '[table:Products_copy] - unit price column is not valid. Unit Price should be greater than 0.50 and less than 500';

END IF;

END;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

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