Database Reference
In-Depth Information
UPDATE
triggers are executed before or after an
UPDATE
statement is executed.
Be aware of the following:
■
Within
UPDATE
trigger code, you can refer to a virtual table named
OLD
to access the previous (pre-
UPDATE
statement) values and
NEW
to
access the new updated values.
■
In a
BEFORE UPDATE
trigger, the values in
NEW
may also be updated
(allowing you to change values about to be used in the
UPDATE
state-
ment).
■
The values in
OLD
are all read-only and cannot be updated.
The following example ensures that state abbreviations are always in uppercase
(regardless of how they were actually specified in the
UPDATE
statement):
▼
Input
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
▼
Analysis
Obviously, any data cleanup needs to occur in the
BEFORE UPDATE
state-
ment as it does in this example. Each time a row is updated, the value in
NEW.
vend_state
(the value that is used to update table rows) is replaced with
Upper(NEW.vend_state
).
What about logging updates to the
orders
table? With what you have learned
here you should be able to create a
updateorder
trigger that inserts a row
into the
orders_log
table (make sure to insert a
U
for
change_type
).
Before wrapping up this chapter, here are some important points to keep in
mind when using triggers:
■
Trigger support in MariaDB is rather rudimentary at best when com-
pared to other DBMSs. There are plans to improve and enhance trig-
ger support in future versions.
■
Creating triggers might require special security access. However, trig-
ger execution is automatic. If an
INSERT
,
UPDATE
, or
DELETE
state-
ment may be executed, any associated triggers are executed, too.
