TRIGGERS AND TYPES....

Triggers are procedures stored in PL/SQL or Java that run (fire) implicitly whenever a table or view is modified or when some user actions or database system actions occur.

CREATE or REPLACE TRIGGER trigger_name

BEFORE|AFTER|INSTEAD OF INSERT|UPDATE|DELETE

   ON table_name

   [FOR EACH ROW ]

DECLARE

   -- Variable declarations

BEGIN

   -- trigger code

EXCEPTION

   WHEN...

   -- Exception handling

END;

You can write triggers that fire whenever one of the following operations occurs:

1.      DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user

2.      DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database

3.      Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database

Types of Triggers

This section describes the different types of triggers:

• Row Triggers and Statement Triggers

A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.

Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected

A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once.

Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, use a statement trigger to:

Make a complex security check on the current time or user

Generate a single audit record

• BEFORE and AFTER Triggers

·         When defining a trigger, you can specify the trigger timing—whether the trigger action is to be run before or after the triggering statement. BEFORE andAFTER apply to both statement and row triggers.

·         BEFORE and AFTER triggers fired by DML statements can be defined only on tables, not on views. However, triggers on the base tables of a view are fired if anINSERT, UPDATE, or DELETE statement is issued against the view. BEFORE and AFTER triggers fired by DDL statements can be defined only on the database or a schema, not on particular tables.

• INSTEAD OF Triggers

INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.

You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.