PLSQL TRIGGERS

TRIGGER’S:-

What is a Trigger ?

A piece of logic that is executed at or triggered by a SQL *forms event.

Types of Triggers?

1.   Row Triggers:- A row Trigger is fired each time a row in the table is affected by the triggering statement.

2.   Statement Triggers:- It is fired once on behalf of the triggering statement, independent of the number of rows the triggering statement affects.

3.   Before Triggers:-  1. Before row Trigger  2. Before statement Trigger.

4.   After Triggers:- 1. After row Trigger 2. After statement Trigger.

Database triggers are event based objects which means, execution dependson the event attached to them. These events are categorized into 4 groups :

DML EVENTS.........INSERT, UPDATE, DELETE [DML TRIGGERS]

               These triggers are used in response  to  the

               Insert, Delete, Update statements on a table.

Syntax:

CREATE OR REPLACE TRIGGER <Trig.Name>

<execution time>

<event list>

ON <table name>

[FOR EACH ROW]      .... Trigger type

[WHEN <condition>]  ....   ,, condition

<plsql anonymous block> ;   

These triggers always are executed before the event and

are row level triggers.

DDL EVENTS.........CREATE, ALTER, DROP    [DDL TRIGGERS]

               These triggers allow you to write a code that

               is invoked in response to the DDL statements.

Syntax: 

------

 CREATE OR REPLACE TRIGGER <Trig.name>

 BEFORE | AFTER

 CREATE OR ALTER OR DROP ON {DATABASE | <Schema.SCHEMA>}

 <pl/sql block>;

INSTEAD-OF EVENTS..VIEW      [INSTEADOF TRIGGERS]

          These triggers allow you to write a code that

                is invoked when a user tries to insert, update

                or delete through a view.

Syntax:

CREATE OR REPLACE TRIGGER <Trig.Name>

INSTEAD OF <DML events> ON <view name>

FOR EACH ROW

[WHEN <condition>]

<pl/sql block>;

DATABASE EVENTS....STARTUP, SHUTDOWN, LOGON, 

                                      LOGOUT,  SERVER ERROR 

                                      [DATABASE EVENT TRIGGERS]

                    These triggers allow you to write a code that

          is invoked in response to database events such

          as startup, shutdown etc.

RAISE_APPLICATION_ERROR Procedure?

Oracle engine provides a procedure name RAISE_APPLICATION_ERROR that 

Allows programmers to issue user-defined error message.

Syntax:- RAISE_APPLICATION_ERROR(<error number>, <message>);

Where error number is a negative integer in the range -20000 to 

-20999. and message is a character string upto 2048 bytes.

RAISE_APPLICATION_ERROR is used in Data base Triggers.