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

You can use the same test cases provided in Q1 to test this new trigger.

Solving this problem allows you to learn that many (not all) business rules or constraints
can be implemented by either type of triggers. However, the idea and logic behind these
two types of triggers are very different, you need to carefully design them in order to
achieve the desired effects. For example, you can use ROLLBACK statement in an AFTER trigger
to abort the operation that fires the trigger but you don't need ROLLBACK in an INSTEAD OF
trigger because, when an INSTEAD OF trigger is fired, the firing operation is already
aborted by the trigger.

Further, if nothing found necessary to be aborted or rollback, an AFTER trigger may perform
no repairing INSERT, UPDATE, or DELETE. However, when an INSTEAD OF trigger is fired, it must
execute its own INSERT, UPDATE, or DELETE operations to replace the corresponding but already
aborted operations.

The INSTEAD OF trigger you are going to implement in Q2 allows you to practice one of such
cases, meaning, when it finds the triggering INSERT or UPDATE contains an out of range price
value, it only needs to throw the error, but, very importantly, if the price is valid, it must
execute its own INSERT or UPDATE to complete the operation because the INSERT or UPDATE that
fires the trigger has already aborted automatically by the INSTEAD OF trigger.

--Test Case 1: INSERT and passed

insert into Products_copy ([ProductID], [ProductName], [UnitPrice], [Discontinued])
values (301, 'Product 301', NULL, 1),
(302, 'Product 302', 199.5, 1);

--Test Case 2: INSERT but failed

insert into Products_copy ([ProductID], [ProductName], [UnitPrice], [Discontinued])
values (303, 'Product 301', 22, 1),
(304, 'Product 302', 0.25, 1);

--Test Case 3: UPDATEs and passed

update Products_copy
   set [UnitPrice] = NULL
   where [ProductID] <= 3;

update Products_copy
   set [UnitPrice] = 0.5
   where [ProductID] <= 3;

--Test Case 4: UPDATEs but failed

update Products_copy
   set [UnitPrice] = [UnitPrice] - 30
   where [ProductID] <= 15;

update Products_copy
   set [UnitPrice] = -2.99
   where [ProductID] <= 10;

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.