Learn SQL Trigger ( MYSQL)

Introduction to SQL Trigger

A SQL trigger is a set of  SQL statements stored in the database. A SQL trigger is executes whenever an event(SQL INSERT, UPDATE, or DELETE statement) associated with a table occurs.

Trigger is not called directly like a stored procedure. The main difference between a trigger and a stored procedure is that a trigger is called automatically when a data modification event is made against a table whereas a stored procedure must be called explicitly.

Advantages of using SQL triggers

• SQL triggers provide an alternative way to check the integrity of data.
• SQL triggers can catch errors in business logic in the database layer.
• SQL triggers provide an alternative way to run scheduled tasks. By using SQL triggers, you don’t have to wait to run the scheduled tasks because the triggers are invoked  automatically before or after a change  is made to the data in the tables.
• SQL triggers are very useful to audit the changes of data in tables.

MySQL Triggers Implementation
 
A trigger can be defined to be invoked either before or after the data is changed by INSERT, UPDATE or DELETE statement.

BEFORE INSERT/UPDATE/DELETE – activated before data is inserted/updated/deleted into the table.
AFTER INSERT/UPDATE/DELETE – activated after data is inserted/updated/deleted into the table.

MySQL triggers storage

MySQL stores triggers in a data directory e.g., /data/classicmodels/ with the files named tablename.TRG and triggername.TRN :

- The tablename.TRG file maps the trigger to the corresponding table.
- The triggername.TRN file contains the trigger definition.
- You can back up the MySQL triggers by copying the trigger files to the backup folder.
- You can also backup the triggers using the mysqldump tool.

Creating Trigger in MySQL
 
MySQL trigger syntax

Following illustrates the syntax of the CREATE TRIGGER statement:

CREATE TRIGGER trigger_name trigger_time trigger_event
    ON table_name
    FOR EACH ROW
 BEGIN
 ...
 END;

Where:
trigger_name : Trigger name and trigger name should follow the naming convention [trigger time]_[table name]_[trigger event]. Example: before_customer_update.
trigger_time : Trigger activation time can be BEFORE or AFTER. You must specify the activation time when you define a trigger.
trigger_event: The trigger event can be INSERT, UPDATE or DELETE.

Note:
A trigger must be associated with a specific table. Without a table trigger would not exist therefore you have to specify the table name after the ON keyword.
You place the SQL statements between BEGIN and END block.

Example of MySQL trigger.
Let us start creating a tringger in mysql database before that just consider a table CUSTOMER as following for our demo.

+------------+-----------+----------+------+------------+------------------+
| customerID | firstname | lastname | sex  | DOB        | customer_email   |
+------------+-----------+----------+------+------------+------------------+
|          1 | mukesh    | kumar    | M    | 0000-00-00 | mukesh@gmail.com |
|          2 | shivam    | shakya   | M    | 2011-11-12 | shivam@gmail.com |
|          5 | ritu      | shakya   | F    | 2011-11-14 | ritu@gmail.com   |
|          6 | Abhishek  | Shakya   | M    | 1992-11-11 | abi@gmail.com    |
+------------+-----------+----------+------+------------+------------------+

Create a new table named customer_audit to keep the changes of the employee table.

CREATE TABLE customer_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    customer_lname VARCHAR(50) NOT NULL,
    changedat DATETIME DEFAULT NULL,
    action VARCHAR(50) DEFAULT NULL
);

Next, create a BEFORE UPDATE trigger that is invoked before a change is made to the CUSTOMER table.

DELIMITER $$
CREATE TRIGGER before_CUSTOMER_update
    BEFORE UPDATE ON CUSTOMER
    FOR EACH ROW
BEGIN
    INSERT INTO CUSTOMER_audit
    SET action = 'update',
         customerID = OLD.customerID,
        lastname = OLD.lastname,
        changedat = NOW();
END$$
DELIMITER ;

Here inside this trigger, we used the OLD keyword to access customerID and lastname column of the row affected by the trigger.

Note :

• In a trigger defined for INSERT, you can use NEW keyword only.  
• In the trigger defined for DELETE, there is no new row so you can use the OLD keyword only.
• In the UPDATE trigger, OLD refers to the row before it is updated and NEW refers to the row after it is updated.

Once trigger created you use SHOW TRIGGERS statement as follows:

mysql> show triggers;

Testing the trigger
Update table CUSTOMER to check if trigger is invoking or not. before update you can check customer_audit table.

Update table  CUSTOMER
SET
    lastname='Singh'
WHERE
    customerID=6;
  
Now as you can see below table that trigger had been invoked and make the old entry into customer_audit table.  

mysql> select * from customer_audit;
+----+-------------+----------------+---------------------+--------+
| id | customer_id | customer_lname | changedat           | action |
+----+-------------+----------------+---------------------+--------+
|  1 |           6 | Shakya         | 2017-03-29 06:42:50 | update |
+----+-------------+----------------+---------------------+--------+

Managing Triggers in MySQL

After creating a trigger, you can display its definition in the data folder, which contains trigger definition file. A trigger is stored as a plain text file in the following database folder:

/data_folder/database_name/table_name.trg

You can also display the trigger in MYSQL by querying the triggers table in the information_schema database as follows:

SELECT
    *
FROM
    information_schema.triggers
WHERE
    trigger_schema = 'database_name'
        AND trigger_name = 'trigger_name';

The statement allows you to view both content of the trigger and its metadata such as associated table name and definer, which is the name of MySQL user who created the trigger.

To find all triggers associated with a particular table, you use the following query:

SELECT
    *
FROM
    information_schema.triggers
WHERE
    trigger_schema = 'database_name'
        AND event_object_table = 'table_name';


MySQL SHOW TRIGGERS statement
Another quick way to display triggers in a particular database is to use SHOW TRIGGERS statement as follows:

SHOW TRIGGERS [FROM|IN] database_name
[LIKE expr | WHERE expr];

For example, if you want to view all triggers in the current database, you can use the SHOW TRIGGERS statement as follows:

SHOW TRIGGERS

To get all triggers in a specific database, you specify the database name in the SHOW TRIGGERS  statement as the statement below:

SHOW TRIGGERS FROM demodb;

It returns all triggers in the demodb database.

To get all the trigger associated with a specific table, you use the WHERE clause in the SHOW TRIGGERS statement. The following statement returns all triggers associated with the employees table:

SHOW TRIGGERS FROM demodb WHERE `table` = 'CUSTOMER';

Removing a trigger
To remove an existing trigger, you use DROP TRIGGER statement as follows:

DROP TRIGGER table_name.trigger_name;

DROP TRIGGER CUSTOMER.before_CUSTOMER_update;

NOTE:
To modify a trigger, you have to delete it first and recreate it with the new code. There is no such ALTER TRIGGER statement available in MySQL, therefore, you cannot modify an existing trigger like modifying other database objects such as tables, views, and stored procedures.