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

• prevent changes (e.g. prevent an invoice from being changed after it's been mailed out)
• log changes (e.g. keep a copy of the old data)
• 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, not the client's)
• 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)

Some systems also support non-data triggers, which fire in response to Data Definition Language (DDL) events such as creating tables, or runtime events such as logon, commit, and rollback, and may also be used for auditing purposes.

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

• do not accept parameters or arguments (but may store affected-data in temporary tables)
• cannot perform commit or rollback operations because they are part of the triggering SQL statement (only through autonomous transactions)
• can cancel a requested operation
• can cause mutating table errors, if they are poorly written.

DML Triggers

There are typically three triggering events that cause data triggers to 'fire':

• INSERT event (as a new record is being inserted into the database).
• UPDATE event (as a record is being changed).
• DELETE event (as a record is being deleted).

Structurally, triggers are either "row triggers" or "statement triggers". Row triggers define an action for every row of a table, while statement triggers occur only once per INSERT, UPDATE, or DELETE statement. DML triggers cannot be used to audit data retrieval via SELECT statements, because SELECT is not a triggering event.

Furthermore, there are "BEFORE triggers" and "AFTER triggers" which run in addition to any changes already being made to the database, and "INSTEAD OF trigger" which fully replace the database's normal activity.

Triggers do not accept parameters, but they do receive information in the form of implicit variables. For row-level triggers, these are generally OLD and NEW variables, each of which have fields corresponding to the columns of the affected table or view; for statement-level triggers, something like SQL Server's Inserted and Deleted tables may be provided so the trigger can see all the changes being made.

For data triggers, the general order of operations will be as follows:

1. a statement requests changes on a row: OLD represents the row as it was before the change (or is all-null for inserted rows), NEW represents the row after the changes (or is all-null for deleted rows)
2. each statement-level BEFORE trigger is fired
3. each row-level BEFORE trigger fires, and can modify NEW (but not OLD); each trigger can see NEW as modified by its predecessor, they are chained together
4. if an INSTEAD OF trigger is defined, it is run using OLD and NEW as available at this point
5. if no INSTEAD OF trigger is defined, the database modifies the row according to its normal logic; for updatable views, this may involve modifying one or more other tables to achieve the desired effect; if a view is not updatable, and no INSTEAD OF trigger is provided, an error is raised
6. each row-level AFTER trigger fires, and is given NEW and OLD, but its changes to NEW are either disallowed or disregarded
7. each statement-level AFTER trigger is fired
8. implied triggers are fired, such as referential actions in support of foreign key constraints: on-update or on-delete CASCADE, SET NULL, and SET DEFAULT rules

In ACID databases, an exception raised in a trigger will cause the entire stack of operations to be rolled back, including the original statement.

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 two main types of triggers are:

1. Row Level Trigger
2. Statement Level Trigger

Based on the 2 types of classifications, we could have 12 types of triggers.

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 is not implemented in PostgreSQL:

• SQL allows triggers to fire on updates to specific columns; PostgreSQL does not support this feature.
• The standard allows the execution of a number of SQL statements other than SELECT, INSERT, UPDATE, such as CREATE TABLE as the triggered action.

Synopsis:

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
    ON TABLE [ FOR [ EACH ] { ROW | STATEMENT } ]
    EXECUTE PROCEDURE funcname ( arguments )

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.

PostgreSQL's database-level constraints may perform a similar function to a TRANSACTION COMMIT trigger, though they are oriented toward detecting and preventing disallowed conditions, not performing arbitrary operations.

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

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 on IBM Midrange Systems

IBM Midrange Systems (AS/400, iSeries, etc.) support triggers on Insert, Update, and Delete operations to DB2 physical files ("tables" in SQL parlance), both before and after the operation occurs. Trigger programs may be created in any high-level language, or in MI. They are attached to files with the ADDPFTRG command, and removed with the RMVPFTRG command. A DSPFD on a physical file returns an entire section on attached triggers, and the PRTTRGPGM command will generate a spooled report on all triggers in a library.

External links

• Microsoft SQL Server CREATE TRIGGER
• Microsoft SQL Server DROP TRIGGER
• MySQL Database triggers
• DB2 CREATE TRIGGER statement
• Oracle CREATE TRIGGER
• PostgreSQL CREATE TRIGGER
• Oracle How to get Rid of Mutating Table Error?
• Oracle Mutating Table Problems with DELETE CASCADE