A database trigger is procedural code that is automatically executed in response to certain events on a particular table
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions...
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
. 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)
A data manipulation language is a family of syntax elements similar to a computer programming language used for inserting, deleting and updating data in a database...
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)
Data Definition Language
A data definition language or data description language is a syntax similar to a computer programming language for defining data structures, especially database schemas.-History:...
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
Oracle Database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
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:
Row Level Trigger: This gets executed before or after any column value of a row changes
Column Level Trigger: This gets executed before or after the specified column changes
For Each Row Type: This trigger gets executed once for each row of the result set caused by insert/update/delete
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.)
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
supports triggers either after or instead of an insert, update, or delete operation. They can be set on tables
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions...
with the constraint that a view can be referenced only by an INSTEAD OF trigger.
A data definition language or data description language is a syntax similar to a computer programming language for defining data structures, especially database schemas.-History:...
(DDL) triggers, which can fire in reaction to a very wide range of events, including:
Performing conditional actions in triggers (or testing data following modification) is done through accessing the temporary Inserted and Deleted tables.
PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
introduced support for triggers in 1997. The following functionality in SQL:2003
SQL:2003
SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.-Summary:The SQL:2003 standard makes minor modifications to all parts of SQL:1999 , and officially introduces a few new features such as:* XML-related features * Window functions* the...
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');
Firebird is an open source SQL relational database management system that runs on Linux, Windows, and a variety of Unix. The database forked from Borland's open source edition of InterBase in 2000, but since Firebird 1.5 the code has been largely rewritten ....
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 view
Materialized view
A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables, are also known as snapshots...
s. 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 officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My...
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)
SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.-Summary:The SQL:2003 standard makes minor modifications to all parts of SQL:1999 , and officially introduces a few new features such as:* XML-related features * Window functions* the...
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 http://www.ibm.com/developerworks/data/library/techarticle/dm-0907autonomoustransactions/index.html.
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.
SQL PL stands for Structured Query Language Procedural Language and was developed by IBM as a set of commands that extend the use of SQL in the IBM DB2 database system. It provides procedural programmability in addition to the querying commands of SQL. It is a subset of the SQL Persistent Stored...
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
Sedna (database)
Sedna is an open source database management system that provides native storage for XML data.The distinctive design decisions employed in Sedna are schema-based clustering storage strategy for XML data and memory management based on layered address space.- Data Organization :Data organization in...
, that provides support for triggers based on XQuery
XQuery
- Features :XQuery provides the means to extract and manipulate data from XML documents or any data source that can be viewed as XML, such as relational databases or office documents....
. Triggers in Sedna were designed to be analogous to SQL:2003
SQL:2003
SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.-Summary:The SQL:2003 standard makes minor modifications to all parts of SQL:1999 , and officially introduces a few new features such as:* XML-related features * Window functions* the...
triggers, but natively base on XML query and update languages (XPath
XPath
XPath is a language for selecting nodes from an XML document. In addition, XPath may be used to compute values from the content of an XML document...
- Features :XQuery provides the means to extract and manipulate data from XML documents or any data source that can be viewed as XML, such as relational databases or office documents....
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;
}
