Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Triggers

Explore how to create and implement MySQL triggers that automatically update values like restock dates when product stock changes. Understand trigger syntax, timing, and event handling, with examples and alternative approaches for enforcing business logic within the database.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
/* Create the trigger */
DELIMITER //
CREATE TRIGGER trg_update_restock_date
BEFORE UPDATE ON Products
FOR EACH ROW
BEGIN
    -- If stock increases, set LastRestockDate to today's date
    IF NEW.Stock > OLD.Stock THEN
        SET NEW.LastRestockDate = CURDATE();
    END IF;
END //
DELIMITER ;
-- Check current values
SELECT ProductID, ProductName, Stock, LastRestockDate
FROM   Products
WHERE  ProductID = 1;
-- Increase stock to trigger the update
UPDATE Products
SET    Stock = Stock + 10
WHERE  ProductID = 1;
-- Verify that LastRestockDate is updated to today's date
SELECT ProductID, ProductName, Stock, LastRestockDate
FROM   Products
WHERE  ProductID = 1;

Explanation

The explanation of the solution code is given below:

  • Line 2: The DELIMITER // command sets a temporary delimiter so that semicolons inside the trigger body do not terminate the CREATE TRIGGER statement too early. This is required when defining multiple-statement routines or triggers in MySQL.

  • Lines 4–12: A row-level trigger trg_update_restock_date is created on the Products table to run before each update.

    • CREATE TRIGGER trg_update_restock_date defines the trigger name.

    • BEFORE UPDATE ON Products specifies that it fires before any update to rows in Products, so the trigger can adjust column values before they are stored.

    • FOR EACH ROW ensures the logic runs separately for every row that is updated by the statement.

    • BEGIN and END // wrap the trigger body as a single execution block using the custom delimiter.

    • Inside the block, IF NEW.Stock > OLD.Stock THEN checks whether the new Stock value is greater than the old one, meaning the product has been restocked.

    • When that condition is true, SET NEW.LastRestockDate = CURDATE(); updates the LastRestockDate column in the new row to today’s date before the row is written.

  • Line 14: ...