A database trigger is a procedure that is automatically invoked by the DBMS in response to a change event against the database. A database that has triggers attached to it is called an active database.
The change events and the timing of the firing are specified within the trigger code and they are:
· Before Insert
· After Insert
· Before Update
· After Update
· Before Delete and
· After Delete
The importance of triggers emanates from the fact that they are fired according to their set-up regardless of the source that is requesting the change. For example, when database triggers are fired in response to an Update operation, the trigger code is executed whether application 1, application 2, or the database administrator through the SQL interface of the DBMS is performing the operation.
The frequency of firing the database trigger can also be controlled from within the code of the trigger. The trigger can fire once for each row being affected by the operation (Row-level trigger) or it can fire only once regardless of the number of rows being affected by the operation (Statement-level trigger).
While database triggers are especially suited for auditing and statistical gathering, there is no need or no way to write a standard for limiting the scope of their use by application programmers. The standard however recommends that application suppliers and database administrators document database triggers because the maintenance of active databases is very difficult. The documentation is necessary because maintenance personnel must trace the error condition to either application code or database trigger action. Finally, some conditions may cause database triggers to fire in a chain reaction and this needs to be documented.
The database trigger documentation should include the name of the trigger, the firing event or operation, the frequency of the firing, the name of the source table, the name of any table(s) affected by the trigger, a short description of the actions of the trigger.