Introduction

Triggers are used whenever referential integrity and other declarative constraints are not sufficient.

You may want to enforce a more complex form of referential integrity involving more detailed checking, or you may want to enforce checking on new data but allow legacy data to violate constraints. Another use for triggers is in logging the activity on database tables, independent of the applications using the database.

Trigger execution permissions-Triggers execute with the permissions of the owner of the associated table, not the user ID whose actions cause the trigger to fire. A trigger can modify rows in a table that a user could not modify directly.

Triggers can be defined on one or more of the following triggering actions:

Action	Description
INSERT	The trigger is invoked whenever a new row is inserted into the table associated with the trigger
DELETE	The trigger is invoked whenever a row of the associated table is deleted.
UPDATE	The trigger is invoked whenever a row of the associated table is updated.
UPDATE OF column-list	The trigger is invoked whenever a row of the associated table is updated such that a column in the column-list has been modified

Triggers can be defined as row-level triggers or statement-level triggers. Row-level triggers can execute BEFORE or AFTER each row modified by the triggering insert, update, or delete operation is changed. Statement-level triggers execute after the entire operation is performed.

Flexibility in trigger execution time is particularly useful for triggers that rely on referential integrity actions such as cascaded updates or deletes being carried out, or not, as they execute.

If an error occurs while a trigger is executing, the operation that fired the trigger fails. INSERT, UPDATE, and DELETE are atomic operations (see "Atomic compound statements"). When they fail, all effects of the statement (including the effects of triggers and any procedures called by triggers) are undone.

Syntax

CREATE TRIGGER trigger-name trigger-time trigger-event [, trigger-event,..]
... ON table-name
... [ REFERENCING [ OLD AS old-name ]
            [ NEW AS new-name ] ]
... [ FOR EACH { ROW | STATEMENT } ] MODE DB2SQL
triggered-action
... [ WHEN ( search-condition ) ]

Examples

The following CREATE TRIGGER statement is appropriate for statement-level UPDATE triggers:

CREATE TRIGGER mytrigger AFTER UPDATE ON employee
REFERENCING NEW AS table_after_update
            OLD AS table_before_update
FOR EACH STATEMENT
BEGIN
   ...
END

The REFERENCING NEW and REFERENCING OLD clause allows the UPDATE trigger code to refer to both the old and new values of the rows being updated. Columns in the new row are referred to with the table alias table_after_update and columns in the old row are referred to with the table alias table_before_update.

The REFERENCING NEW and REFERENCING OLD clause has a slightly different meaning for statement-level and row-level triggers. For statement-level triggers the REFERENCING OLD or NEW aliases are table aliases, while in row-level triggers they refer to the row being altered.

New Employee Insertion Trigger

CREATE TRIGGER NEW_HIRED
  AFTER INSERT ON EMPLOYEE
  FOR EACH ROW MODE DB2SQL
  UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1

Stock Quote Update Trigger

Consider an application which records and tracks changes to stock prices. The database contains two tables, CURRENTQUOTE and QUOTEHISTORY.

Tables:
CURRENTQUOTE (SYMBOL, QUOTE, STATUS)
QUOTEHISTORY (SYMBOL, QUOTE, QUOTE_TIMESTAMP)

When the QUOTE column of CURRENTQUOTE is updated, the new quote should be copied, with a timestamp, to the QUOTEHISTORY table. Also, the STATUS column of CURRENTQUOTE should be updated to reflect whether the stock is:

rising in value;
at a new high for the year;
dropping in value;
at a new low for the year;
steady in value.

CREATE TRIGGER STOCK_STATUS
      NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
      REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
      FOR EACH ROW MODE DB2SQL
      BEGIN ATOMIC
         SET NEWQUOTE.STATUS =
            CASE
               WHEN NEWQUOTE.QUOTE >
                     (SELECT MAX(QUOTE) FROM QUOTEHISTORY
                      WHERE SYMBOL = NEWQUOTE.SYMBOL
                      AND YEAR(QUOTE_TIMESTAMP) = YEAR(CURRENT DATE) )
                  THEN 'High'
               WHEN NEWQUOTE.QUOTE <
                     (SELECT MIN(QUOTE) FROM QUOTEHISTORY
                      WHERE SYMBOL = NEWQUOTE.SYMBOL
                      AND YEAR(QUOTE_TIMESTAMP) = YEAR(CURRENT DATE) )
                  THEN 'Low'
               WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE
                  THEN 'Rising'
               WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE
                  THEN 'Dropping'
               WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE
                  THEN 'Steady'
            END;
      END

