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)