Different types of MySQL Triggers (with examples)

A MySQL trigger is a stored program (with queries) which is executed automatically to respond to a specific event such as insertion, updation or deletion occurring in a table. There are 6 different types of triggers in MySQL: 1. Before Update Trigger: As the name implies, it is a trigger which enacts before an update is invoked. If we write an update statement, then the actions of the trigger will be performed before the update is implemented. Example: Considering tables:

create table customer (acc_no integer primary key, cust_name varchar(20), avail_balance decimal); create table mini_statement (acc_no integer, avail_balance decimal, foreign key(acc_no) references customer(acc_no) on delete cascade); 

Inserting values in them:

insert into customer values (1000, "Fanny", 7000); insert into customer values (1001, "Peter", 12000); 

Trigger to insert (old) values into a mini_statement record (including account number and available balance as parameters) before updating any record in customer record/table:

delimiter // create trigger update_cus -> before update on customer -> for each row -> begin -> insert into mini_statement values (old.acc_no, old.avail_balance); -> end; // 

Making updates to invoke trigger:

delimiter; update customer set avail_balance = avail_balance + 3000 where acc_no = 1001; update customer set avail_balance = avail_balance + 3000 where acc_no = 1000; 

Output:

select *from mini_statement; +--------+---------------+ | acc_no | avail_balance | +--------+---------------+ | 1001 | 12000 | | 1000 | 7000 | +--------+---------------+ 2 rows in set (0.0007 sec) 

2. After Update Trigger: As the name implies, this trigger is invoked after an updation occurs. (i.e., it gets implemented after an update statement is executed.). Example: We create another table:

create table micro_statement (acc_no integer, avail_balance decimal, foreign key(acc_no) references customer(acc_no) on delete cascade); 

Insert another value into customer:

insert into customer values (1002, "Janitor", 4500); Query OK, 1 row affected (0.0786 sec) 

Trigger to insert (new) values of account number and available balance into micro_statement record after an update has occurred:

delimiter // create trigger update_after -> after update on customer -> for each row -> begin -> insert into micro_statement values(new.acc_no, new.avail_balance); -> end; // 

Making an update to invoke trigger:

delimiter ; update customer set avail_balance = avail_balance + 1500 where acc_no = 1002; 

Output:

select *from micro_statement; +--------+---------------+ | acc_no | avail_balance | +--------+---------------+ | 1002 | 6000 | +--------+---------------+ 1 row in set (0.0007 sec) 

3. Before Insert Trigger: As the name implies, this trigger is invoked before an insert, or before an insert statement is executed. Example: Considering tables:

create table contacts (contact_id INT (11) NOT NULL AUTO_INCREMENT, last_name VARCHAR (30) NOT NULL, first_name VARCHAR (25), ->birthday DATE, created_date DATE, created_by VARCHAR(30), CONSTRAINT contacts_pk PRIMARY KEY (contact_id)); 

Trigger to insert contact information such as name, birthday and creation-date/user into a table contact before an insert occurs:

delimiter // create trigger contacts_before_insert -> before insert -> on contacts for each row -> begin -> DECLARE vUser varchar(50); -> -> -- Find username of person performing INSERT into table -> select USER() into vUser; -> -> -- Update create_date field to current system date -> SET NEW.created_date = SYSDATE(); -> -> -- Update created_by field to the username of the person performing the INSERT -> SET NEW.created_by = vUser; -> end; // 

Making an insert to invoke the trigger:

delimiter; insert into contacts values (1, "Newton", "Enigma", str_to_date ("19-08-1999", "%d-%m-%Y"), str_to_date ("17-03-2018", "%d-%m-%Y"), "xyz"); 

Output:

select *from contacts; +------------+-----------+------------+------------+--------------+----------------+ | contact_id | last_name | first_name | birthday | created_date | created_by | +------------+-----------+------------+------------+--------------+----------------+ | 1 | Newton | Enigma | 1999-08-19 | 2019-05-11 | root@localhost | +------------+-----------+------------+------------+--------------+----------------+ 

4. After Insert Trigger: As the name implies, this trigger gets invoked after an insert is implemented. Example: Consider tables:

create table contacts (contact_id int (11) NOT NULL AUTO_INCREMENT, last_name VARCHAR(30) NOT NULL, first_name VARCHAR(25), birthday DATE, ->CONSTRAINT contacts_pk PRIMARY KEY (contact_id)); create table contacts_audit (contact_id integer, created_date date, created_by varchar (30)); 

