Database trigger

From Wikipedia, the free encyclopedia
Jump to: navigation, search

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 maintaining 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 also be created in the tables of the taxes, vacations and salaries.

Triggers in DBMS[edit]

Below follows a series of descriptions of how some popular DBMS support triggers.

Oracle[edit]

In addition to triggers that fire when data is modified, Oracle 9i supports triggers that fire when schema level 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[edit]

  • 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.

Microsoft SQL Server[edit]

Microsoft SQL Server supports triggers either after or instead of (but not before: http://msdn.microsoft.com/en-us//library/ms189799.aspx) 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:

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.

PostgreSQL[edit]

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.[1]

Synopsis:

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

Firebird[edit]

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

MySQL[edit]

MySQL 5.0.2 introduced support for triggers. MySQL supports these trigger types:

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

Sample Mytrigger as follows:

-- First of all, drop any other trigger with the same name 
DROP TRIGGER IF EXISTS `Mytrigger`;
-- Create New Trigger
DELIMITER $$
 
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `DB`.`mytriggers` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `DB`.`<Table Name>`
    FOR EACH ROW BEGIN
 
    END$$
 
DELIMITER ;
 
-- Example:
DROP TRIGGER IF EXISTS `Mytrigger`;
 
DELIMITER $$
CREATE TRIGGER `Mytrigger`
AFTER INSERT ON Table_Current
FOR EACH ROW
BEGIN
 
    UPDATE Table_Record
 
    SET `Value` = NEW.`Value` 
    WHERE `Name` = NEW.`Name`
    AND `Value` < NEW.`Value`;
 
END $$
DELIMITER;

IBM DB2 LUW[edit]

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 can't 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[which?] 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.

SQLite[edit]

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.

XML databases[edit]

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

References[edit]

External links[edit]