1. Create a trigger named Products_UPDATE that checks the new value for the Disc

Question

1.      Create a trigger named Products_UPDATE that checks the new value for the DiscountPercent column of the Products table. This trigger should raise an appropriate error if the discount percent is greater than 100 or less than 0.

If the new discount percent is between 0 and 1, this trigger should modify the new discount percent by multiplying it by 100. That way, a discount percent of .2 becomes 20.

Test this trigger with an appropriate UPDATE statement.

2.      CPreate a trigger named Products_INSERT that inserts the current date for the DateAdded column of the Products table if the value for that column is null.

Test this trigger with an appropriate INSERT statement.

3.      Create a table named ProductsAudit. This table should have all columns of the Products table, except the Description column. Also, it should have an AuditID column for its primary key, and the DateAdded column should be changed to DateUpdated.

Create a trigger named Products_UPDATE. This trigger should insert the old data about the product into the ProductsAudit table after the row is updated. Then, test this trigger with an appropriate UPDATE statement.

Explanation / Answer

Here is the solution Aas per the given criteria:-
First we need to create table Products

Go

CREATE TABLE Products
(
CategoryID INT REFERENCES Categories ( CategoryID ),
ProductCode VARCHAR ( 10 ) NOT NULL UNIQUE ,
ProductName VARCHAR ( 255 ) NOT NULL,
Description VARCHAR ( 255 ) NOT NULL,
ListPrice MONEY NOT NULL,
DiscountPercent MONEY NOT NULL DEFAULT 0.00 ,
DateAdded DATETIME NULL
);
GO

===============================
Go
CREATE TABLE ProductsAudit
(
AuditID INT PRIMARY KEY IDENTITY ,
CategoryID INT REFERENCES Categories ( CategoryID ),
ProductCode VARCHAR ( 10 ) NOT NULL UNIQUE ,
ProductName VARCHAR ( 255 ) NOT NULL,
ListPrice MONEY NOT NULL,
DiscountPercent MONEY NOT NULL DEFAULT 0.00 ,
DateUpdated DATETIME NULL
);
GO
------------------------------------------------
Now here we insert some values into tables

INSERT INTO Products
VALUES (4, 'DK-5310', 'Delight Keys 5300',
'This keys is so delight, you just love it!',
445.99, 58.00, NULL)
INSERT INTO Products
VALUES (7, 'PK-5310', 'Private Keys 5300',
'This keys is so private, you just love it!',
759.59, 80.00, NULL)
INSERT INTO Products
VALUES (3, 'AK-5310', 'Awesome Keys 5300',
'This keys is so awsome, you just loved!',
682.95, 52.00, NULL)
INSERT INTO Products
VALUES (2, 'NK-5310', 'Nice Keys 5300',
'This keys is so nice, you just freak!',
648.92, 32.00, NULL)

===============================================

Now we start sort out each queries one by one:-

Part 1) USE Products_UPDATE;

DROP TRIGGER IF EXISTS Products_UPDATE;

DELIMITER //

CREATE TRIGGER Products_UPDATE
BEFORE 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 < 1 THEN
SET NEW.discount_percent = (NEW.discount_percent * 100);
END IF;

END//

DELIMITER ;

UPDATE Products
SET discount_percent = .4
WHERE product_id = 3;
========================================================
Part 2) USE Products_INSERT

IF OBJECT_ID ('Products_INSERT') IS NOT NULL
DROP TRIGGER Products_INSERT;
GO

CREATE TRIGGER Products_INSERT
ON Products
AFTER INSERT
AS
UPDATE Products
SET DateAdded = GETDATE()
WHERE DateAdded IS NULL;
  
GO

To test this command ran the following INSERT statement:

INSERT INTO Products
VALUES (5, 'UK-5310', 'Unique Keys 5300',
'This keys is so unique, you just love it!',
645.99, 50.00, NULL)

=============================================================
Part 3)
GO

IF OBJECT_ID('Products_UPDATE') IS NOT NULL
DROP TRIGGER Products_UPDATE
GO

CREATE TRIGGER Products_UPDATE
ON Products
AFTER INSERT, UPDATE
AS
INSERT ProductsAudit (AuditID, ProductID, CategoryID, ProductCode,
ProductName, ListPrice, DiscountPercent, DateUpdated)

SELECT AuditID, Products.ProductID, Products.CategoryID, Products.ProductCode,
Products.ProductName,Products.ListPrice, Products.DiscountPercent, DateAdded

FROM Products JOIN ProductsAudit

ON ProductsAudit.AuditID = (SELECT AuditID FROM inserted)

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

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