Database trigger

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should be created also in the tables of the taxes, vacations, and salaries.

The need and the usage

Triggers are commonly used to:

• audit changes (e.g. keep a log of the users and roles involved in changes)
• enhance changes (e.g. ensure that every change to a record is time-stamped by the server's clock)
• enforce business rules (e.g. require that every invoice have at least one line item)
• execute business rules (e.g. notify a manager every time an employee's bank account number changes)
• replicate data (e.g. store a record of every change, to be shipped to another database later)
• enhance performance (e.g. update the account balance after every detail transaction, for faster queries)

The examples above are called Data Manipulation Language (DML) triggers because the triggers are defined as part of the Data Manipulation Language and are executed at the time the data is manipulated. Some systems also support non-data triggers, which fire in response to Data Definition Language (DDL) events such as creating tables, or runtime or and events such as logon, commit, and rollback. Such DDL triggers can be used for auditing purposes.

The following are major features of database triggers and their effects:

• triggers do not accept parameters or arguments (but may store affected-data in temporary tables)
• triggers cannot perform commit or rollback operations because they are part of the triggering SQL statement (only through autonomous transactions)
• triggers are normally slow (slowdown the process)

Triggers in Oracle

In addition to triggers that fire when data is modified, Oracle 9i supports triggers that fire when schema objects (that is, tables) are modified and when user logon or logoff events occur. These trigger types are referred to as "Schema-level triggers".

Schema-level triggers

• After Creation
• Before Alter
• After Alter
• Before Drop
• After Drop
• Before Logoff
• After Logon

The four main types of triggers are:

1. Row Level Trigger: This gets executed before or after any column value of a row changes
2. Column Level Trigger: This gets executed before or after the specified column changes
3. For Each Row Type: This trigger gets executed once for each row of the result set caused by insert/update/delete
4. For Each Statement Type: This trigger gets executed only once for the entire result set, but fires each time the statement is executed.

Mutating tables

When a single SQL statement modifies several rows of a table at once, the order of the operations is not well-defined; there is no "order by" clause on "update" statements, for example. Row-level triggers are executed as each row is modified, so the order in which trigger code is run is also not well-defined. Oracle protects the programmer from this uncertainty by preventing row-level triggers from modifying other rows in the same table – this is the "mutating table" in the error message. Side-effects on other tables are allowed, however.

One solution is to have row-level triggers place information into a temporary table indicating what further changes need to be made, and then have a statement-level trigger fire just once, at the end, to perform the requested changes and clean up the temporary table.

Because a foreign key's referential actions are implemented via implied triggers, they are similarly restricted. This may become a problem when defining a self-referential foreign key, or a cyclical set of such constraints, or some other combination of triggers and CASCADE rules (e.g. user deletes a record from table A, CASCADE rule on table A deletes a record from table B, trigger on table B attempts to SELECT from table A, error occurs.)

Triggers in Microsoft SQL Server

Microsoft SQL Server supports triggers either after or instead of an insert, update or delete operation. They can be set on tables and views with the constraint that a view can be referenced only by an INSTEAD OF trigger.

Microsoft SQL Server 2005 introduced support for Data Definition Language (DDL) triggers, which can fire in reaction to a very wide range of events, including:

• Drop table
• Create table
• Alter table
• Login events

A full list is available on MSDN.

Performing conditional actions in triggers (or testing data following modification) is done through accessing the temporary Inserted and Deleted tables.

Triggers in PostgreSQL

PostgreSQL introduced support for triggers in 1997. The following functionality in SQL:2003 was previously not implemented in PostgreSQL:

• SQL allows triggers to fire on updates to specific columns; As of version 9.0 of PostgreSQL this feature is also implemented in PostgreSQL.
• The standard allows the execution of a number of SQL statements other than SELECT, INSERT, UPDATE, such as CREATE TABLE as the triggered action. This can be done through creating a stored procedure or function to call CREATE TABLE.

Synopsis:

SELECT * FROM MY_TABLE;
 UPDATE MY_TABLE SET A = 5;
 INSERT INTO MY_TABLE VALUES (3, 'aaa');

Triggers in Firebird

Firebird supports multiple row-level, BEFORE or AFTER, INSERT, UPDATE, DELETE (or any combination thereof) triggers per table, where they are always "in addition to" the default table changes, and the order of the triggers relative to each other can be specified where it would otherwise be ambiguous (POSITION clause.) Triggers may also exist on views, where they are always "instead of" triggers, replacing the default updatable view logic. (Before version 2.1, triggers on views deemed updatable would run in addition to the default logic.)

Firebird does not raise mutating table exceptions (like Oracle), and triggers will by default both nest and recurse as required (SQL Server allows nesting but not recursion, by default.) Firebird's triggers use NEW and OLD context variables (not Inserted and Deleted tables,) and provide UPDATING, INSERTING, and DELETING flags to indicate the current usage of the trigger.

{CREATE | RECREATE | CREATE OR ALTER} TRIGGER name FOR {table name | view name}
 [ACTIVE | INACTIVE]
 {BEFORE | AFTER}
 {INSERT [OR UPDATE] [OR DELETE] | UPDATE [OR INSERT] [OR DELETE] | DELETE [OR UPDATE] [OR INSERT] }
 [POSITION n] AS
BEGIN
 .....
END

As of version 2.1, Firebird additionally supports the following database-level triggers:

• CONNECT (exceptions raised here prevent the connection from completing)
• DISCONNECT
• TRANSACTION START
• TRANSACTION COMMIT (exceptions raised here prevent the transaction from committing, or preparing if a two-phase commit is involved)
• TRANSACTION ROLLBACK

Database-level triggers can help enforce multi-table constraints, or emulate materialized views. If an exception is raised in a TRANSACTION COMMIT trigger, the changes made by the trigger so far are rolled back and the client application is notified, but the transaction remains active as if COMMIT had never been requested; the client application can continue to make changes and re-request COMMIT.

Syntax for database triggers:

{CREATE | RECREATE | CREATE OR ALTER} TRIGGER name
 [ACTIVE | INACTIVE] ON
 {CONNECT | DISCONNECT | TRANSACTION START | TRANSACTION COMMIT | TRANSACTION ROLLBACK}
 [POSITION n] AS
BEGIN
 .....
END

Triggers in MySQL

MySQL 5.0.2 introduced support for triggers. Some of the triggers MySQL supports are

• Insert Trigger
• Update Trigger
• Delete Trigger

Note: MySQL allows only one trigger of each type on each table (i.e. one before insert, one after insert, one before update, one after update, one before delete and one after delete).

Note: MySQL does NOT fire triggers outside of a statement (i.e. API's, foreign key cascades)

The SQL:2003 standard mandates that triggers give programmers access to record variables by means of a syntax such as REFERENCING NEW AS n. For example, if a trigger is monitoring for changes to a salary column one could write a trigger like the following:

CREATE TRIGGER salary_trigger
    BEFORE UPDATE ON employee_table
    REFERENCING NEW ROW AS n, OLD ROW AS o
    FOR EACH ROW
    IF n.salary <> o.salary THEN

    END IF;
;

Triggers in IBM DB2 LUW

IBM DB2 for distributed systems known as DB2 for LUW (LUW means Linux Unix Windows) supports three trigger types: Before trigger, After trigger and Instead of trigger. Both statement level and row level triggers are supported. If there are more triggers for same operation on table then firing order is determined by trigger creation data. Since version 9.7 IBM DB2 supports autonomous transactions [1].

Before trigger is for checking data and deciding if operation should be permitted. If exception is thrown from before trigger then operation is aborted and no data are changed. In DB2 before triggers are read only — you cant modify data in before triggers. After triggers are designed for post processing after requested change was performed. After triggers can write data into tables and unlike some other databases you can write into any table including table on which trigger operates. Instead of triggers are for making views writeable.

Triggers are usually programmed in SQL PL language.

Triggers in SQLite

CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] [database_name .] trigger_name
   [BEFORE | AFTER | INSTEAD OF] {DELETE | INSERT | UPDATE [OF column_name [, column_name]...]} ON {table_name | view_name}
   [FOR EACH ROW] [WHEN condition]
BEGIN
   ...
END

SQLite only supports row-level triggers, not statement-level triggers.

Updateable views, which are not supported in SQLite, can be emulated with INSTEAD OF triggers.

Triggers in XML databases

An example of implementation of triggers in non-relational database can be Sedna, that provides support for triggers based on XQuery. Triggers in Sedna were designed to be analogous to SQL:2003 triggers, but natively base on XML query and update languages (XPath, XQuery and XML update language).

A trigger in Sedna is set on any nodes of an XML document stored in database. When these nodes are updated, the trigger automatically executes XQuery queries and updates specified in its body. For example, the following trigger cancels person node deletion if there are any open auctions referenced by this person:

CREATE TRIGGER "trigger3"
    BEFORE DELETE
    ON doc("auction")/site//person
    FOR EACH NODE
    DO
    {
       if(exists($WHERE//open_auction/bidder/personref/@person=$OLD/@id))
       then ( )
       else $OLD;
    }

External links

• Microsoft SQL Server DROP TRIGGER
• MySQL Database triggers
• DB2 CREATE TRIGGER statement
• Oracle CREATE TRIGGER
• PostgreSQL CREATE TRIGGER
• Oracle Mutating Table Problems with DELETE CASCADE
• SQLite Query Language: CREATE TRIGGER