This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.
The following four scenarios show the use of a Trigger in a real-life situation.
Trigger Example - Logging deletions
Scenario: We want to keep a log file containing data from rows that have been
deleted from the
BOOKS Table. Here's a Trigger definition that accomplishes
CREATE TRIGGER Books_Delete AFTER DELETE ON Books /* See note (a) */ REFERENCING OLD ROW AS Old /* See note (b) */ FOR EACH ROW /* See note (c) */ INSERT INTO Books_Deleted_Log VALUES (Old.title); /* See note (d) */
This Trigger copies the title of every book deleted from the
into a Table (the log) called
(a): The Trigger action has to be
AFTER, since the Trigger action includes an SQL-data change statement.
(b): It is conventional to use the alias "
Old" or "
Old_Row" for the old row.
(: No log will occur for a
DELETEstatement that affects zero rows.
OLDis an alias for a single old row, so
OLD.TITLEis the scalar value derived from the
TITLEColumn of that old row.
Trigger Example - Inserting default expressions
Scenario: When we add a client, we want the default value for
HOME_TELEPHONE to be the same as the
WORK_TELEPHONE number. The
DEFAULT clause is no good for cases like this, because "
name>" is not a legal option. Here's a Trigger definition that accomplishes
CREATE TRIGGER Clients_Insert BEFORE INSERT ON Clients REFERENCING NEW ROW AS New FOR EACH ROW SET New.home_telephone = COALESCE(New.home_telephone,New.work_telephone);
SET statement causes the value on the right to be "assigned to" the
target on the left; this is part of the "Persistent Stored Modules" feature
package — see our chapter on PSM).
With this Trigger in place, this SQL statement:
INSERT INTO Clients (work_telephone) VALUES ('493-1125');
will insert a new row into
CLIENTS that has '
493-1125' in the
HOME_TELEPHONE Column as well as in the
WORK_TELEPHONE Column. This
Trigger must be activated
INSERT, but it is possible to see
in advance what values the DBMS has tentatively assigned for the new row.
Trigger Example: Constraint substitute
Scenario: The department's budget can't be changed after 5 pm. Here's a Trigger definition that accomplishes this — but it has some flaws:
CREATE TRIGGER Departments_Update AFTER UPDATE OF budget ON Departments /* first flaw */ WHEN (CURRENT_TIME > '17:00:00') /* second flaw */ SELECT MAX(budget) / 0 FROM Departments; /* third flaw */
CREATE TRIGGER statement contains no action granularity clause,
the default applies:
FOR EACH STATEMENT. This means that if this SQL
statement is executed:
UPDATE Departments SET budget = <value>;
SELECT action will be executed — and
it will fail, since it contains a division-by-zero expression. This, in turn,
will cause the Trigger event — the
statement — to fail too, since execution context is
atomic. (Actually there will be two errors. The main one will be "Triggered
action exception" and the secondary one will be "Division by zero".) Therefore,
this Trigger prevents anyone from updating
DEPARTMENTS.BUDGET after 5 pm.
Usually it works, but sometimes it doesn't work — it
contains subtle flaws.
The first flaw is that a SQL statement like this:
UPDATE Departments SET budget = <value>, name = <value>;
might fail to activate the Trigger. In strict Standard SQL, a Trigger's
UPDATE Column list — which in this case
budget" — must exactly match the Columns in
The second flaw is that the
WHEN clause is non-deterministic. In a sense,
that's the point of using a Trigger here rather than a Constraint: SQL doesn't
allow non-deterministic expressions in a Constraint.
The third flaw is that this SQL statement:
UPDATE Departments SET budget = NULL;
will pass — it won't activate the Trigger because
NULL value by zero is legal SQL syntax
— and so the Trigger doesn't accomplish its purpose
100% of the time.
We're not saying that it's bad to use Triggers as Constraint substitutes. This example only shows that you should think hard about the possible consequences before using "tricks".
Trigger Example - Cascading update
Scenario: The first time we elect Bob, we all get a 1% tax cut. On the other hand, every change in taxes will affect the national debt, and cause Bob's popularity to drop. Here are two Trigger definitions that map this situation:
CREATE TRIGGER Prime_Minister_Update AFTER UPDATE ON Prime_Ministers REFERENCING OLD ROW AS Old, NEW ROW AS New FOR EACH ROW WHEN (New.name = 'Bob' AND New.name <> Old.name) UPDATE Taxpayers SET tax_payable = tax_payable * 0.99; CREATE TRIGGER Taxpayer_Update AFTER UPDATE ON Taxpayers REFERENCING OLD ROW AS Old, NEW ROW AS New FOR EACH ROW BEGIN ATOMIC UPDATE National_Debt SET amount = amount + (New.payable - Old.payable); UPDATE Prime_Ministers SET approval_rating = approval_rating - 0.01; END;
In this example, some updates of the
PRIME_MINISTERS Table will cause an
update of the
TAXPAYERS Table, and updates of
TAXPAYERS will cause both
an update of the
NATIONAL_DEBT Table and of
PRIME_MINISTERS. Shown as a
as a symbol for "causes possible
UPDATE of", we have:
Prime_Ministers --> Taxpayers --> National_Debt ^ | | | | | -------------------
There is an apparent cycle here, but the second
PRIME_MINISTERS is for a different Column than the first, so the effects
don't cascade forever. If we said "a change in the national debt will Trigger
Bob's overthrow", then we would have a true cycle —
that would be bad. Your DBMS is supposed to detect it if the same Column
changes value twice in a cycle, and cause the Trigger action (and the SQL
data-change statement that activated the Trigger) to fail: it will return the
SQLSTATE error 27000 "triggered data change violation".