Create Trigger in MySQL

Creating a Trigger in MySQL

A Trigger in MySQL is a special type of stored program that automatically executes in response to INSERT, UPDATE, or DELETE operations on a table.

1. Syntax for Creating a Trigger

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

• trigger_name → Name of the trigger.
• BEFORE | AFTER → Defines whether the trigger fires before or after the event.
• INSERT | UPDATE | DELETE → Specifies the event that activates the trigger.
• table_name → Table on which the trigger is applied.
• FOR EACH ROW → Ensures that the trigger executes for each affected row.

2. Creating a BEFORE INSERT Trigger

📌 Example: Automatically set the created_at timestamp when inserting a new employee.

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END;

✔ Explanation:

• Before a new row is inserted into employees, the created_at column is set to the current timestamp (NOW()).
• NEW refers to the new row being inserted.

3. Creating an AFTER INSERT Trigger

📌 Example: Log inserted employees into an employee_audit table

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit(employee_id, action, action_date)
    VALUES (NEW.employee_id, 'INSERT', NOW());
END;

✔ Explanation:

• After a new employee is inserted into employees, an audit log is recorded.

4. Creating an AFTER UPDATE Trigger

📌 Example: Log salary changes for employees

CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_changes(employee_id, old_salary, new_salary, change_date)
    VALUES (OLD.employee_id, OLD.salary, NEW.salary, NOW());
END;

✔ Explanation:

• When the the salary column is updated in employees, the old and new salary values are logged.
• OLD.salary refers to the previous salary, and NEW.salary refers to the updated salary.

5. Creating a BEFORE DELETE Trigger

📌 Example: Prevent deletion of employees with admin role

CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    IF OLD.role = 'admin' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot delete admin employees!';
    END IF;
END;

✔ Explanation:

• If an employee has the role 'admin', deletion is prevented using SIGNAL to raise an error.

6. Viewing Triggers

📌 List all triggers in the database

SHOW TRIGGERS;

📌 View a specific trigger

SHOW CREATE TRIGGER after_employee_insert;

7. Dropping a Trigger

📌 Remove a specific trigger

DROP TRIGGER IF EXISTS after_employee_insert;

8. Summary

✔ Triggers automate database tasks based on events (INSERT, UPDATE, DELETE).
✔ Use BEFORE triggers to modify data before an operation.
✔ Use AFTER triggers to log changes or take additional actions.
✔ Use SHOW TRIGGERS; to list existing triggers.
✔ Use DROP TRIGGER trigger_name; to remove a trigger.

Would you like a trigger customized for your database? 🚀