1. Create a trigger named products_before_insert that inserts the current date f

Question

1. Create a trigger named products_before_insert that inserts the current date for the date_added column of the Products table if the value for that column is null.

Test this trigger with an appropriate INSERT statement.

2. Create a table named Products_Audit. This table should have all columns of the Products table, except the description column. Also, it should have an audit_id column for its primary key, and the date_added column should be changed to date_updated.

3. Check whether the event scheduler is turned on. If it isn

Explanation / Answer

CREATE TABLE PRODUCTS(
PRODUCT_ID int,
PRODUCT_NAME varchar(100),
DESCRIPTION varchar(255),
PRICE DECIMAL(10,2),
DATE_ADDED date,
PRIMARY KEY (PRODUCT_ID)
);

CREATE TRIGGER products_before_insert BEFORE INSERT ON products
FOR EACH ROW
BEGIN
   IF NEW.date_added IS NULL THEN
       SET NEW.date_added = CURDATE();
   END IF;
END;

INSERT INTO PRODUCTS (1,'Prod1','Prod1 Desc',100,CURDATE());
INSERT INTO PRODUCTS (2,'Prod2','Prod2 Desc',200,NULL);
INSERT INTO PRODUCTS (3,'Prod3','Prod3 Desc',300,NULL);
INSERT INTO PRODUCTS (4,'Prod4','Prod4 Desc',400,CURDATE());

SELECT * FROM PRODUCTS;

CREATE TABLE PRODUCTS_AUDIT AS
SELECT PRODUCT_ID AS AUDIT_ID, PRODUCT_ID, PRODUCT_NAME, PRICE, DATE_ADDED AS DATE_UPDATED
FROM PRODUCT WHERE 1=2;

ALTER TABLE PRODUCTS_AUDIT MODIFY AUDIT_ID NOT NULL AUTO_INCREMENT;

ALTER TABLE PRODUCTS_AUDIT ADD PRIMARY KEY (AUDIT_ID);

CREATE TRIGGER products_after_update AFTER UPDATE
ON PRODUCTS
FOR EACH ROW
BEGIN
   INSERT INTO PRODUCTS_AUDIT(OLD.PRODUCT_ID,OLD.PRODUCT_NAME,OLD.PRICE,CURDATE());
END;

UDPATE PRODUCTS SET PRICE=PRICE*10;

SELECT * FROM PRODUCTS_AUDIT;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

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