A trigger is a set of commands invoked automatically when an SQL statement changes data on the associated table. Other stored SQL types in MySQL are events, stored procedures, and stored functions.

A trigger can be defined to occur BEFORE or AFTER the table data is changed. Data can be changed via the INSERT, UPDATE, and DELETE statements:

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

SQL statements that do not use INSERT, UPDATE, or DELETE but change data will not invoke triggers. For example, both the TRUNCATE and DROP TABLE statements remove rows from a table, but do not use a DELETE statement to do so. Foreign key cascading actions (ON DELETE CASCADE and ON UPDATE CASCADE) do not invoke triggers in mysqld 6.0 and will be resolved when foreign keys are implemented across all storage keys in future releases.

Creating a trigger

The CREATE TRIGGER statement creates a trigger and has the following syntax:

CREATE TRIGGER triggername

[BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON tablename

FOR EACH ROW {statement}

SQL statements within a trigger are put inside the BEGIN . . . END compound statement syntax. When more than one SQL statements are there, use MySQL’s DELIMITER SQL extension to change the client delimiter while defining the trigger. Else, the first semicolon used to end the first SQL statement in the trigger will be interpreted as the end of the trigger definition and will throw error.

EXAMPLE:

delimiter |

CREATE TRIGGER emptrigger

AFTER INSERT ON employee

FOR EACH ROW

BEGIN

insert into emp values(NEW.empName,NEW.empId);

update emp set `first name`="HJK01" where id=6;

END;

|

delimiter ;

To test this insert some data into the employee table.

Finding all triggers

To view all the triggers in a database, use the SHOW TRIGGERS command. SHOW TRIGGERS LIKE ’customer’\G.

You can also query the INFORMATION_SCHEMA.TRIGGERS table as:

Select * from INFORMATION_SCHEMA.TRIGGERS

Or to get all trigger names:

select TRIGGER_NAME from INFORMATION_SCHEMA.TRIGGERS;

The SHOW CREATE TRIGGER command shows the original SQL command used to create the trigger.

Dropping a trigger

To drop a trigger, use the DROP TRIGGER statement. An error is thrown if the trigger does not exist, but error can be prevented by using the IF EXISTS phrase:

DROP TRIGGER staff_update_date;

DROP TRIGGER IF EXISTS staff_update_date;

Trigger storage and backup

Triggers are stored in the data directory (datadir) in files named tblname.TRG and triggername.TRN. The .TRG files map triggers to tables, and the .TRN files contain the trigger definition. There will be one .TRG file for each table that is affected. If we create six different triggers on four tables, there will be only four .TRG files. When a table is renamed, the trigger files are renamed too, thus no triggers are lost during a table rename. Triggers can be backed up using mysqldump and by copying the .TRG and .TRN files.

More Notes on MySQL Triggers

1. MySQL does not have a command to change an existing trigger. The only way to change a trigger is to drop the trigger and re-create it. 
2. Views and temporary tables do not support triggers.
3. Triggers defined by one user in one environment may be invoked by an action from another user in another environment.
4. By default, a trigger is saved with the sql_mode of the user defining the trigger and can lead to problems if the execution environment has a very different sql_mode. SHOW CREATE TRIGGER or SHOW TRIGGERS can be used to see the sql_mode associated with a trigger. To change the sql_mode of a trigger you have to drop the trigger set the desired sql_mode, re-create the trigger.
5. A trigger is saved with the current character set and collation stored in the character_set_client and collation_connection system variables. Changing these values in a trigger involves dropping the trigger, setting the character set and collation, and re-creating the trigger.
6. By default, the trigger is invoked as the user who defined the trigger. However, the trigger definer can be set by using a DEFINER clause in the CREATE TRIGGER statement and a SUPER user can change the DEFINER to any valid user later. If the user does not exist in the grant tables, a warning will be issued.
7. CREATE TRIGGER and DROP TRIGGER statements are not replicated; hence the trigger must be defined on each database instance.
8. Triggers cannot modify a table already being used by the statement that invoked the trigger without using the NEW and OLD aliases.
9. Triggers cannot use dynamic SQL and also many other commands such as SHOW, LOAD, BACKUP DATABASE, RESTORE, COMMIT, ROLLBACK and prepared statement related such as PREPARE, EXECUTE, and DEALLOCATE PREPARE.