Trigger in MySQL

In MySQL, a trigger is a set of SQL statements that are

automatically executed in response to a specified event

occurring in a database. The event can be an INSERT,

UPDATE, or DELETE operation performed on a particular

table or view in the database.

CREATE TABLE employees(

employeeNumber INT AUTO_INCREMENT PRIMARY KEY,

lastname VARCHAR(100),

firstName VARCHAR(100),

extension VARCHAR(10),

email VARCHAR(50),

officeCode varchar(20),

reportTo INT,

jobTitle varchar(100)

);

INSERT INTO employees(lastname,firstName,extension,email,officeCode,reportTo,jobTitle)

values("desai","sangram","5678","sangram2681@gmail.com","1001",0,"Node.js Developer"),

("desai","sangar","5679","sagar1778@gmail.com","1002",0,"FULLSTACK Developer");

select * from employees;

Output:

+----------------+----------+-----------+-----------+-----------------------+------------+----------+---------------------+

| employeeNumber | lastname | firstName | extension | email | officeCode | reportTo | jobTitle |

+----------------+----------+-----------+-----------+-----------------------+------------+----------+---------------------+

| 1 | desai | sangram | 5678 | sangram2681@gmail.com | 1001 | 0 | Node.js Developer |

| 2 | desai | sangar | 5679 | sagar1778@gmail.com | 1002 | 0 | FULLSTACK Developer |

+----------------+----------+-----------+-----------+-----------------------+------------+----------+---------------------+

CREATE TABLE employees_audit (

id INT AUTO_INCREMENT PRIMARY KEY,

employeeNumber INT NOT NULL,

lastname VARCHAR(50) NOT NULL,

changedat DATETIME DEFAULT NULL,

action VARCHAR(50) DEFAULT NULL

);

IN trigger we can access old & new column values with OLD & NEW keywords.

The following table illustrates the availability of the OLD and NEW modifiers:

Trigger Event OLD NEW

INSERT No Yes

UPDATE Yes Yes

DELETE Yes No

CREATE TRIGGER before_employee_update

BEFORE UPDATE ON employees

FOR EACH ROW

INSERT INTO employees_audit

SET action = 'update',

employeeNumber = OLD.employeeNumber,

lastname = OLD.lastname,

changedat = NOW();

we used the OLD keyword to access values of the columns employeeNumber and lastname of the row affected by update statement.

Now RUN UPDATE Query on employees table as follows:

UPDATE employees

SET

lastName = 'sardeshmukh'

WHERE

employeeNumber = 1;

before update query get fired ,trigger got fired and INSERT record into employees_audit,lets check

select * from employees_audit;

Output:

+----+----------------+----------+---------------------+--------+

| id | employeeNumber | lastname | changedat | action |

+----+----------------+----------+---------------------+--------+

| 1 | 1 | desai | 2023-04-18 05:45:04 | update |

+----+----------------+----------+---------------------+--------+

AFTER UPDATE TRIGGER :

Example:

CREATE TRIGGER salary_update_trigger AFTER UPDATE ON employees

FOR EACH ROW

BEGIN

IF OLD.salary != NEW.salary THEN

INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)

VALUES (OLD.id, OLD.salary, NEW.salary, NOW());

END IF;

END;

BEFORE DELETE TRIGGER:

Example:

CREATE TRIGGER order_delete_trigger BEFORE DELETE ON orders

FOR EACH ROW

BEGIN

DECLARE order_age INT;

SET order_age = DATEDIFF(NOW(), OLD.order_date);

IF order_age > 30 THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'Cannot delete order older than 30 days';

END IF;

END;

BEFORE INSERT TRIGGER:

Example:

CREATE TRIGGER employee_insert_trigger BEFORE INSERT ON employees

FOR EACH ROW

BEGIN

SET NEW.hire_date = NOW();

END;

AFTER INSERT TRIGGER:

Example:

CREATE TRIGGER order_insert_trigger AFTER INSERT ON orders

FOR EACH ROW

BEGIN

INSERT INTO order_history (order_id, customer_id, order_date, total_amount)

VALUES (NEW.id, NEW.customer_id, NEW.order_date, NEW.total_amount);

END;

View & Trigger:

you can create a trigger on a view. However, there are some limitations

on what types of events can trigger the execution of the trigger.

For instance, you cannot create an INSERT or DELETE trigger on a view.

The reason for this is that a view is a virtual table that does not

store data directly but rather provides a way to access data from

one or more tables.

You can, however, create an UPDATE trigger on a view.

This allows you to perform some action whenever the data

in the underlying tables that the view is based on is updated.

To create a trigger on a view in MySQL, you would use the same syntax as

you would for creating a trigger on a table, but specify

the view name instead of the table name in the trigger definition.

Keep in mind that the SQL statements executed by the trigger

should be based on the underlying tables that the view is based on,

rather than the view itself.

We can see triggers created in database by following query

SHOW TRIGGERS;

Output:

+------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+

| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |

+------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+

| before_employee_update | UPDATE | employees | INSERT INTO employees_audit

SET action = 'update',

employeeNumber = OLD.employeeNumber,

lastname = OLD.lastname,

changedat = NOW() | BEFORE | 2023-04-18 05:38:35.36 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |

+------------------------+--------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+

N