O'Reilly logo

MySQL Stored Procedure Programming by Steven Feuerstein, Guy Harrison

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Creating Triggers

Triggers are created with the—you guessed it—CREATE TRIGGER statement, which has the following syntax:

    CREATE [DEFINER={user|CURRENT_USER}] TRIGGER trigger_name
      {BEFORE|AFTER}
      {UPDATE|INSERT|DELETE}
    ON table_name
    FOR EACH ROW
    trigger_statements

Let’s look at each part of the CREATE TRIGGER statement in turn:

DEFINER ={user | CURRENT_USER }

Controls the account that will be used to check privileges when the trigger is invoked. The default of CURRENT_USER indicates that the trigger statements will run with the authority of the account that issued the CREATE TRIGGER statement, rather than the account that issued the DML that caused the trigger to fire.

trigger_name

The trigger name follows the normal conventions for MySQL’s naming of database objects. While you can call your trigger virtually anything, we recommend that you adopt a predictable naming convention. There can be only one trigger for any combination of BEFORE or AFTER and UPDATE, INSERT, or DELETE (for example, there can be only one BEFORE UPDATE trigger on a table), so a sensible convention might result in triggers being given names such as table_name _bu (for a BEFORE UPDATE trigger) or table_name _ai (for an AFTER INSERT trigger).

BEFORE|AFTER

Specifies whether the trigger fires before or after the DML statement itself has been executed. We’ll discuss the implications of this shortly.

UPDATE|INSERT|DELETE

Defines the DML statement to which the trigger is associated.

ON table_name

Associates the trigger with ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required