Database trigger
Encyclopedia : D : DA : DAT : 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 access to data.
There are two classes of triggers, they are either "row triggers" or "statement triggers". With row triggers you can define an action for every row of a table, while statement triggers occur only once and are not dependent on the shape of the data.
Each class can be of several types. There are "BEFORE triggers" and "AFTER triggers" which alters the time of execution of the trigger. There is also an "INSTEAD OF trigger" which is a conditional trigger that will fire instead of the triggering statement. However, "INSTEAD OF trigger" are available only for views.
There are typically three triggering EVENTS that cause trigger 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).
BEFORE UPDATE ON employee_table FOR ALL records IF :NEW.salary <> :OLD.salary THEN do something here END IF; END;The major features and effects of database triggers are that they:
- do not accept parameters or arguments
- can commit/rollback only through autonomous transactions
- can cause mutating table errors, if they are poorly written.
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
- Before Create
- After Create
- Before Alter
- After Alter
- Before Drop
- After Drop
- After Logon
- Before Logoff
Triggers in Microsoft SQL Server
Microsoft SQL Server supports triggers either after or instead of an insert, update, or delete operation.
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
Triggers in MySQL
MySQL 5.0 introduced support for triggers. Some of the triggers MYSQL supports are
- INSERT Trigger
- UPDATE Trigger
- DELETE Trigger
See also
External links
From Wikipedia, the Free Encyclopedia. Original article here. Support Wikipedia by contributing or donating.
All text is available under the terms of the GNU Free Documentation License See Wikipedia Copyrights for details.