Name
CREATE TRIGGER
Synopsis
CREATE [DEFINER = {'user
'@'host
'|CURRENT_USER}] TRIGGERtrigger
{AFTER|BEFORE} {DELETE|INSERT|UPDATE} ONtable
FOR EACH ROWstatement
Only one of each trigger timing and trigger event combination
is allowed for each table. For example, a table cannot have two BEFORE INSERT
triggers, but it
can have a BEFORE INSERT
and an AFTER
INSERT
trigger.
To specify that the trigger be executed immediately before the
associated user statement, use the parameter
BEFORE
; to indicate that the trigger should be
executed immediately afterward, use AFTER
.
At this time, only three types of SQL statements can cause the
server to execute a trigger: insertions, deletions, and updates.
Specifying INSERT
, however, applies
the trigger to INSERT
statements, LOAD
DATA
statements, and REPLACE
statements—all statements
that are designed to insert data into a table. Similarly, specifying DELETE
includes both
DELETE
and REPLACE
statements
because REPLACE
potentially deletes rows as well as
inserting them.
Triggers are actions to be taken when a user requests a change
to data. Each trigger is associated with a particular table and
includes definitions related to timing and
event. A trigger timing indicates when a trigger
is to be performed (i.e., BEFORE
or
AFTER
). A trigger event is the action that causes
the trigger to be executed (i.e., a DELETE
,
INSERT
, or UPDATE
on a
specified table).
After specifying the trigger event, give the keyword
ON
followed by the table name. This is followed by ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.