MySQL Trigger

A MySQL TRIGGER is a special type of stored procedure that is associated with a table, and that executes automatically when a particular event occurs for that table. A trigger must be associated with a permanent table. It can not be associated with a TEMPORARY table or a view.

Trigger names exist in the database namespace, meaning that all triggers must have unique names within a database. Triggers in different databases can have the same name.

Note: The main difference between the trigger and procedure is that a trigger is called automatically when a particular event occurs for the table. In contrast, a stored procedure must be called explicitly.

CREATE TRIGGER Statement

The MySQL CREATE TRIGGER statement is used to create a trigger. CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger.

Syntax

The syntax of creating a trigger in MySQL is given below:

CREATE TRIGGER trigger_name    
  (AFTER | BEFORE) (INSERT | UPDATE | DELETE)  
    ON table_name FOR EACH ROW  
      
    BEGIN    
      --variable declarations    
      --trigger code    
    END;

The above statement creates a trigger named trigger_name that is associated with table named table_name. The keyword AFTER or BEFORE indicates the trigger action time. The keyword INSERT, UPDATE or DELETE indicates triggering event. The statement following FOR EACH ROW defines the trigger body; that is, the statement to execute each time the trigger activates, which occurs once for each row affected by the triggering event.

Types of Triggers in MySQL

There are six types of triggers in MySQL:

Trigger Type	Description
Before Insert Trigger	Activates before the insertion of data into the table
After Insert Trigger	Activates after the insertion of data into the table
Before Update Trigger	Activates before the update of data in the table
After Update Trigger	Activates after the update of the data in the table
Before Delete Trigger	Activates before the data is removed from the table
After Delete Trigger	Activates after the deletion of data from the table

DROP TRIGGER Statement

Once a trigger is created in MySQL, it can be removed by using DROP TRIGGER statement. The DROP TRIGGER statement requires the TRIGGER privilege for the table associated with the trigger. Triggers for a table are also dropped if you drop the table.

Syntax

The syntax of removing a trigger in MySQL is given below:

DROP TRIGGER [IF EXISTS] [database.]trigger_name;

The IF EXISTS is an optional parameter that conditionally removes trigger only if it exists on the database. If a trigger is deleted which does not exist, it will raise an error.

The database name is optional. If it is omitted, the trigger is dropped from the default database. The above statement removes a trigger named trigger_name.

Example:

To drop a trigger named sales_order_insert from default database, the following statement can be used:

DROP TRIGGER sales_order_insert;