The need and the usage:
Triggers are commonly used to:
• prevent changes (e.g. prevent an invoice from being changed after it's been mailed out)
• log changes (e.g. keep a copy of the old data)
• audit changes (e.g. keep a log of the users and roles involved in changes)
• enhance changes (e.g. ensure that every change to a record is time-stamped by the server's clock, not the client's)
• enforce business rules (e.g. require that every invoice have at least one line item)
• execute business rules (e.g. notify a manager every time an employee's bank account number changes)
• replicate data (e.g. store a record of every change, to be shipped to another database later)
• enhance performance (e.g. update the account balance after every detail transaction, for faster queries)
Some systems also support non-data triggers, which fire in response to Data Definition Language (DDL) events such as creating tables, or runtime events such as logon, commit, and rollback, and may also be used for auditing purposes.
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 cancel a requested operation
• can cause mutating table errors, if they are poorly written.
DML Triggers:
There are typically three triggering events that cause data 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).
Structurally, triggers 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. DML triggers cannot be used to audit data retrieval via SELECT statements, because SELECT is not a triggering event.
Furthermore, there are "BEFORE triggers" and "AFTER triggers" which run in addition to any changes already being made to the database, and "INSTEAD OF trigger" which fully replace the database's normal activity.
Triggers do not accept parameters, but they do receive information in the form of implicit variables. For row-level triggers, these are generally OLD and NEW variables, each of which have fields corresponding to the columns of the affected table or view; for statement-level triggers, something like SQL Server's Inserted and Deleted tables may be provided so the trigger can see all the changes being made.
For data triggers, the general order of operations will be as follows:
1. a statement requests changes on a row: OLD represents the row as it was before the change (or is all-null for inserted rows), NEW represents the row after the changes (or is all-null for deleted rows)
2. each statement-level BEFORE trigger is fired
3. each row-level BEFORE trigger fires, and can modify NEW (but not OLD); each trigger can see NEW as modified by its predecessor, they are chained together
4. if an INSTEAD OF trigger is defined, it is run using OLD and NEW as available at this point
5. if no INSTEAD OF trigger is defined, the database modifies the row according to its normal logic; for updatable views, this may involve modifying one or more other tables to achieve the desired effect; if a view is not updatable, and no INSTEAD OF trigger is provided, an error is raised
6. each row-level AFTER trigger fires, and is given NEW and OLD, but its changes to NEW are either disallowed or disregarded
7. each statement-level AFTER trigger is fired
8. implied triggers are fired, such as referential actions in support of foreign key constraints: on-update or on-delete CASCADE, SET NULL, and SET DEFAULT rules
In ACID databases, an exception raised in a trigger will cause the entire stack of operations to be rolled back, including the original statement.
**************************************************
Triggers in Oracle:
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
**************************************************
Triggers in Microsoft SQL Server:
DDL Triggers:
• Drop table
• Create table
• Alter table
• Login events
**************************************************
Triggers in Firebird:
• CONNECT (exceptions raised here prevent the connection from completing)
• DISCONNECT
• TRANSACTION START
• TRANSACTION COMMIT (exceptions raised here prevent the transaction from committing, or preparing if a two-phase commit is involved)
• TRANSACTION ROLLBACK
**************************************************
Triggers in MySQL:
• Insert Trigger
• Update Trigger
• Delete Trigger
***************************************************
If you have any suggestion plese make .... :)
