Database Reference
In-Depth Information
When creating a trigger you need to specify four pieces of information:
The unique trigger name
■
The table to which the trigger is to be associated
■
The action that the trigger should respond to (
DELETE
,
INSERT
, or
UPDATE
)
■
When the trigger should be executed (before or after processing)
■
Triggers are created using the
CREATE TRIGGER
statement. Here is a really
simple example (which doesn't actually do anything useful, but helps explain
the syntax needed):
▼
Input
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW
BEGIN
END;
▼
Analysis
CREATE TRIGGER
is used to create the new trigger named
newproduct
.
Triggers can be executed before or after an operation occurs, and here
AFTER
INSERT ON
is specified so the trigger will execute after a successful
INSERT
statement has been executed. The trigger then specifies
FOR EACH ROW
and
the code to be executed for each inserted row. So, whenever a product is
added to the
products
table, this trigger will run, and any code between
BEGIN
and
END
will be executed. And as there is nothing between
BEGIN
and
END
, well, the trigger will run, but it doesn't actually do anything.
Triggers are defined per time per event per table, and only one trigger per time
per event per table is allowed. As such, up to six triggers are supported per
table (
BEFORE
and
AFTER
each
INSERT
,
UPDATE
, and
DELETE
). A single trig-
ger cannot be associated with multiple events or multiple tables, so if you need
a trigger to be executed for both
INSERT
and
UPDATE
operations, you need to
define two triggers.
Note
When Triggers Fail If a
BEFORE
trigger fails, MariaDB will not perform the requested
operation. In addition, if either a
BEFORE
trigger or the statement itself fail, MariaDB
will not exdcute an
AFTER
trigger (if one exists).
