Chapter 24 – SQL Trigger¶

Note

You are reading a digital copy of SQL-99 Complete, Really, a book that documents the SQL-99 standard.

For more information specific to CrateDB, check out the CrateDB Reference.

“If you press the SECOND dorsal fin-spine on a trigger fish, an amazing event occurs. The FIRST dorsal fin-spine goes down too.”

– Applied Ichthyology

In this chapter, we’ll describe SQL Triggers in detail, and show you the syntax to use to create, alter and destroy them.

Table of Contents

Trigger ¶

A Schema may contain zero or more Triggers. An SQL Trigger is a named chain reaction that you set off with an SQL data change statement: it specifies a set of SQL statements that are to be executed (either once for each row or once for the whole triggering INSERT, DELETE or UPDATE statement) either before or after rows are inserted into a Table, rows are deleted from a Table, or one or more Columns are updated in rows of a Table. Triggers are dependent on some Schema – the <Trigger name> must be unique within the Schema the Trigger belongs to – and are created, altered and dropped using standard SQL statements.

Triggers are similar to Constraints. (In fact, referential Constraints are now defined by the SQL Standard as merely a type of Trigger, although they don’t share the same name-space.) What distinguishes a Trigger from a Constraint is flexibility: the Trigger body may contain actual SQL procedure statements, that you define yourself. In effect, when you “create a Trigger on Table TABLE_1", you are saying “whenever (in the future) a specific sort of event occurs which changes Table TABLE_1, execute the following further SQL statements”. For example, you could create a Trigger so that, whenever TABLE_1 is updated, a counter should be incremented in a summary Table.

Triggers are a SQL3 feature, but most DBMS vendors implemented them years ago. People use Triggers to enforce business rules, to maintain logs or to substitute for Constraints (where the rules for Constraints are too restrictive).

A Trigger is defined by a descriptor that contains eight pieces of information:

The <Trigger name>, qualified by the <Schema name> of the Schema it belongs to.
The name of the Table whose data, when changed, will cause the Trigger to be activated. (This is called the Trigger’s subject Table.)
The Trigger action time, which tells your DBMS when to execute the Trigger body (either BEFORE or AFTER the Trigger event).
The Trigger event, which tells your DBMS which data change statement (either INSERT, UPDATE or DELETE), executed on the Trigger’s Table, activates the Trigger.
The Trigger Column list for the Trigger event, as well as whether the list was explicitly or implicitly defined (for UPDATE Trigger events only).
Any old values <Correlation name>, new values <Correlation name>, old values Table alias or new values Table alias defined for the Trigger.
The Trigger body: the SQL statements you want your DBMS to execute on the Trigger’s Table when the Trigger is activated.
The Trigger’s timestamp: when it was created.

To create a Trigger use the CREATE TRIGGER statement (either as a stand-alone SQL statement or within a CREATE SCHEMA statement). CREATE TRIGGER specifies the enclosing Schema, names the Trigger and defines the Trigger’s Table, action time, event and body. To destroy a Trigger, use the DROP TRIGGER statement. To change an existing Trigger, drop and then redefine it.

Trigger Names¶

A <Trigger name> identifies a Trigger. The required syntax for a <Trigger name> is:

<Trigger name> ::=
[ <Schema name>. ] unqualified name

A <Trigger name> is a <regular identifier> or a <delimited identifier> that is unique (for all Triggers) within the Schema it belongs to. The <Schema name> that qualifies a <Trigger name> names the Schema that the Trigger belongs to and can either be explicitly stated, or a default will be supplied by your DBMS, as follows:

If a <Trigger name> in a CREATE SCHEMA statement isn’t qualified, the default qualifier is the name of the Schema you’re creating.
If the unqualified <Trigger name> is found in any other SQL statement in a Module, the default qualifier is the name of the Schema identified in the SCHEMA clause or AUTHORIZATION clause of the MODULE statement which defines that Module.

Here are some examples of <Trigger name>s:

 TRIGGER_1
 -- a <Trigger name>

 SCHEMA_1.TRIGGER_1
 -- a simple qualified <Trigger name>

