Movatterモバイル変換

CREATE    [DEFINER =user]    TRIGGER [IF NOT EXISTS]trigger_nametrigger_timetrigger_event    ONtbl_name FOR EACH ROW    [trigger_order]trigger_bodytrigger_time: { BEFORE | AFTER }trigger_event: { INSERT | UPDATE | DELETE }trigger_order: { FOLLOWS | PRECEDES }other_trigger_name

This statement creates a new trigger. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. The trigger becomes associated with the table namedtbl_name, which must refer to a permanent table. You cannot associate a trigger with aTEMPORARY table or a view.

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

IF NOT EXISTS prevents an error from occurring if a trigger having the same name, on the same table, exists in the same schema.

This section describesCREATE TRIGGER syntax. For additional discussion, seeSection 27.4.1, “Trigger Syntax and Examples”.

CREATE TRIGGER requires theTRIGGER privilege for the table associated with the trigger. If theDEFINER clause is present, the privileges required depend on theuser value, as discussed inSection 27.7, “Stored Object Access Control”. If binary logging is enabled,CREATE TRIGGER might require theSUPER privilege, as discussed inSection 27.8, “Stored Program Binary Logging”.

TheDEFINER clause determines the security context to be used when checking access privileges at trigger activation time, as described later in this section.

trigger_time is the trigger action time. It can beBEFORE orAFTER to indicate that the trigger activates before or after each row to be modified.

Basic column value checks occur prior to trigger activation, so you cannot useBEFORE triggers to convert values inappropriate for the column type to valid values.

trigger_event indicates the kind of operation that activates the trigger. Thesetrigger_event values are permitted:

Thetrigger_event does not represent a literal type of SQL statement that activates the trigger so much as it represents a type of table operation. For example, anINSERT trigger activates not only forINSERT statements but alsoLOAD DATA statements because both statements insert rows into a table.

A potentially confusing example of this is theINSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax: aBEFORE INSERT trigger activates for every row, followed by either anAFTER INSERT trigger or both theBEFORE UPDATE andAFTER UPDATE triggers, depending on whether there was a duplicate key for the row.

It is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you can have twoBEFORE UPDATE triggers for a table. By default, triggers that have the same trigger event and action time activate in the order they were created. To affect trigger order, specify atrigger_order clause that indicatesFOLLOWS orPRECEDES and the name of an existing trigger that also has the same trigger event and action time. WithFOLLOWS, the new trigger activates after the existing trigger. WithPRECEDES, the new trigger activates before the existing trigger.

trigger_body is the statement to execute when the trigger activates. To execute multiple statements, use theBEGIN ... END compound statement construct. This also enables you to use the same statements that are permitted within stored routines. SeeSection 15.6.1, “BEGIN ... END Compound Statement”. Some statements are not permitted in triggers; seeSection 27.9, “Restrictions on Stored Programs”.

Within the trigger body, you can refer to columns in the subject table (the table associated with the trigger) by using the aliasesOLD andNEW.OLD.col_name refers to a column of an existing row before it is updated or deleted.NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.

Triggers cannot useNEW.col_name or useOLD.col_name to refer to generated columns. For information about generated columns, seeSection 15.1.21.8, “CREATE TABLE and Generated Columns”.

MySQL stores thesql_mode system variable setting in effect when a trigger is created, and always executes the trigger body with this setting in force,regardless of the current server SQL mode when the trigger begins executing.

TheDEFINER clause specifies the MySQL account to be used when checking access privileges at trigger activation time. If theDEFINER clause is present, theuser value should be a MySQL account specified as'user_name'@'host_name',CURRENT_USER, orCURRENT_USER(). The permitteduser values depend on the privileges you hold, as discussed inSection 27.7, “Stored Object Access Control”. Also see that section for additional information about trigger security.

If theDEFINER clause is omitted, the default definer is the user who executes theCREATE TRIGGER statement. This is the same as specifyingDEFINER = CURRENT_USER explicitly.

MySQL takes theDEFINER user into account when checking trigger privileges as follows:

Within a trigger body, theCURRENT_USER function returns the account used to check privileges at trigger activation time. This is theDEFINER user, not the user whose actions caused the trigger to be activated. For information about user auditing within triggers, seeSection 8.2.23, “SQL-Based Account Activity Auditing”.

If you useLOCK TABLES to lock a table that has triggers, the tables used within the trigger are also locked, as described inLOCK TABLES and Triggers.

For additional discussion of trigger use, seeSection 27.4.1, “Trigger Syntax and Examples”.