Triggers in Oracle

Trigger Action

A trigger act is the procedure (pL/SQL block) that contains the SQL statement and pL/SQL code to be executed

when a triggering statement is issued and the trigger restriction evaluates to TRUE.
Like to stored procedures, a trigger action can contain SQL language constructs (variables ,constants ,cursors,

exceptions, and so on),and call stored procedures. As well, for row trigger, the statement in a trigger action

have access to column values (new and old) of the current row being handle by the trigger. TWO relationship

names provide access to the old and new values for each column.
TYPES OF Trigger
When you name a trigger , you can specify the number of times the trigger action is to be executed. once for

every row affected by the triggering statement(such as might be fired by UPDATE statement that updates many

rows), or once for the triggering statement  , no  matter how many rows it affects.

ROW 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 executed at all.
ROW triggers are helpful if the code in the trigger action depends on data provided by the triggering statement

or rows that are affected. FOR example, Figure 15-3 shows  a row trigger that uses the values of each row

affected by the triggering statement .

Triggers are stored programs, which are automatically executed or fired when some event take place.

These triggers are in fact, written to be executed to any of the following actions.

1.DML A database manipulation.

2.DDL A database definition statements like (create, alter, drop)

Triggers could be defined on tables, views, schemas, database.

Advantages of Triggers

1. Enforcing referential integrity.

2 ensure a data

3. Preventing Invalid/unacceptable Transaction

4. Imposing security consent.

Let us learn how to create a Before Trigger in Oracle.

Before Insert Trigger means that oracle will fire

Triggers before the insert operation takes place.

Syntax

The following is the syntax of before Trigger

Create [or replace ] Trigger trigger_name

Before Insert

On Table_name

[for each row ]

Declare

--variable declaration

Begin

-- Trigger Code

Exception

When ....

---Exception Handling

End;

Parameters or arguments

Or Replace is optional. It allows you to re-create

Triggers with new definition.

trigger_name is the name of trigger to create.

Before Insert indicates that this trigger will fire

before insert operations is executed.

Table_name is the name of table that the trigger is

Created on.

Let us try an example given below.

We will create a table.

SQL> create table emp(empno number primary key,

  2  last_name varchar2(50),

  3  city varchar2(50),

  4  salary varchar2(50));

Table created.

Now we will create a trigger emp_insert on emp table

which will force all last_name to uppercase.

Create or replace trigger emp_insert

before insert

on emp

for each row

declare

s_flag number;

begin

:new.last_name :=upper(:new.last_name);

End;

/

Now let us add a row in emp table as ....

SQL> insert into emp values (101,'smith','mumbai',12000);

We will get the following output.

SQL> select empno,last_name from emp;

     EMPNO LAST_NAME

--------------------------------------------------

       101 SMITH

BEFORE vs. AFTER Triggers

When defining a trigger , you can identify the trigger timing. That is , you can give whether the

Trigger action is to be executed before or after the triggering statement . BEFORE and AFTER apply

to both statement and row triggers.

BEFORE Triggers BEFORE triggers execute the trigger action before the triggering statement .

This type of trigger is generally used in the following condition:

·         BEFORE triggers are used when the trigger action should settle on whether the triggering statement should be allowed to complete. By using a BEFORE trigger for this reason, you

Can reduce unnecessary processing of the triggering statement and its eventual rollback in

Cases where an exception is raised in the trigger action.

·         BEFORE triggers are used to obtain specific column values before completing a triggering INSERT or UPDATE statement.

AFTER Triggers AFTER triggers execute the trigger action after the triggering statement is

Executed. AFTER triggers are used in the following situations:

·         AFTER triggers are used when you want the trigger statement to complete before executing

               Before executing the trigger action.

·         If a BEFORE trigger is already present , an AFTER trigger can execute different actions on the

              Same triggering statement.

Combinations

Using the options listed in the previous two divisions, you can create  four types of triggers :

·         BEFORE statement trigger  Before executing the triggering statement statement, the trigger

Action is executed.

·         Before row trigger Before modifying each row affected by the triggering statement and

Before checking appropriate integrity constraints, the trigger action is executed

Provided that the trigger restriction was not violated.

·         AFTER statement trigger  After executing the triggering statement and applying any deferred integrity constraints, the trigger action is executed.