How to Create and execute triggers in MySQL

Triggers are named database objects that activate when you modify table data. They can:

Examine data before it is inserted or updated, and verify deletes and updates
Act as a data filter by modifying data if it is out of range, before an insert or update
Modify how INSERT, UPDATE, and DELETE behave
Mimic the behavior of foreign keys for storage engines that do not support foreign keys
Provide logging functionality
Automatically create summary tables

Database triggers are named database objects that are maintained within a database and are activated when data within a table is modified. You can use triggers to bring a level of power and security to the data within the tables.

Trigger Features

You can use triggers to provide control over access to specific data, the ability to perform specific logging, or auditing of the data itself.

Create Triggers

The CREATE TRIGGER statement syntax is as follows:

CREATE TRIGGER trigger_name
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON table_name
FOR EACH ROW
triggered_statement

trigger_name is the name you give the trigger, and table_name is the name of the table you want the trigger to be associated with. BEFORE and AFTER indicates when the trigger activates, either before or after the triggering event, and INSERT, UPDATE, or DELETE indicates what that event is.

Example of creating a trigger:

CREATE TRIGGER City_AD AFTER DELETE
ON City
FOR EACH ROW
INSERT INTO DeletedCity (ID, Name)
VALUES (OLD.ID, OLD.Name);

Note: The table names OLD and NEW refer to virtual tables made visible to the trigger. They contain the old version of data modified by UPDATE or DELETE statements or the new version of data added by INSERT or UPDATE statements, respectively.

Trigger Events

BEFORE and AFTER

The BEFORE and AFTER keywords refer to the trigger’s activation time relative to when the data modification statement (INSERT, UPDATE, or DELETE) writes its changes to the underlying database.

The BEFORE keyword causes the trigger to execute before the data modification in question. Use BEFORE triggers to capture invalid data entries and correct or reject them prior to writing to the table.

BEFORE INSERT: Triggered before new data is added
BEFORE UPDATE: Triggered before existing data is updated (or overwritten) with new data
BEFORE DELETE: Triggered before data is deleted

The AFTER keyword defines a trigger that executes when the data modification succeeds. Use AFTER triggers to log or audit the modification of data within your databases.

AFTER INSERT: Triggered after new data is added
AFTER UPDATE: Triggered after existing data is updated (or overwritten) with new data
AFTER DELETE: Triggered after data is deleted

Trigger Error Handling

When a trigger fails, MySQL rolls back the transaction containing the statement that causes the trigger to fire. MySQL handles errors during trigger execution as follows:

1. Failed BEFORE triggers – The transaction containing the operation on the corresponding row rolls back. 2. AFTER trigger execution – BEFORE trigger events and the row operation must execute successfully.

For non-transactional tables, such rollback cannot be done. As a result, although the statement fails, any changes performed prior to the point of the error remain in effect.

Examining Triggers

SHOW CREATE TRIGGER trigger_name

This statement returns the exact string that you can use to re-create the named trigger. You must know the name of the trigger to run this statement; there is no LIKE or WHERE syntax for the SHOW CREATE TRIGGER statement.

SHOW TRIGGERS

This statement is a MySQL extension. It returns characteristics of triggers, such as the database, name, type, creator, and creation and modification dates. This statement has the advantage of being able to display specific triggers based on conditions provided in a LIKE pattern or a WHERE clause. If you do not specify a condition, the statement displays information for all triggers.

INFORMATION_SCHEMA.TRIGGERS

– Contains all data displayed by the SHOW commands. – Holds the most complete picture of the triggers available in all databases.

Dropping Triggers

When you use DROP TRIGGER trigger_name, the server looks for that trigger name in the current schema. If you want to drop a trigger in another schema, include the schema name.

1. To explicitly drop a trigger, use this syntax:

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

Use IF EXISTS to prevent the error that occurs when you attempt to delete a trigger that does not exist.

2. Implicitly drop a trigger by dropping: – The table on which the trigger is defined – The database containing the trigger

Restrictions on Triggers

Disallowed statements while creating MySQL triggeres include:

SQL-prepared statements
Explicit or implicit COMMIT and ROLLBACK
Statements that return a result set
FLUSH statements
Statements that modify the table to which the trigger applies

Triggers are not fired in response to:

Changes caused by cascading foreign keys
Changes caused during row-based replication

Result Sets Cannot Be Returned

The following are not allowed:

SELECT statements that do not have an INTO var_list clause
SHOW statements

Process a result set within a trigger either with SELECT … INTO var_list or by using a cursor and FETCH statements.

Trigger Privileges

To execute the CREATE TRIGGER and DROP TRIGGER commands, you need the TRIGGER privilege. You need additional privileges to use OLD and NEW within a trigger:

To assign the value of a column with SET NEW.col_name= value, you need the UPDATE privilege for the column.
To use NEW.col_name in an expression to refer to the new value of a column, you need the SELECT privilege for the column.

SIGNAL and RESIGNAL

MySQL supports the use of SIGNAL and RESIGNAL to raise a specific SQLSTATE. It is similar to exception handling in other languages. It can be also used for advanced flow control when handling errors.

Use SIGNAL to throw an error or warning state and RESIGNAL to forward an error or warning state originated by a prior SIGNAL. These commands provide a way for stored routines and triggers to return an error to application programs or the end user. SIGNAL provides error information to a handler, to an outer portion of the application, or to the client. For example, use SIGNAL within a stored procedure to throw an error to the code that called the stored procedure. That code can handle the error in an appropriate way.

RESIGNAL passes on the error condition information that is available during execution of a condition handler. Use RESIGNAL within a compound statement inside a stored procedure or function, trigger, or event. For example, if stored procedure p calls stored procedure q, and if q uses SIGNAL to throw an error, p can declare a handler to process signals from q. The handler in p can use the RESIGNAL statement to throw the same error information to the code that calls p.