How many TRIGGERS are allowed in MySql table?

There are 6 possible trigger events for which you can define triggers

In MySQL, a maximum of one trigger is allowed per table for each combination of:

Trigger event: INSERT, UPDATE, or DELETE
Trigger timing: BEFORE or AFTER

✅ This means:

You can have up to 6 triggers per table, as follows:

Trigger Timing	Trigger Event	Example Trigger Name
BEFORE	INSERT	`before_insert_trigger`
BEFORE	UPDATE	`before_update_trigger`
BEFORE	DELETE	`before_delete_trigger`
AFTER	INSERT	`after_insert_trigger`
AFTER	UPDATE	`after_update_trigger`
AFTER	DELETE	`after_delete_trigger`

CREATE TRIGGER before_insert_example BEFORE INSERT ON your_table FOR EACH ROW ...
CREATE TRIGGER after_insert_example AFTER INSERT ON your_table FOR EACH ROW ...
CREATE TRIGGER before_update_example BEFORE UPDATE ON your_table FOR EACH ROW ...
CREATE TRIGGER after_update_example AFTER UPDATE ON your_table FOR EACH ROW ...
CREATE TRIGGER before_delete_example BEFORE DELETE ON your_table FOR EACH ROW ...
CREATE TRIGGER after_delete_example AFTER DELETE ON your_table FOR EACH ROW ...

Here are examples of all 6 possible triggers in MySQL for a sample table called employees. These examples log changes to an employee_audit table.

🔧 Setup: Sample Tables

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2)
);

CREATE TABLE employee_audit (
    action_time DATETIME,
    action_type VARCHAR(20),
    old_name VARCHAR(100),
    new_name VARCHAR(100),
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2)
);

1️⃣ BEFORE INSERT Trigger

CREATE TRIGGER before_insert_employees
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (action_time, action_type, new_name, new_salary)
    VALUES (NOW(), 'BEFORE INSERT', NEW.name, NEW.salary);
END;

2️⃣ AFTER INSERT Trigger

CREATE TRIGGER after_insert_employees
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (action_time, action_type, new_name, new_salary)
    VALUES (NOW(), 'AFTER INSERT', NEW.name, NEW.salary);
END;

3️⃣ BEFORE UPDATE Trigger

CREATE TRIGGER before_update_employees
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (action_time, action_type, old_name, old_salary, new_name, new_salary)
    VALUES (NOW(), 'BEFORE UPDATE', OLD.name, OLD.salary, NEW.name, NEW.salary);
END;

4️⃣ AFTER UPDATE Trigger

CREATE TRIGGER after_update_employees
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (action_time, action_type, old_name, old_salary, new_name, new_salary)
    VALUES (NOW(), 'AFTER UPDATE', OLD.name, OLD.salary, NEW.name, NEW.salary);
END;

5️⃣ BEFORE DELETE Trigger

CREATE TRIGGER before_delete_employees
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (action_time, action_type, old_name, old_salary)
    VALUES (NOW(), 'BEFORE DELETE', OLD.name, OLD.salary);
END;

6️⃣ AFTER DELETE Trigger

CREATE TRIGGER after_delete_employees
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (action_time, action_type, old_name, old_salary)
    VALUES (NOW(), 'AFTER DELETE', OLD.name, OLD.salary);
END;