CATALOG_1.SCHEMA_1.TRIGGER_1
 -- a fully qualified <Trigger name>

CREATE TRIGGER Statement ¶

The CREATE TRIGGER statement names a new Trigger and defines the Trigger’s Table, action time, event and body. The required syntax for the CREATE TRIGGER statement is:

CREATE TRIGGER <Trigger name>
{BEFORE | AFTER} <trigger event> ON <Table name>
[ REFERENCING <old or new values alias list> ]
<triggered action>

   <trigger event> ::=
   INSERT |
   DELETE |
   UPDATE [ OF <trigger Column list> ]

      <trigger Column list> ::= <Column name> [ {,<Column name>} ... ]

   <old or new values alias list> ::=
   <old or new values alias>...

      <old or new values alias> ::=
      OLD [ ROW ] [ AS ] old values <Correlation name> |
      NEW [ ROW ] [ AS ] new values <Correlation name> |
      OLD TABLE [ AS ] <old values Table alias> |
      NEW TABLE [ AS ] <new values Table alias>

         <old values Table alias> ::= <identifier>

         <new values Table alias> ::= <identifier>

   <triggered action> ::=
   [ FOR EACH {ROW | STATEMENT} ] [ WHEN (search condition) ]
      <triggered SQL statement>

      <triggered SQL statement> ::=
      SQL statement |
      BEGIN ATOMIC {SQL statement;}... END

CREATE TRIGGER defines a new Trigger. A Trigger is owned by the Schema it belongs to.

The <Trigger name> identifies the Trigger and the Schema that it belongs to. Typical <Trigger name>s include the Trigger event, e.g.: Employee_Update or After_Delete_From_Employee. A <Trigger name> that includes an explicit <Schema name> qualifier belongs to the Schema named. A <Trigger name> that does not include an explicit <Schema name> qualifier belongs to the SQL-session default Schema. The <Trigger name> must be unique (for all Triggers) within the Schema that owns it.

If CREATE TRIGGER is part of a CREATE SCHEMA statement, the <Trigger name>, if explicitly qualified, must include the <Schema name> of the Schema being created; that is, it isn’t possible to create a Trigger belonging to a different Schema from within CREATE SCHEMA. For example, this SQL statement will not return an error because the <Trigger name> will default to include the qualifying <Schema name>:

CREATE SCHEMA bob
   CREATE TABLE Table_1 (column_1 SMALLINT)
   CREATE TRIGGER Trigger_1 AFTER DELETE ON Table_1
      INSERT INTO Log_table VALUES ('deleted from Table_1');
-- creates a Trigger called BOB.TRIGGER_1 in Schema BOB

This SQL statement will not return an error either because the <Trigger name> explicitly includes a qualifying <Schema name> that matches the name of the Schema being created:

CREATE SCHEMA bob
   CREATE TABLE bob.Table_1 (column_1 SMALLINT)
   CREATE TRIGGER bob.Trigger_1 AFTER DELETE ON Table_1
      INSERT INTO Log_table VALUES ('deleted from Table_1');
-- creates a Trigger called BOB.TRIGGER_1 in Schema BOB

But this SQL statement will return an error because the <Trigger name> explicitly includes a qualifying <Schema name> that is different from the name of the Schema being created:

CREATE SCHEMA bob
   CREATE TABLE bob.Table_1 (column_1 SMALLINT)
   CREATE TRIGGER sam.Trigger_1 AFTER DELETE ON bob.Table_1
      INSERT INTO Log_table VALUES ('deleted from Table_1');
-- tries to create a Trigger belonging to Schema SAM inside Schema BOB; illegal syntax

If CREATE TRIGGER is executed as a stand-alone SQL statement, the current <AuthorizationID> must either be the owner of the Schema that this new Trigger belongs to, or the Schema’s owner must be a Role that the current <AuthorizationID> may use. That is, only the owner of a Schema can create Triggers for that Schema.

The CREATE TRIGGER statement’s main parts are its Table, its event (the description of the SQL-data change statement that activates the Trigger) and its action (the SQL statements which are to be executed by the Trigger).

ON Clause¶

