Triggers in DBMS
Below follows a series of descriptions of how some popularOracle
In addition to triggers that fire (and executeSchema-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 theSystem-level triggers
From Oracle 8i, database events - logons, logoffs, startups - can fire Oracle triggers.Microsoft SQL Server
A list of all available firing events in Microsoft SQL Server for DDL triggers is available onPostgreSQL
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,__Firebird_
FOR_[_EACH__.html"_;"title="EACH_.html"_;"title="FOR_[_EACH_">FOR_[_EACH__">EACH_.html"_;"title="FOR_[_EACH_">FOR_[_EACH______EXECUTE_PROCEDURE_funcname_(_arguments_)__Firebird_
Firebird_(database_server)">FirebirdFirebird
Firebird (database server)">Firebird Firebird and fire bird may refer to: Mythical birds * Phoenix (mythology), sacred firebird found in the mythologies of many cultures * Bennu, Egyptian firebird * Huma bird, Persian firebird * Firebird (Slavic folklore) Bird species ''Various sp ...MySQL/MariaDB
Limited support for triggers in the MySQL/MariaDBIBM 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 supportsSQLite
XML databases
An example of implementation of triggers in non-relational database can be Sedna, that provides support for triggers based onRow and statement level triggers
To understand how trigger behavior works, you need to be aware of the two main types of triggers; these are Row and Statement level triggers. The distinction between the two is how many times the code within the trigger is executed, and at what time. Suppose you have a trigger that is made to be called on an UPDATE to a certain table. Row level triggers would execute once for each row that is affected by the UPDATE. It is important to keep in mind if no rows are affected by the UPDATE command, the trigger will not execute any code within the trigger. Statement level triggers will be called once regardless of how many rows are affected by the UPDATE. Here it is important to note that even if the UPDATE command didn't affect any rows, the code within the trigger will still be executed once. Using the BEFORE and AFTER options determine when the trigger is called. Suppose you have a trigger that is called on an INSERT to a certain table. If your trigger is using the BEFORE option, the code within the trigger will be executed before the INSERT into the table occurs. A common use of the BEFORE trigger is to verify the input values of the INSERT, or modify the values accordingly. Now let's say we have a trigger that uses AFTER instead. The code within the trigger is executed after the INSERT happens to the table. An example use of this trigger is creating an audit history of who has made inserts into the database, keeping track of the changes made. When using these options you need to keep a few things in mind. The BEFORE option does not allow you to modify tables, that is why input validation is a practical use. Using AFTER triggers allows you to modify tables such as inserting into an audit history table. When creating a trigger to determine if it is statement or row level simply include the FOR EACH ROW clause for a row level, or omit the clause for a statement level. Be cautious of using additionalAfter - statement-level trigger
An Oracle syntax statement trigger that is called after an UPDATE to the phone_book table. When the trigger gets called it makes an insert into phone_book_edit_history tableBefore each - row-level trigger
This example demonstrates a BEFORE EACH ROW trigger that modifies the INSERT using a WHEN conditional. If the last name is larger than 10 letters, using the SUBSTR function we change the last_name column value to an abbreviation.Before - statement-level trigger
Using a BEFORE statement trigger is particularly useful when enforcing database restrictions. This example demonstrate how to enforce a restriction upon someone named "SOMEUSER" on the table phone_book.References
External links