Trigger to insert contact_id and contact creation-date/user information into contacts_audit record after an insert occurs:

delimiter // create trigger contacts_after_insert -> after insert -> on contacts for each row -> begin -> DECLARE vUser varchar(50); -> -> -- Find username of person performing the INSERT into table -> SELECT USER() into vUser; -> -> -- Insert record into audit table -> INSERT into contacts_audit -> ( contact_id, -> created_date, -> created_by) -> VALUES -> ( NEW.contact_id, -> SYSDATE(), -> vUser ); -> END; // 

Making an insert to invoke the trigger:

insert into contacts values (1, "Kumar", "Rupesh", str_to_date("20-06-1999", "%d-%m-%Y")); 

Output:

select *from contacts_audit; +------------+--------------+----------------+ | contact_id | created_date | created_by | +------------+--------------+----------------+ | 1 | 2019-05-11 | root@localhost | +------------+--------------+----------------+ 1 row in set (0.0006 sec) 

5. Before Delete Trigger: As the name implies, this trigger is invoked before a delete occurs, or before deletion statement is implemented. Example: Consider tables:

create table contacts (contact_id int (11) NOT NULL AUTO_INCREMENT, last_name VARCHAR (30) NOT NULL, first_name VARCHAR (25), birthday DATE, created_date DATE, created_by VARCHAR(30), CONSTRAINT contacts_pk PRIMARY KEY (contact_id)); create table contacts_audit (contact_id integer, deleted_date date, deleted_by varchar(20)); 

Trigger to insert contact_id and contact deletion-date/user information into contacts_audit record before a delete occurs:

delimiter // create trigger contacts_before_delete -> before delete -> on contacts for each row -> begin -> -> DECLARE vUser varchar(50); -> -> -- Find username of person performing the DELETE into table -> SELECT USER() into vUser; -> -> -- Insert record into audit table -> INSERT into contacts_audit -> ( contact_id, -> deleted_date, -> deleted_by) -> VALUES -> ( OLD.contact_id, -> SYSDATE(), -> vUser ); -> end; // 

Making an insert and then deleting the same to invoke the trigger:

delimiter; insert into contacts values (1, "Bond", "Ruskin", str_to_date ("19-08-1995", "%d-%m-%Y"), str_to_date ("27-04-2018", "%d-%m-%Y"), "xyz"); delete from contacts where last_name="Bond"; 

Output:

select *from contacts_audit; +------------+--------------+----------------+ | contact_id | deleted_date | deleted_by | +------------+--------------+----------------+ | 1 | 2019-05-11 | root@localhost | +------------+--------------+----------------+ 1 row in set (0.0007 sec) 

6. After Delete Trigger: As the name implies, this trigger is invoked after a delete occurs, or after a delete operation is implemented. Example: Consider the tables:

create table contacts (contact_id int (11) NOT NULL AUTO_INCREMENT, last_name VARCHAR (30) NOT NULL, first_name VARCHAR (25), birthday DATE, created_date DATE, created_by VARCHAR (30), CONSTRAINT contacts_pk PRIMARY KEY (contact_id)); create table contacts_audit (contact_id integer, deleted_date date, deleted_by varchar(20));

Trigger to insert contact_id and contact deletion-date/user information into contacts_audit record after a delete occurs:

 create trigger contacts_after_delete -> after delete -> on contacts for each row -> begin -> -> DECLARE vUser varchar(50); -> -> -- Find username of person performing the DELETE into table -> SELECT USER() into vUser; -> -> -- Insert record into audit table -> INSERT into contacts_audit -> ( contact_id, -> deleted_date, -> deleted_by) -> VALUES -> ( OLD.contact_id, -> SYSDATE(), -> vUser ); -> end; // 

Making an insert and deleting the same to invoke the trigger:

delimiter; insert into contacts values (1, "Newton", "Isaac", str_to_date ("19-08-1985", "%d-%m-%Y"), str_to_date ("23-07-2018", "%d-%m-%Y"), "xyz"); delete from contacts where first_name="Isaac"; 

Output:

select *from contacts_audit; +------------+--------------+----------------+ | contact_id | deleted_date | deleted_by | +------------+--------------+----------------+ | 1 | 2019-05-11 | root@localhost | +------------+--------------+----------------+ 1 row in set (0.0009 sec)