The ON clause of the CREATE TRIGGER statement names the Trigger’s Table: the Base table that, when changed, may cause the Trigger to act. The Table must belong to the same Schema that the Trigger will belong to, and the current <AuthorizationID> must have the TRIGGER Privilege on that Table. (You do not need a Privilege to “use” a Trigger. Triggers will be activated whenever you execute the appropriate SQL data-change statement on the Table, whether you want them or not.)

Trigger Action Time¶

The Trigger action time defines when you want the Trigger’s action to be executed: it may be either BEFORE or AFTER. Trigger action time should be BEFORE if you want the Trigger action to occur before the Trigger event. It should be AFTER if you want the Trigger action to occur after the Trigger event.

Trigger Event¶

The Trigger event defines the SQL-data change statement whose execution (on the Trigger’s Table) will activate the Trigger: it may be either INSERT, DELETE or UPDATE. In the case of UPDATE only, you may add an optional subclause that lists the Trigger Columns: the Columns on which an UPDATE will activate the Trigger (UPDATE on Columns not in the list won’t activate the Trigger). The Column list names some or all of the Trigger Table’s Columns (each may appear in the list only once). If you omit this optional subclause, the effect is as if you included an OF clause that names every Column of the Trigger’s Table. For example, given this Table definition:

CREATE TABLE Table_1 (column_1 SMALLINT, column_2 CHAR(5));

these two Trigger definitions are equivalent:

CREATE TRIGGER Trigger_1
   AFTER UPDATE ON Table_1
      INSERT INTO Log_table VALUES ('updated Table_1');

CREATE TRIGGER Trigger_1
   AFTER UPDATE OF column_1,column_2 ON Table_1
      INSERT INTO Log_table VALUES ('updated Table_1');

Here are two more Trigger definition examples:

CREATE TRIGGER Trigger_1
   AFTER INSERT ON Table_1
      INSERT INTO Log_table VALUES ('insert into Table_1');

CREATE TRIGGER Trigger_1
   AFTER DELETE ON Table_1
      INSERT INTO Log_table VALUES ('delete from Table_1');

REFERENCING Clause¶

The optional REFERENCING clause of the CREATE TRIGGER statement defines a list of one to four unique <Correlation name>s, or aliases: one name for the old row acted on by the Trigger and/or one name for the new row acted on by the Trigger and/or one name for the old Table acted on by the Trigger and/or one name for the new Table acted on by the Trigger (each may be specified once). The <keyword> AS in each alias option is noise and may be omitted. If neither ROW nor TABLE is specified, the default is ROW – for example, these four SQL statements are equivalent:

CREATE TRIGGER Trigger_1
   AFTER UPDATE ON Table_1 REFERENCING OLD ROW AS old_row_name
      FOR EACH ROW INSERT INTO Log_table VALUES ('updated Table_1');

CREATE TRIGGER Trigger_1
   AFTER UPDATE ON Table_1 REFERENCING OLD ROW old_row_name
      FOR EACH ROW INSERT INTO Log_table VALUES ('updated Table_1');

CREATE TRIGGER Trigger_1
   AFTER UPDATE ON Table_1 REFERENCING OLD AS old_row_name
      FOR EACH ROW INSERT INTO Log_table VALUES ('updated Table_1');

CREATE TRIGGER Trigger_1
   AFTER UPDATE ON Table_1 REFERENCING OLD old_row_name
      FOR EACH ROW INSERT INTO Log_table VALUES ('updated Table_1');

Your Trigger definition must include a REFERENCING clause if the Trigger action contains references to the Trigger’s <Table name> or any of its <Column name>s. The OLD values are the values before the UPDATE / DELETE Trigger event (OLD ROW and OLD TABLE are not allowed if the Trigger event is INSERT) and the NEW values are the values after the UPDATE / INSERT Trigger event (NEW ROW and NEW TABLE are not allowed if the Trigger event is DELETE). If Trigger action time is BEFORE, a REFERENCING clause may not specify OLD TABLE or NEW TABLE. Here are some more REFERENCING clause examples:

... REFERENCING OLD ROW AS old_row

