SQL Triggers | Automate Database Events

# CHAPTER 25

Triggers and Database Automation #

1. Introduction #

2. Learning Objectives #

• Define the architectural purpose of an SQL Trigger.

• Understand the event timing (BEFORE vs AFTER).

• Understand the event types (INSERT, UPDATE, DELETE).

• Access data states using the NEW and OLD keywords.

• Build an automated Audit Logging system.

3. What is a Trigger? #

Events that can fire a Trigger:

• INSERT

• UPDATE

• DELETE

Timing of the Trigger:

• BEFORE: Fires *before* the data is physically saved to the hard drive (Useful for data validation/modification).

• AFTER: Fires *after* the data is safely written (Useful for auditing or updating other tables).

4. Creating an AFTER Trigger (Audit Logging) #

We use the magical keywords OLD (the data before the update) and NEW (the data after the update).

DELIMITER //

CREATE TRIGGER AfterSalaryUpdate
-- Firing Rule: After a row is updated in the employees table...
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- Check if the salary column specifically was changed
    IF OLD.salary != NEW.salary THEN
        -- Automatically insert a log! The PHP backend doesn't even know this is happening!
        INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_at)
        VALUES (NEW.id, OLD.salary, NEW.salary, NOW());
    END IF;
END //

DELIMITER ;

*(Result: If an admin runs UPDATE employees SET salary = 90000 WHERE id = 5;, the trigger silently fires in the background, permanently saving a trail of the action).*

5. Creating a BEFORE Trigger (Data Validation) #

DELIMITER //

CREATE TRIGGER PreventNegativePrice
-- Firing Rule: Intercept the data BEFORE it saves to the hard drive
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    -- Check the incoming data payload (NEW)
    IF NEW.price < 0 THEN
        -- Silently overwrite the bad data with a 0!
        SET NEW.price = 0;
    END IF;
END //

DELIMITER ;

6. Mini Project: E-Commerce Inventory Automation #

DELIMITER //

CREATE TRIGGER DeductInventory
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    -- Automatically reduce the stock of the specific product purchased
    UPDATE inventory 
    SET stock_quantity = stock_quantity - NEW.quantity
    WHERE product_id = NEW.product_id;
END //

DELIMITER ;

*(Now, inserting an order item mathematically guarantees the inventory is synced. Flawless architecture!)*

7. The Danger of Cascading Triggers #

8. Common Mistakes #

• Hidden Logic Debugging: Because Triggers run silently in the background, they are notorious for causing "ghost" bugs. A Node.js developer inserts an Order, and the Inventory mysteriously decreases. The developer spends 10 hours checking their JavaScript code, not realizing a Database Trigger is doing it! Always document your triggers clearly.

9. Best Practices #

• Keep Triggers Tiny: A trigger executes *every single time* a row is touched. If your trigger contains a massive, 10-second JOIN query, and you try to UPDATE 1,000 rows, your database will freeze for 10,000 seconds. Triggers must be instantaneous, simple logic.

10. Exercises #

1. 1. What two keywords are used inside an UPDATE trigger to access the data payload before and after the modification?

1. 2. If you want a trigger to physically intercept and alter incoming data before it is saved to the table, what timing clause must you use?

11. SQL Challenges #

DROP TRIGGER PreventNegativePrice;

12. MCQ Quiz with Answers #

13. Interview Questions #

• Q: Explain an architectural scenario where implementing a BEFORE UPDATE trigger is heavily favored over implementing an AFTER UPDATE trigger.

• Q: A junior developer suggests using Triggers to handle all business logic (e.g., sending welcome emails, calculating taxes, managing inventory) to keep the backend Python code "clean." Argue against this architectural pattern, specifically addressing the concepts of "Hidden Logic" and Trigger Cascading.

14. FAQs #

15. Summary #

16. Next Chapter Recommendation #