Editing Database trigger (section)

==Triggers in DBMS ==
Below follows a series of descriptions of how some popular [[DBMS]] support triggers.

=== Oracle ===
In addition to triggers that fire (and execute [[PL/SQL]] code) when data is modified, [[Oracle Database|Oracle 10g]] supports triggers that fire when schema-level objects (that is, tables) are modified and when user logon or logoff events occur.

==== Schema-level triggers ====

* After Creation
* Before Alter
* After Alter
* Before Drop
* After Drop
* Before Insert

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]] affected by an insert/update/delete.
# For each statement type: This trigger gets executed only once for the entire result set, but also fires each time the statement is executed.

==== System-level triggers ====
From [[Oracle8i|Oracle 8i]], database events - logons, logoffs, startups - can fire Oracle triggers.<ref>
{{cite book
| last1 = Nanda
| first1 = Arup
| last2 = Burleson
| first2 = Donald K.
| chapter = 9
| editor1-last = Burleson
| editor1-first = Donald K.
| title = Oracle Privacy Security Auditing: Includes Federal Law Compliance with HIPAA, Sarbanes Oxley and the Gramm Leach Bliley Act GLB
| url = https://books.google.com/books?id=Gm2hzeWhou0C
| series = Oracle in-focus series
| year = 2003
| volume = 47
| location = Kittrell, North Carolina
| publisher = Rampant TechPress
| publication-date = 2003
| page = 511
| isbn = 9780972751391
| access-date = 2018-04-17
| quote = [...] system-level triggers [...] were introduced in Oracle8i. [...] system-level triggers are fired at specific system events such as logon, logoff, database startup, DDL execution, and servererror [...].
}}
</ref>

=== Microsoft SQL Server ===

MS SQL Server supports trigger for DML and DDL statement plus special trigger "logon".

The scope of DDL triggers can be a database (CREATE TRIGGER name ON DATABASE ...) or the entire SQL Server instance (CREATE TRIGGER name ON ALL SERVER). When you use the entire instance, you can capture all events executed on commands that have server-level scope as well as any commands that have database-level scope in the SQL instance.

A list of all available firing events in Microsoft SQL Server for DDL triggers is available on [[Microsoft Docs]].<ref>{{Cite web|url=https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-events?view=sql-server-ver15|title=DDL Events - SQL Server|date=15 March 2023 }}</ref>

Performing conditional actions in DML triggers (or testing data following modification) is done through accessing the pseudo tables (temporary ones) Inserted and Deleted tables. DML trigger are always "FOR EACH STATEMENT" but can be code at row event ("FOR EACH ROW")  using a cursor on the inserted/deleted pseudo tables.

=== 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 (SQL)|SELECT]], [[Insert (SQL)|INSERT]], [[Update (SQL)|UPDATE]], such as CREATE TABLE as the triggered action. This can be done through creating a stored procedure or function to call CREATE TABLE.<ref>{{Cite web|url=https://www.postgresql.org/docs/9.0/sql-createtrigger.html|title=PostgreSQL: Documentation: 9.0: CREATE TRIGGER|website=www.postgresql.org|date=8 October 2015}}</ref>

Synopsis:

<syntaxhighlight lang="postgresql">
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
    ON TABLE [ FOR [ EACH ] { ROW | STATEMENT } ]
    EXECUTE PROCEDURE funcname ( arguments )
</syntaxhighlight>

=== Firebird ===
[[Firebird (database server)|Firebird]] supports multiple row-level, BEFORE or AFTER, INSERT, UPDATE, DELETE (or any combination of 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.

<syntaxhighlight lang="sql">
{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
</syntaxhighlight>

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

<syntaxhighlight lang="sql">
{CREATE | RECREATE | CREATE OR ALTER} TRIGGER name
 [ACTIVE | INACTIVE] ON
 {CONNECT | DISCONNECT | TRANSACTION START | TRANSACTION COMMIT | TRANSACTION ROLLBACK}
 [POSITION n] AS
BEGIN
 .....
END
</syntaxhighlight>

=== MySQL/MariaDB ===
Limited support for triggers in the MySQL/MariaDB [[DBMS]] was added in the 5.0 version of MySQL, launched in 2005.<ref>MySQL 5.0 Reference Manual. [https://downloads.mysql.com/docs/refman-5.0-en.pdf "Triggers.  MySQL 5.0 added limited support for triggers"], ''[[Oracle Corporation]]'', Retrieved on 4 March 2020.</ref> 

As of version 8.0, they allow for DDL (Data Definition Language) triggers and for DML (Data Manipulation Language) triggers. They also allow either type of DDL trigger (AFTER or BEFORE) to be used to define triggers. They are created by using the clause ''CREATE TRIGGER'' and deleted by using the clause ''DROP TRIGGER''. The statement called upon an event happens is defined after the clause ''FOR EACH ROW'', followed by a keyword (''SET'' or ''BEGIN''), which indicates whether what follows is an expression or a statement respectively.<ref>{{Cite web|url=https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html|title = MySQL :: MySQL 8.0 Reference Manual :: 25.3.1 Trigger Syntax and Examples}}</ref>

=== IBM DB2 LUW ===
IBM DB2 for distributed systems known as DB2 for LUW (LUW means '''L'''inux, '''U'''nix, '''W'''indows) 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]].<ref>{{Cite web|url=http://www.ibm.com/developerworks/data/library/techarticle/dm-0907autonomoustransactions/index.html|title=Autonomous transactions|date=July 30, 2009|website=www.ibm.com}}</ref>

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&nbsp;— 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|date=June 2012}} 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 ===

<syntaxhighlight lang="sql">
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 is mandatory ]
BEGIN
   ...
END</syntaxhighlight>

[[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===
An example of implementation of triggers in non-relational database can be [[Sedna (database)|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:
<syntaxhighlight lang="xql">
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;
    }
</syntaxhighlight>