... REFERENCING OLD ROW AS old_row, NEW ROW AS new_row

... REFERENCING OLD Table AS old_table, NEW Table AS new_table

(A table alias is either a <regular identifier> or a <delimited identifier>.)

Every Trigger event has its own “execution context”: it includes the old row values and/or the new row values of the Trigger’s Table. If the Trigger event is INSERT, then there are no old row values since no existing rows of a Table are affected by INSERT. If the Trigger event is DELETE, then there are no new row values since DELETE's action is to remove rows from a Table. If the Trigger event is UPDATE, then there are three row versions:

The actual row of data in the Trigger’s Table.
The “old row values” copy of the actual row – often the same as [1], but if there are two distinct UPDATE events then there might be a difference between [1] and [2]. We’ll call this the “old row” from now on. The set of all old rows is the “old Table”.
The “new row values” copy of the actual row – it contains what the DBMS proposes to change the actual row to once the UPDATE statement’s execution is completed. We’ll call this the “new row” from now on. The set of all new rows is the “new Table”. (Note: Even with a BEFORE Trigger, the “new row values” are known to the DBMS.)

A Trigger’s execution context is important (indeed, it’s mandatory) if you are going to refer to the Trigger’s Table in the Trigger’s action statements. It would usually be wrong to refer to Column COLUMN_1 of Table TABLE_1 with its <Column reference>, TABLE_1.COLUMN_1, since the <Column reference> refers to that Column in the actual Base table copy. What you really need to refer to is either the old row or the new row. The REFERENCING clause of the CREATE TRIGGER statement makes it possible to specify what context names you will use for referring to OLD ROW or NEW ROW in the Trigger action.

The execution context is “atomic” in the usual SQL sense of the word: if any action statements fail, then all action statements fail and so does the statement that caused the Trigger activation: your DBMS will return the SQLSTATE error 09000 "triggered action exception". In other words, the old row and the new row are simply destroyed and you’re left with the same thing you started with: the actual row.

Trigger Action¶

The Trigger action defines the SQL statements you want the Trigger to execute when it is activated and has three parts: the action granularity, the action when condition and the action body.

Action Granularity ¶

The optional FOR EACH clause of the CREATE TRIGGER statement defines the Trigger action granularity: it may be either FOR EACH STATEMENT (the default) or FOR EACH ROW. The action granularity tells your DBMS how big the field of action is. For example, suppose you create a Trigger for AFTER UPDATE OF column_1 ON Employees, then do this:

UPDATE Employees SET column_1 = 5;

Assume that the EMPLOYEES Table has 1000 rows. If action granularity is FOR EACH STATEMENT, the Trigger action will occur once (just for the statement). If action granularity is FOR EACH ROW, the Trigger action will occur 1000 times (once for each row of the Trigger Table). As stated earlier, the default is FOR EACH STATEMENT, but “row” granularity is more common. In fact, if your Trigger definition contains a REFERENCING clause that includes either OLD ROW or NEW ROW, it must also include an action granularity clause of FOR EACH ROW.

Action When Condition ¶

The optional WHEN clause of the CREATE TRIGGER statement defines the Trigger action when condition: it may be any parenthesized search condition. When the Trigger is activated, if the search condition is TRUE, the Trigger action will occur, if the search condition is FALSE, the Trigger action will not occur and if the search condition is UNKNOWN, then apparently the Trigger action will not occur (the Standard document is unclear about this point: see “satisfies” in the Glossary). If you omit the WHEN clause from a Trigger definition, the Trigger action will occur as soon as the Trigger is activated.

Typically, you’d add a WHEN clause to your Trigger definition if the Trigger event definition is too general for your purposes. For example, the Trigger event might be “UPDATE Employees”, but the Trigger action should occur only “WHEN salary > 1000.00”. Such specificity only makes sense if the action granularity is FOR EACH ROW.

Action Body ¶

The action body of the CREATE TRIGGER statement defines the Trigger action itself: the SQL statement(s) you want your DBMS to execute when the Trigger is activated. The action body may be either a single SQL statement or it may be a series of SQL statements, delimited by semicolons, with a BEGIN ATOMIC ... END subclause. Here are two examples of Trigger action bodies (the first shows an action body using a single SQL statement and the second shows an action body showing multiple SQL statements):

