Triggers in Oracle

What is Trigger?
---------------

A trigger is a pl/sql block structure which is fired when a DML statements like
Insert, Delete, Update is executed on a database table.
A trigger is triggered automatically when an associated DML statement is executed.

Syntax
------

CREATE [OR REPLACE ] TRIGGER trigger_name
 {BEFORE | AFTER | INSTEAD OF }  {INSERT [OR] | UPDATE [OR] | DELETE}  [OF col_name]  ON table_name
 [REFERENCING OLD AS o NEW AS n]
 [FOR EACH ROW]
 WHEN (condition)
 BEGIN
 --- sql statements
 END;


[FOR EACH ROW] 
--------------
 This clause is used to determine whether a trigger must fire when each row gets affected
( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e.statement level Trigger).

WHEN (condition) 
--------------
 This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy
 the condition specified.

Types of PL/SQL Triggers
------------------------

There are two types of triggers based on the which level it is triggered.
1) Row level trigger - An event is triggered for each row upated, inserted or deleted.
2) Statement level trigger - An event is triggered for each sql statement executed.


Order of the trigger
-------------------
The following hierarchy is followed when a trigger is fired.
1) BEFORE statement trigger fires first.
2) Next BEFORE row level trigger fires, once for each row affected.
3) Then AFTER row level trigger fires once for each affected row.
   This events will alternates between BEFORE and AFTER row level triggers.
4) Finally the AFTER statement level trigger fires.


How to Know the information about Triggers
------------------------------------------
select * from user_triggers;

Cyclic cascading trigger and cascading trigger
----------------------------------------------
The below example shows how Trigger's can enter into cyclic cascading.

Let's consider we have two tables 'abc' and 'xyz'. Two triggers are created.

1) The INSERT Trigger, triggerA on table 'abc' issues an UPDATE on table 'xyz'.
2) The UPDATE Trigger, triggerB on table 'xyz' issues an INSERT on table 'abc'.

Example:
------------

The price of a product changes constantly. It is important to maintain the history of the prices of the products.

We can create a trigger to update the 'product_price_history' table when the price of
the product is updated in the 'product' table.

1) Create the 'product' table and 'product_price_history' table

CREATE TABLE product_price_history (product_id number(5), product_name varchar2(32),
supplier_name varchar2(32), unit_price number(7,2) );

CREATE TABLE product (product_id number(5), product_name varchar2(32),
supplier_name varchar2(32), unit_price number(7,2) );


2) Create the price_history_trigger and execute it.


CREATE or REPLACE TRIGGER price_history_trigger
 BEFORE UPDATE OF unit_price ON product
FOR EACH ROW
BEGIN
INSERT INTO product_price_history
VALUES (:old.product_id,  :old.product_name,  :old.supplier_name,  :old.unit_price);
 END;
/

3) Lets update the price of a product.

UPDATE PRODUCT SET unit_price = 800 WHERE product_id = 100

CREATE TABLE SOURCE_HIST                    -- Create history table
  AS SELECT SYSDATE CHANGE_DATE, ALL_SOURCE.*
  FROM   ALL_SOURCE WHERE 1=2;

CREATE OR REPLACE TRIGGER change_hist        -- Store code in hist table
  AFTER CREATE ON SCOTT.SCHEMA          -- Change SCOTT to your schema name
DECLARE
BEGIN
  IF ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
                           'PACKAGE',   'PACKAGE BODY',
                           'TYPE',      'TYPE BODY')
  THEN
     -- Store old code in SOURCE_HIST table
     INSERT INTO SOURCE_HIST
            SELECT sysdate, all_source.* FROM ALL_SOURCE
             WHERE  TYPE = ORA_DICT_OBJ_TYPE  -- DICTIONARY_OBJ_TYPE IN 8i
               AND  NAME = ORA_DICT_OBJ_NAME; -- DICTIONARY_OBJ_NAME IN 8i
  END IF;
EXCEPTION
  WHEN OTHERS THEN
       raise_application_error(-20000, SQLERRM);
END;
/


Ur's
AmarAlam