Database trigger触发器
A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.There are two classes of triggers, they 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. Triggers cannot be used to audit data retrieval via SELECT statements.
Each class can be of several types. There are "BEFORE triggers" and "AFTER triggers" which identifies the time of execution of the trigger. There is also an "INSTEAD OF trigger" which is code that gets executed instead of the triggering statement.
There are typically three triggering events that cause 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).
The trigger is used to automate DML condition process.
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 cause mutating table errors, if they are poorly written.
Contents
1 Triggers in Oracle
2 Triggers in Microsoft SQL Server
3 Triggers in PostgreSQL
4 Triggers in MySQL
5 Triggers in native XML database Sedna
6 External links
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.
Before Insert row level
After Insert row level
Before Delete row level
After Delete row level
Before Update row level
After Update row level
Before Insert Statement Level
After Insert Statement Level
Before Delete Statement Level
After Delete Statement Level
Before Update Statement Level
After Update Statement Level
Triggers in Microsoft SQL Server
Microsoft SQL Server supports triggers either after or instead of an insert, update, or delete operation.
'Microsoft SQL Server supports triggers 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.
Visit MSDN for information on using 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 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 in native XML database Sedna
Sedna 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 tr3 cancels person node deletion if there are any open auctions referenced by this person:
CREATE TRIGGER "tr3"
BEFORE DELETE
ON doc("auction")/site//person
FOR EACH NODE
DO
{
if(exists($WHERE//open_auction/bidder/personref/@person=$OLD/@id))
then ( )
else $OLD;
}
More details on the syntax, execution semantics and usage of Sedna triggers are available in Sedna Programmer's Guide.
来自：en.wikipedia.org/wiki/Trigger_(database)
页:
[1]