A trigger is a stored program that is automatically executed ("triggered") in response to certain events occurring in the database. These events can include INSERT, UPDATE, or DELETE operations on a table.

 Triggers are created using the CREATE TRIGGER statement.

Types Of Triggers in MYSQL

  1. Before Insert: Activated Before insert data in table.
  2. After Insert: Activated After insert data in table.
  3. Before Update: Activated Before update data in table.
  4. After Update: Activated After update data in table.
  5. Before Delete: Activated Before remove data in table.
  6. After Delete: Activated After remove data in table.
CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name
FOR EACH ROW
BEGIN
   -- SQL statements
END;

Example:

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  salary INT
);

CREATE TABLE salary_log (
  emp_id INT,
  old_salary INT,
  new_salary INT
);

 

CREATE TRIGGER salary_update
AFTER UPDATE
ON employees
FOR EACH ROW
BEGIN
   INSERT INTO salary_log(emp_id, old_salary, new_salary)
   VALUES(OLD.id, OLD.salary, NEW.salary);
END;

This trigger runs immediately after a row in employees is updated.

  • AFTER UPDATE - executes after the UPDATE statement successfully changes the row
  • FOR EACH ROW - executes once for every row that is updated

Show Triger:

SHOW TRIGGERS;

Drop Triger:

DROP TRIGGER trigger_name;