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) 


My Personal Notes arrow_drop_up

Game Design | Cryptography | Module Development

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


3


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.