Open In App

Different types of MySQL Triggers (with examples)

Last Updated : 04 Jul, 2019
Improve
Improve
Like Article
Like
Save
Share
Report

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) 


Previous Article
Next Article

Similar Reads

SQL | Triggers
Trigger is a statement that a system executes automatically when there is any modification to the database. In a trigger, we first specify when the trigger is to be executed and then the action to be performed when the trigger executes. Triggers are used to specify certain integrity constraints and referential constraints that cannot be specified u
3 min read
Difference between Row level and Statement level triggers
Triggers are defined as stored programs which are automatically executed whenever some events such as CREATE, ALTER, UPDATE, INSERT, DELETE takes place.They can be defined on a database, table, view with which event is associated. Triggers can be broadly classified into Row Level and Statement Level triggers. Broadly, these can be differentiated as
1 min read
Difference between Assertions and Triggers in DBMS
1. What are Assertions? When a constraint involves 2 (or) more tables, the table constraint mechanism is sometimes hard and results may not come as expected. To cover such situation SQL supports the creation of assertions that are constraints not associated with only one table. And an assertion statement should ensure a certain condition will alway
3 min read
Different types of Procedures in MySQL
A procedure is a subroutine (like a subprogram) in a regular scripting language, stored in a database. In the case of MySQL, procedures are written in MySQL and stored in the MySQL database/server. A MySQL procedure has a name, a parameter list, and SQL statement(s). There are four different types of MySQL procedures: 1. Procedure with no parameter
5 min read
MySQL | Common MySQL Queries
MySQL server is a open-source relational database management system which is a major support for web based applications. Databases and related tables are the main component of many websites and applications as the data is stored and exchanged over the web. Even all social networking websites mainly Facebook, Twitter, and Google depends on MySQL dat
9 min read
Different Types of Database Users
A Database User is defined as a person who interacts with data daily, updating, reading, and modifying the given data. Database users can access and retrieve data from the database through the Database Management System (DBMS) applications and interfaces. Types of Database UsersDatabase users are categorized based on their interaction with the data
4 min read
MySQL | LEAD() and LAG() Function
The LEAD() and LAG() function in MySQL are used to get preceding and succeeding value of any row within its partition. These functions are termed as nonaggregate Window functions. The Window functions are those functions which perform operations for each row of the partition or window. These functions produce the result for each query row unlikely
3 min read
PHP | MySQL UPDATE Query
The MySQL UPDATE query is used to update existing records in a table in a MySQL database. It can be used to update one or more field at the same time. It can be used to specify any condition using the WHERE clause. Syntax : The basic syntax of the Update Query is - Implementation of Where Update Query : Let us consider the following table "Data" wi
2 min read
PHP | MySQL Database Introduction
What is MySQL? MySQL is an open-source relational database management system (RDBMS). It is the most popular database system used with PHP. MySQL is developed, distributed, and supported by Oracle Corporation. The data in a MySQL database are stored in tables which consists of columns and rows.MySQL is a database system that runs on a server.MySQL
4 min read
PHP | MySQL ( Creating Database )
What is a database? Database is a collection of inter-related data which helps in efficient retrieval, insertion and deletion of data from database and organizes the data in the form of tables, views, schemas, reports etc. For Example, university database organizes the data about students, faculty, and admin staff etc. which helps in efficient retr
3 min read
Article Tags :