MySQL Triggers

1. What is a Trigger?

• A trigger is a stored database object that automatically executes (fires) when a specified event occurs on a table.
• Events can be:
  • INSERT
  • UPDATE
  • DELETE

👉 Triggers are useful for auditing, logging, enforcing rules, automatic calculations.

---

2. Trigger Syntax

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

---

3. Example 1: Logging Insert

Suppose we have two tables:

• employees(id, name, salary)
• employee_log(id, action, log_time)

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
   INSERT INTO employee_log(action, log_time)
   VALUES (CONCAT('Inserted employee ', NEW.name), NOW());
END;

👉 Every time a row is inserted into employees, a log entry is added.

---

4. Example 2: Prevent Negative Salary

CREATE TRIGGER before_salary_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
   IF NEW.salary < 0 THEN
      SET NEW.salary = 0;
   END IF;
END;

👉 This ensures salary never becomes negative.

---

5. Example 3: Delete Logging

CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
   INSERT INTO employee_log(action, log_time)
   VALUES (CONCAT('Deleted employee ', OLD.name), NOW());
END;

👉 Uses OLD keyword since data is being deleted.

---

6. NEW vs OLD Keywords

• NEW.column → Refers to the new value (in INSERT, UPDATE).
• OLD.column → Refers to the existing value (in UPDATE, DELETE).

---

7. Show and Drop Triggers

-- Show all triggers
SHOW TRIGGERS;

-- Drop a trigger
DROP TRIGGER trigger_name;

---

8. When to Use Triggers

✅ Good for:

• Automatic logs (auditing).
• Data validation before updates.
• Cascading changes (like updating related tables).

❌ Avoid triggers when:

• Business logic can be handled in the application (to keep DB simple).
• Too many triggers → performance overhead & debugging complexity.

---

✅ Summary:

• Triggers = Automatic actions on table events.
• Use BEFORE for validation/modification.
• Use AFTER for logging/auditing.
• Access row values using NEW and OLD.