Triggers

A trigger defines an action the database should take when some database-related event occurs. The code within a trigger, called the trigger body, is made up of PL/SQL blocks. Triggers are executed by the database when specific types of data manipulation commands are performed on specific tables. Such commands may include inserts, updates, and deletes.

Required System Privileges
۞
To create a trigger on a table, you must be able to alter that table.

۞ Therefore, you must either own the table, have the ALTER privilege for the table, or have the ALTER ANY TABLE system privilege.

۞ You must have the CREATE TRIGGER system privilege.

۞ To create triggers in another user's account (also called a schema), you must have the CREATE ANY TRIGGER system privilege.

۞ The CREATE TRIGGER system privilege is part of the RESOURCE role provided with Oracle.

۞ To create a trigger on a database-level event, you must have the ADMINISTER DATABASE TRIGGER system privilege.


Types of Triggers
A trigger's type is defined by the type of triggering transaction and by the level at which the trigger is executed.


Row-Level Triggers
Row-level triggers execute once for each row in a transaction. For the LEDGER table auditing example described earlier, each row that is changed in the LEDGER table may be processed by the trigger.

Row-level triggers are created using the for each row clause in the create trigger command.


Statement-Level Triggers
Statement-level triggers execute once for each transaction. For example, if a single transaction inserted 500 rows into the LEDGER table, then a statement-level trigger on that table would only be executed once.

Statement-level triggers are the default type of trigger created via the create trigger command.


BEFORE and AFTER Triggers
Since the events that execute triggers include database transactions, triggers can be executed immediately before or after inserts, updates, and deletes.

Within the trigger, you can reference the old and new values involved in the transaction. "Old" refers to the data as it existed prior to the transaction; updates, and deletes usually reference old values. "New" values are the data values that are the transaction creates (such as the columns in an inserted record).

If you need to set a column value in an inserted row via your trigger, then you need to use a BEFORE INSERT trigger to access the "NEW" values. Using an AFTER INSERT trigger would not allow you to set the insert value, since the row will already have been inserted into the table.


INSTEAD OF Triggers
You can use INSTEAD OF triggers to tell Oracle whet to do instead of performing the actions that invoked the trigger. For example, you could use an INSTEAD OF trigger on a view to redirect inserts into a table or to update multiple tables that are part of a view. You can use INSTEAD OF triggers on either object views or relational views.

For example, if a view involves a join of two tables, your ability to use the update command on records in the view is limited. However, if you use an INSTEAD OF trigger, you can tell Oracle how to inserts, updates, and deletes records in the view's underlying tables when a user attempts to change values via the view. The code in the INSTEAD OF trigger is executed in place of the inserts, updates, or deletes command you enter.


Schema Triggers
As of Oracle8i, you can create triggers on schema operations. The allowable triggering events include create table, alter table and drop table. You can even create triggers to prevent users from dropping their own tables. For the most part, schema-level triggers provide two capabilities: preventing DDL operations and providing additional security monitoring when DDL operations occur.


Database-Level Triggers
You can create triggers to be fired on database events, including errors, logons, logoffs, shutdowns and startup. You can use this type of trigger to automate database maintenance or auditing actions.