... UPDATE Table_1

... BEGIN ATOMIC
      DELETE FROM Table_1;
      DELETE FROM Table_2;
      DELETE FROM Table_3;
   END

The Trigger action may not contain a host variable or an SQL parameter reference, nor may it contain a <triggered SQL statement> that is an SQL- transaction statement, an SQL-Connection statement, an SQL-Schema statement or an SQL-session statement (see chapter 1, SQL statement classes). A variety of SQL statements are legal inside the Trigger body, though.

If Trigger action time is BEFORE, the Trigger action may include these SQL statements: DECLARE TABLE, DECLARE CURSOR, OPEN, CLOSE, FETCH, SELECT (for a single row only), FREE LOCATOR, HOLD LOCATOR, CALL, RETURN and GET DIAGNOSTICS. It may also name an SQL-invoked routine, as long as that routine isn’t one that possibly modifies SQL-data.
If Trigger action time is AFTER, the Trigger action may include all of the SQL statements allowed for BEFORE Triggers, plus: INSERT, UPDATE and DELETE. It may also name any SQL-invoked routine.

Of the SQL statements available to you for a Trigger action, OPEN, FETCH, CLOSE, single-row SELECT and GET DIAGNOSTICS are not very useful because it is forbidden to include host variables and parameters inside the action body of a Trigger definition. INSERT, UPDATE and DELETE are much more useful; often a Trigger action body consists of just one such SQL-data change statement. (Remember, though, that you can’t use them with a BEFORE Trigger.) SQL-invoked routines are useful too. In effect, a procedure in your host language program can be called by a Trigger – compare the various “callback” situations in the Windows API.

If you want to restrict your code to Core SQL, don’t use the CREATE TRIGGER statement.

Activation of Triggers ¶

In our discussion of the CREATE TRIGGER statement, we made this true, but imprecise statement: “When the Trigger event occurs, the Trigger is activated.” Let’s get more precise about the meaning of “when” and “activated”.

The meaning of “when” depends on the Trigger action time (BEFORE or AFTER) and on the Trigger’s priority relative to other Triggers or Constraints. For example, suppose we associate three Triggers and one Constraint with Table TABLE_1:

CREATE TRIGGER Trigger_1 AFTER UPDATE ON Table_1 ...;

CREATE TRIGGER Trigger_2 BEFORE UPDATE ON Table_1 ...;

CREATE TRIGGER Trigger_3 AFTER UPDATE ON Table_1 ...;

ALTER TABLE Table_1 ADD CONSTRAINT Constraint_1 ...;

What is the effect when an UPDATE on TABLE_1 is executed? Since such an UPDATE is a Trigger event for TABLE_1, several things happen – in a rigid order of events.

First, TRIGGER_2 is activated. It’s a BEFORE Trigger, so it’s first. The DBMS executes TRIGGER_2's action (if TRIGGER_2's definition includes a WHEN clause, this happens only if the search condition is TRUE).
Second, the DBMS updates TABLE_1. Trigger events follow BEFORE Trigger actions.
Third, CONSTRAINT_1 is checked. Constraint checks occur at end-of-statement.
Fourth, TRIGGER_1 is activated. It’s an AFTER Trigger, so it occurs after execution of the action statement – and after any Constraint checking associated with the action statement.
Fifth, TRIGGER_3 is activated. It’s another AFTER Trigger, and it follows TRIGGER_1 because it was created later (Triggers are timestamped so the DBMS knows the order in which Triggers were created: older Triggers have priority).

To sum it up, the order-of-execution for any Triggered SQL-data change statement is: (1) BEFORE Triggers, (2) SQL-data change statement itself, (3) Constraint checks on SQL-data change statement, (4) AFTER Triggers – and within that, old Triggers before young Triggers.

The meaning of “activated” means “the Trigger action happens”. Remember that the Trigger action is defined by three separate clauses in the CREATE TRIGGER statement:

