MySQL Triggers

A trigger is a named database object that is associated with a table, which performs their own operation automatically when user performs any INSERT,UPDATE,DELETE operations on a specific table.

If you drop a table, any triggers for the table are also dropped. Triggers must have unique names within a schema. Triggers in different schema can have the same name. And you cannot associate a trigger with a TEMPORARY table or a view.

There cannot be multiple triggers for a given table that have the same trigger event and action time. For example, you cannot have two BEFORE UPDATE triggers for a table. But you can have a BEFORE UPDATE and a BEFORE INSERT trigger, or a BEFORE UPDATE and an AFTER UPDATE trigger.

Triggers are three types,
1. BEFORE Trigger
2. AFTER Trigger
3. INSTEAD of Trigger

An BEFORE Trigger will fire, before the INSERT/UPDATE/DELETE operation is executed.

An AFTER Trigger will fire, after the INSERT/UPDATE/DELETE operation is executed.

INSTEAD of Triggers performs their operations instead of performing user specified operations.

CREATE MySQL Trigger Syntax

The statement CREATE TRIGGER creates a new trigger in MySQL. Here is the syntax :

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_body is the statement to execute when the trigger activates.To execute multiple statements, use the BEGIN … END compound statement construct.

Delete MySQL Trigger Syntax

To delete a trigger, use a DROP TRIGGER statement. As usually you must specify the schema name, if the trigger is not in the current schema.

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_nam

Notes:

INSERT: The trigger activates whenever a new row is inserted into the table; for example, through INSERT, LOAD DATA, and REPLACE statements.

UPDATE: The trigger activates whenever a row is modified; for example, through UPDATE statements.

DELETE: The trigger activates whenever a row is deleted from the table; for example, through DELETE and REPLACE statements. DROP TABLE and TRUNCATE TABLE statements on the table do not activate this trigger, because they do not use DELETE.