Advanced MySQL Topics - Triggers & Scheduled Events

CREATE TABLE Products (ProductID VARCHAR(10), ProductName VARCHAR(100),BuyPrice DECIMAL(6,2), SellPrice DECIMAL(6,2), NumberOfItems INT);

INSERT INTO Products (ProductID, ProductName, BuyPrice, SellPrice, NumberOfITems)
VALUES ("P1", "Artificial grass bags ", 40, 50, 100),  
("P2", "Wood panels", 15, 20, 250),  
("P3", "Patio slates",35, 40, 60),  
("P4", "Sycamore trees ", 7, 10, 50),  
("P5", "Trees and Shrubs", 35, 50, 75),  
("P6", "Water fountain", 65, 80, 15);

SELECT * FROM Products;

CREATE TABLE Notifications (NotificationID INT AUTO_INCREMENT, Notification VARCHAR(255), DateTime TIMESTAMP NOT NULL, PRIMARY KEY(NotificationID));

DESCRIBE Notifications;

CREATE TABLE Orders (
    OrderID INT NOT NULL PRIMARY KEY,
    ClientID VARCHAR(10),
    ProductID VARCHAR(10),
    Quantity INT,
    Cost DECIMAL(6,2),
    Date DATE
); 

-- Syntax to create, drop and list triggers
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE}
ON table_name FOR EACH ROW
trigger_body;

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

SHOW TRIGGERS
[{FROM | IN} database_name]
[LIKE 'pattern' | WHERE search_condition];

-- OLD is used to access the value of the column before the operation (For example BEFORE INSERT, UPDATE and DELETE). NEW is used to access the value of the column after the operation.
DELIMITER //
CREATE TRIGGER OrderQtyCheck  
  BEFORE INSERT ON Orders  
  FOR EACH ROW  
BEGIN 
  IF NEW.Quantity < 0 THEN  
    SET NEW.Quantity = 0; 
  END IF; 
END//
DELIMITER ;


CREATE TRIGGER AfterDeleteOrder 
  AFTER DELETE   
  ON Orders FOR EACH ROW 
INSERT INTO Notifications VALUES(CONCAT('Order ',OLD.OrderID, ' was deleted at ', CURRENT_TIME(), ' on ', CURRENT_DATE()),'DELETE');
INSERT INTO Notifications(Notification,DateTime) 
VALUES(CONCAT('Order ',OLD.OrderID, ' was deleted!'), NOW()); 

-- Test the newly created triggers 
INSERT INTO Orders(OrderID, ClientID, ProductID , Quantity, Cost, Date) VALUES
(31, "Cl1", "P1", -10, 500, "2020-09-01");

-- The result will be 0 instead of -10
SELECT Quantity FROM Orders WHERE OrderID = 31; 

DELETE FROM Orders WHERE OrderID = 31;
-- A new record is added in table Notifications
SELECT * FROM Notifications;


-- Create an INSERT trigger called ProductSellPriceInsertCheck. This trigger must check if the SellPrice of the product is less than the BuyPrice after a new product is inserted in the Products table. If this occurs, then a notification must be added to the Notifications table to inform the sales department. The sales department can then ensure that the incorrect values were not inserted by mistake.
DELIMITER //
CREATE TRIGGER ProductSellPriceInsertCheck 
    AFTER INSERT  
    ON Products FOR EACH ROW  
    BEGIN
    IF NEW.SellPrice <= NEW.BuyPrice THEN
        INSERT INTO Notifications(Notification,DateTime) 
        VALUES(CONCAT('A SellPrice same or less than the BuyPrice was inserted for ProductID ', NEW.ProductID), NOW()); 
    END IF;
    END //
DELIMITER ;

INSERT INTO Products (ProductID, ProductName, BuyPrice, SellPrice, NumberOfITems)
VALUES ("P7", "Hats", 12, 9.9, 500);

SELECT * FROM Notifications;

-- Create an UPDATE trigger called ProductSellPriceUpdateCheck. This trigger must check that products are not updated with a SellPrice that is less than or equal to the BuyPrice.
-- If this occurs, add a notification to the notifications table for the sales department so they can ensure that product prices were not updated with the incorrect values. This trigger sends a notification to the Notifications table that warns the sales department of the issue.
DELIMITER //
CREATE TRIGGER ProductSellPriceUpdateCheck 
    AFTER UPDATE  
    ON Products FOR EACH ROW  
    BEGIN
    IF NEW.SellPrice <= NEW.BuyPrice THEN
        INSERT INTO Notifications(Notification,DateTime) 
        VALUES(CONCAT(NEW.ProductID,' was updated with a SellPrice of ', NEW.SellPrice,' which is the same or less than the BuyPrice'), NOW()); 
    END IF;
    END //
DELIMITER ;

UPDATE Products SET BuyPrice = 10 WHERE ProductID = 'P7';

SELECT * FROM Notifications;

-- Create a DELETE trigger called NotifyProductDelete. This trigger must insert a notification in the notifications table for the sales department after a product has been deleted from the Products table.
CREATE TRIGGER NotifyProductDelete 
AFTER DELETE   
ON Products FOR EACH ROW   
INSERT INTO Notifications(Notification, DateTime) 
VALUES(CONCAT('The product with a ProductID ', OLD.ProductID,' was deleted'), NOW()); 

DELETE FROM Products WHERE ProductID = 'P7';

SELECT * FROM Notifications;

-- Check if event_schedular is ON/OFF
SHOW VARIABLES LIKE 'event_scheduler';
-- Turn it on if it's off
SET GLOBAL event_scheduler = ON;

CREATE EVENT GenerateRevenueReport ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE DO
INSERT INTO ReportData (OrderID, ClientID, ProductID, Quantity, Cost, Date) SELECT * FROM Orders WHERE DATE BETWEEN '2022-09-01' AND '2022-09-30';

DELIMITER //
CREATE EVENT DailyRestock ON SCHEDULE EVERY 1 DAY DO
BEGIN
IF Products.NumberOfItems < 50 
THEN UPDATE Products SET NumberOfItems = 50;
END IF;
END//
DELIMITER ;

SHOW EVENTS;

DROP EVENT GenerateRevenueReport;
DROP EVENT DailyRestock;