Query 1: Create After Trigger / Database According to the design of Products (an
ID: 3705805 • Letter: Q
Question
Query 1: Create After Trigger / Database
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 UnitPrice.
Further, there is no limit what UnitPrice values could be, including a very 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 that
only 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.
APPROACH: 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.)
Each approach has its consideration and advantage/ disadvantage, meaning you are required to create an AFTER trigger called 'tgr_priceValidation' to implement the manager's new price rule in Products_copy table.
Any operation that violates this rule should be aborted by this trigger and receive
an error 51234 with a message below
"No product is allowed to have a unit price out of the range between $0.50 and $500.00."
Note that any action query, i.e., INSERT, DELETE, and UPDATE, can possibly affect more than one row of data. For example, if you are inserting 12 new products, each has a
valid or invalid or null value of unit price. SQL Server trigger is capable of examing all 12 prices because it uses the internal "INSERTED" table as you already learned from
the OUTPUT clause, which is introduced in the lectures of Chapter 7. To know what is the price before update, we use DELETED table, and to get the price after insert or
update, we use INSERTED table. Invalid values found in INSERTED table should invoke the ROLLBACK operation to "UNDO" or reset the entire INSERT or UPDATE operation that fires the trigger.
Several test cases are provided below for your use to test the trigger. If your trigger is implemented correctly, each test case should result in the passed or failed output as indicated in each case.
Test Cases:
--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;
Categories- Table:
Products-Table:
Products_Copy Table:
Suppliers-Table:
Column Name Data Type Allov Nulls : CategoryID CategoryName Description nt nvarchar 15) nvarchar(MAX) nvarchar(MAO
Explanation / Answer
----------------------------USING ERROR MESSAGE ----------------------------------------
CREATE TRIGGER tgr_priceValidation
ON Products_Copy
FOR INSERT AND DELETE AS
BEGIN
IF Products_Copy.UnitPrice NOT BETWEEN 0.50 AND 500.00
BEGIN
RAISERROR(' No product is allowed to have a unit price out of the range between $0.50 and $500.00', 16, 1)
ROLLBACK TRANSACTION
END
END
---------------------------USING ERROR CODE--------------------------------
CREATE TRIGGER tgr_priceValidation
ON Products_Copy
FOR INSERT AND DELETE AS
BEGIN
IF Products_Copy.UnitPrice NOT BETWEEN 0.50 AND 500.00
BEGIN
RAISERROR(51234 , 16, 1)
ROLLBACK TRANSACTION
END
END
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.