a database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. the trigger is mostly used for maintaining 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 also be created in the tables of the taxes, vacations and salaries. ==triggers in dbms == below follows a series of descriptions of how some popular dbms support triggers. === oracle === in addition to triggers that fire when data is modified, oracle 9i supports triggers that fire when schema level 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 affected by an 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. === microsoft sql server === microsoft sql server supports triggers either after or instead of (but not before: http://msdn.microsoft.com/en-us//library/ms189799.aspx) an insert, update or delete operation. they can be set 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. === postgresql === 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, 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.http://www.postgresql.org/docs/9.0/static/s
Database_management_systems