use ClearWater if object_id(\'Products_copy\') is not null drop table Products_c
ID: 3705916 • Letter: U
Question
use ClearWater
if object_id('Products_copy') is not null
drop table Products_copy;
select * into Products_copy from Products;
go
/* Q1. (10 points)
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.
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.) 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, . 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.
Below are some 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;
Below is a screenshot of the table products
ResutsMessages ProductID Product Name SupplierlD CategorylD QuantityPerUnit UnitPrice UnitsinStock UnitsOnOrderReorderLeve Discontinued 10 boxes x 20 bags 24-12 oz bottles 12-550 ml bottles 48-6 oz jars 36 boxes 12-8 oz jars 12-1 lb pkgs 12-12 oz jars 18.00 19.00 10.00 22.00 53 21.35 0 25.00 120 30.00 15 40.00 39 17 10 25 25 40 Aniseed Syrup Chef Anton's Cajun Seasoning 2 Chef Anton's Gumbo Mix Grandma's Boysenbemy Spread 3 Uncle Bob's Organic Dried Pears 3 Northwoods Cranbeny Sauce3 25 10 2Explanation / Answer
CREATE TRIGGER trigger_priceValidation
BEFORE INSERT,UPDATE
ON Products_copy
FOR EACH ROW
BEGIN
IF NEW.UnitPrice>500.00 THEN
SIGNAL SQLSTATE VALUE '000234'
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
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.