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)
Contact Us