Duration: 2 hours (Lecture: 2 hours, Lab: 2 hours)

Syllabus Topics:

• Lecture:
  • Triggers (BEFORE, AFTER)
  • New and Old Trigger Variables
  • Trigger Examples and Real-Time Use
• Lab:
  • Create BEFORE Triggers
  • Create AFTER Triggers

Lecture Notes (In-Depth)

1. Triggers (BEFORE, AFTER)

• Definition: A trigger is a stored program in MySQL that automatically executes in response to specific Data Manipulation Language (DML) events (INSERT, UPDATE, DELETE) on a table.

• Types:

  • BEFORE Triggers:
    • Execute before the DML operation is applied.
    • Can modify the NEW values for INSERT or UPDATE operations.
    • Use case: Validate or transform data before it’s written to the table.
  • AFTER Triggers:
    • Execute after the DML operation is completed.
    • Cannot modify NEW values (read-only access to NEW and OLD).
    • Use case: Log changes or update related tables after the operation.

• Syntax:

  CREATE TRIGGER trigger_name
  {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
  ON table_name
  FOR EACH ROW
  BEGIN
      -- Trigger logic
  END;
  
  

• Key Notes:

  • Triggers are associated with a specific table and event.
  • MySQL triggers operate at the row level (FOR EACH ROW), not statement level.
  • Triggers cannot call themselves recursively to avoid infinite loops.
  • Supported by InnoDB; MyISAM support is limited in older MySQL versions.
  • Use DROP TRIGGER trigger_name; to delete a trigger.
  • View triggers with SHOW TRIGGERS;.

2. New and Old Trigger Variables

• NEW:

  • Represents the new row being inserted (INSERT) or the updated row (UPDATE).
  • Accessible in BEFORE and AFTER triggers for INSERT and UPDATE.
  • Modifiable in BEFORE triggers (e.g., SET NEW.column = value;).
  • Example: Set a default salary if NULL in a BEFORE INSERT trigger.

• OLD:

  • Represents the row before an update (UPDATE) or deletion (DELETE).
  • Accessible in BEFORE and AFTER triggers for UPDATE and DELETE.
  • Read-only; cannot be modified.
  • Example: Log the old salary in an AFTER UPDATE trigger.

• Availability:

  Operation	NEW Available	OLD Available
  INSERT	Yes (modifiable in BEFORE)	No
  UPDATE	Yes (modifiable in BEFORE)	Yes (read-only)
  DELETE	No	Yes (read-only)

• Key Notes:

  • Use NEW to enforce data rules (e.g., validate or transform values).
  • Use OLD to track changes (e.g., for auditing).

3. Trigger Examples and Real-Time Use

• Examples:

  • BEFORE INSERT Trigger:

    • Ensure salaries are non-negative.

    DELIMITER //
    CREATE TRIGGER check_salary_before_insert
    BEFORE INSERT ON employees
    FOR EACH ROW
    BEGIN
        IF NEW.salary < 0 THEN
            SET NEW.salary = 0;
        END IF;
    END //
    DELIMITER ;
    
    

  • AFTER INSERT Trigger:

    • Log new employee insertions to an audit table.

    CREATE TABLE audit_log (
        log_id INT AUTO_INCREMENT PRIMARY KEY,
        action VARCHAR(50),
        emp_id INT,
        details VARCHAR(100),
        action_time TIMESTAMP
    );
    DELIMITER //
    CREATE TRIGGER log_employee_insert
    AFTER INSERT ON employees
    FOR EACH ROW
    BEGIN
        INSERT INTO audit_log (action, emp_id, details, action_time)
        VALUES ('INSERT', NEW.emp_id, CONCAT('Added ', NEW.name), NOW());
    END //
    DELIMITER ;
    
    

  • BEFORE UPDATE Trigger:

    • Prevent salary decreases.

    DELIMITER //
    CREATE TRIGGER prevent_salary_decrease
    BEFORE UPDATE ON employees
    FOR EACH ROW
    BEGIN
        IF NEW.salary < OLD.salary THEN
            SET NEW.salary = OLD.salary;
        END IF;
    END //
    DELIMITER ;
    
    

  • AFTER DELETE Trigger:

    • Log deleted employees.

    DELIMITER //
    CREATE TRIGGER log_employee_delete
    AFTER DELETE ON employees
    FOR EACH ROW
    BEGIN
        INSERT INTO audit_log (action, emp_id, details, action_time)
        VALUES ('DELETE', OLD.emp_id, CONCAT('Deleted ', OLD.name), NOW());
    END //
    DELIMITER ;
    
    

• Real-Time Use Cases:

  • Data Validation: Ensure data meets business rules (e.g., non-negative salaries).
  • Auditing: Track changes to sensitive data (e.g., log salary updates).
  • Cascading Updates: Update related tables automatically (e.g., update a summary table after an insert).
  • Enforcing Constraints: Implement rules not covered by standard constraints (e.g., custom validation logic).
  • Example: Log all changes to an orders table in an e-commerce system for compliance.

Lab Notes (In-Depth)