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 this:

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 BOOKS Table into a Table (the log) called BOOKS_DELETED_LOG.

  • Note (a): The Trigger action has to be AFTER, since the Trigger action includes an SQL-data change statement.
  • Note (b): It is conventional to use the alias "Old" or "Old_Row" for the old row.
  • Note (c): No log will occur for a DELETE statement that affects zero rows.
  • Note (d): OLD is an alias for a single old row, so OLD.TITLE is the scalar value derived from the TITLE Column 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 "DEFAULT <Column name>" is not a legal option. Here's a Trigger definition that accomplishes this:

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);

(The 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 BEFORE the 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 */

Since this 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>;

the Trigger's 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 UPDATE 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 explicit UPDATE Column list which in this case is "budget" must exactly match the Columns in the UPDATE statement.

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 dividing a 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 diagram, with --> as a symbol for "causes possible UPDATE of", we have:

Prime_Ministers --> Taxpayers --> National_Debt
     ^                 |
     |                 |
     |                 |
     -------------------

There is an apparent cycle here, but the second UPDATE to 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".

Comments

Comments loading...