[FOR EACH {ROW|STATEMENT}]            -- granularity
[WHEN (search condition)]             -- when condition
SQL statement |                       -- body
BEGIN ATOMIC {SQL statement;}... END

If Trigger action doesn’t include “WHEN (search condition)” or if Trigger action does include “WHEN (search condition)” and the condition is TRUE, the DBMS decides that the Trigger action has happened. This specifically means that:

If the action body is a SQL statement, the DBMS executes that SQL statement.
Otherwise, the action body must be BEGIN ATOMIC ... END, and the DBMS executes all of the SQL statements that occur between the <keyword>s ATOMIC and END, in the order that they are defined.

If any part of the Trigger action can’t be executed successfully, that SQL statement will fail and so will the entire Trigger action and so will the Trigger event statement that caused the Trigger to activate: your DBMS will return the SQLSTATE error 09000 "triggered action exception". The result is that no change is made to the Trigger’s Table or to any other Object that the Trigger might have affected.

Trigger Examples ¶

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

The Trigger action has to be AFTER, since the Trigger action includes an SQL-data change statement.
It is conventional to use the alias Old or Old_Row for the old row.
No log will occur for a DELETE statement that affects zero rows.
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".

Triggers versus Constraints ¶

The essential idea of a Trigger is that if you change a Table, you cause a given set of SQL statements to be executed. This idea is good: it allows you to associate “rules” with Tables. And it can be efficient in a client-server environment, because Trigger actions are pre-parsed and stored on the server. However, Triggers are not the only way to implement this idea. There are other ways to accomplish the same effect.

As one alternative, you could incorporate SQL statements in “method” procedures. This requires somewhat advanced procedures; see our chapter on UDTs.

As another alternative, you could use a Constraint to declare what the most important rules are. As we said earlier though, the advantage of using a Trigger, instead of a Constraint, is flexibility. You can use a wide variety of SQL statements in a Trigger, while the only things you can do with Constraints are integrity checks and foreign key options. But is flexibility a good thing? There are reasons why you should prefer the “rigidity” that Constraints imply:

Your DBMS can recognize exactly what you’re trying to accomplish, and optimize accordingly.
You know what the consequences are because people thought them through when they devised the rigid syntax – so there’s less chance of bugs.
Constraints have been part of standard SQL for a long time, while Triggers have not been.
Constraints are deferrable; Triggers aren’t. Most experts appear to agree that you should use Constraints instead of Triggers, if you can. Triggers are tricky.

DROP TRIGGER Statement ¶

The DROP TRIGGER statement destroys a Trigger. The required syntax for the DROP TRIGGER statement is:

DROP TRIGGER <Trigger name>

DROP TRIGGER destroys a Trigger. The <Trigger name> must identify an existing Trigger whose owner is either the current <AuthorizationID> or a Role that the current <AuthorizationID> may use. That is, only the <AuthorizationID> that owns the Trigger may drop it.

The effect of DROP TRIGGER <Table name>, e.g.:

DROP TRIGGER Trigger_1;

is that the Trigger named is destroyed: it will have no further effect on SQL-data. No other Objects are affected by DROP TRIGGER, since no Objects are dependent on Triggers.

If you want to restrict your code to Core SQL, don’t use the DROP TRIGGER statement.

Dialects ¶

Triggers are not part of SQL-92 or the SQL3 Core SQL specifications, but many DBMSs have had them for years – particularly “client-server” DBMSs. Since these DBMSs supported Triggers before SQL3 came out, they naturally differ from SQL3 and from each other. Here are some deviations we have noticed:

There is no TRIGGER Privilege; only Table owners can create Triggers.
A given Table can have only one BEFORE Trigger and one AFTER Trigger, or can have only a limited number of Triggers (e.g. 12) in total.
Trigger events are conglomerable, e.g.: BEFORE INSERT OR UPDATE OF ....
It is illegal to access the subject Table during the Trigger action, except through “new” and “old” row references. Such references must always be preceded by a colon (as if they’re host variables).
The WHEN clause is not supported.
Assignments do not include the keyword SET, and are legal only for Triggers.

Chapter 24 – SQL Trigger¶

Subscribe to the CrateDB Newsletter now