TRIGGERS

A trigger is an database object which executes automatically
in response to a an on a particular table in a database.
the event can be insert , update or delete.

Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events:

A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).

A database definition (DDL) statement (CREATE, ALTER, or DROP).

A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers could be defined on the table, view, schema, or database with which the event is associated.

Benefits of Triggers

Triggers can be written for the following purposes:

Generating some derived column values automatically

Enforcing referential integrity

Event logging and storing information on table access

Auditing

Synchronous replication of tables

Imposing security authorizations

Preventing invalid transactions

INSERT : fires a trigger whenever a new record is being inserted
UPDATE : Fires a trigger whenever a new record is being updated.
DELETE : Fires a trigger whenever a new record is being Deletd.


A trigger has three parts
1. Trigger event : can be update, insert or delete that causes trigger to fire
2. Trigger constraints : the condition that must be set to true in order to fire a trigger.
3. trigger action: A set of code that should be executed when a triggered is fired.


Types of triggers:


1. ROW LEVEl : fires each time whenever a row is being effected by trigger event.
We use 'FOR EACH 'keyword to specify Row level trigger.
2. STATEMENt LEVEL : fired only once irrespective to the number of rows being effected by trigger event.


3. INSTED OF : it is used with view to update the table which is not being updated
directly ( Because of key preserved tables, Google it for more info)


NOTE: There are two more triggers : Database level and schema level triggers mostly used by database administrators
For Auditing purpose