In another example, we want to store a user password in the database. However, we don't want it stored in clear text, we would rather have it encrypted. Various databases allow a number of different encryption schemes. In this example, MySQL applies an SHA-2 cryptographic hash function to the clear text password before storing it. For example,

select SHA2('abc', 0);

produces the hashed string:

ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad

which is always 64 characters long. (Different hash functions may produce different lengths of string.)

Because the hashing and encryption of the password is done by a trigger, the application program is not involved. In this example, the stamp column records when the record was updated.

CREATE TRIGGER `trUserInsert`
BEFORE INSERT ON `user_base`.`user` FOR EACH ROW
BEGIN
  SET NEW.userStamp = now();
  -- A hashed password is 64 characters long.
  IF LENGTH(NEW.userPassword) != 64 THEN
    SET NEW.userPassword = SHA2(NEW.userPassword,0);
  END IF;
END

and:

CREATE TRIGGER `trUserUpdate`
BEFORE UPDATE ON `user_base`.`user` FOR EACH ROW
BEGIN
  SET NEW.userStamp = now();
  -- A hashed password is 64 characters long.
  IF LENGTH(NEW.userPassword) != 64 THEN
    SET NEW.userPassword = SHA2(NEW.userPassword,0);
  END IF;
END

These two triggers update the user password on insertion and update. The triggers check the length of the new user password because you do not want to apply the hash function to an already hashed value. This trigger would obviously fail to protect against that if the user enters a 64 character password string, so the application program need to put a limit of the size of the clear text password the user can enter.

You can then validate the password by comparing the value stored in the database against a clear text value entered by the user:

SELECT * FROM user WHERE userPassword = SHA2($userInput);

This selection fails if the hashed version of the value entered by the user ($userInput) doesn't match the value already stored in the database. Note that there is no way to recover the original password from the database except by comparison to the correct clear text value.

Insertion / Update Prevention Triggers

Insertions and updates can fail for many reasons - invalid data types, NULL where NULL is not allowed, invalid foreign keys, repeated primary key values, etc. For insertions and updates that are not caught by any of the above, a trigger can be used to validate input and cancel an insertion or update. The difficulty is that the trigger must have some way of actually forcing the insertion or update to fail.

One approach is to have the trigger violate one of table's constraints. In the following example, the trigger uses the TRIM function to strip leading and trailing spaces off of the new value for the k_desc column. If the resulting value is an empty string, it changes the k_desc value to NULL. Because that column does not allow NULL, the insertion fails with the error message SQL Error (1048): Column 'k_desc' cannot be NULL:

CREATE TRIGGER `trInsertKDesc`
BEFORE INSERT ON `keyword` FOR EACH ROW
BEGIN
  IF TRIM(new.k_desc) = '' THEN
    SET new.k_desc = NULL;
  END IF;
END

(SQL Error 1048 refers to the fact that the column cannot be NULL)

The advantage to this approach is that it should work for most SQL dialects, as it simply makes use of existing constraints to cause an insertion failure.

The drawback to this approach is that the resulting error message can be misleading, as it is the error message for the constraint failure, and may not address the actual problem.

An alternative approach for MySQL is to force an insertion failure through the SIGNAL command. SIGNAL is a MySQL command that allows you to send messages to the server or client as appropriate. These messages can include the affected table name, column name, error code, or a custom message. The following trigger definition also forces the k_desc column to contain a non-empty string:

BEGIN
  IF TRIM(new.k_desc) = '' THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'keyword description cannot be an empty string';
  END IF;
END

This trigger causes the message SQL Error (1644): keyword description cannot be an empty string to appear, and cancels the insertion.

(SQLSTATE '45000' refers to an Unhandled user-defined exception condition, i.e. you are telling the client that you are generating a user-defined error. SQL Error (1644) means that the system has caught an unhandled user-defined exception.

The advantage of this approach is that the error messages can be tailored to the exact problem. You could define multiple error messages within an IF - ELSE IF - END IF structure.

The disadvantage is that this approach is not as portable to other SQL dialects as the first approach. Given that the SQL trigger code itself may not be portable, this is not a terrible thing.