Oracle SQL Trigger

A trigger is a named program unit that is fired in response to an event.You must have the CREATE TRIGGER system privilege to create a trigger.

Use BEFORE row triggers to modify the row before the row data is written to disk.BEFORE row triggers are slightly more efficient than AFTER row triggers. With AFTER row triggers, affected data blocks must be read (logical read, not physical read) once for the trigger and then again for the triggering statement. Alternatively, with BEFORE row triggers, the data blocks must be read only once for both the triggering statement and the trigger.

FOR EACH ROW CLAUSE

If you specify FOR EACH ROW, then the trigger fires once for each row of the table that is affected by the triggering statement. The absence of the FOR EACH ROW option indicates that the trigger fires only once for each applicable statement, but not separately for each row affected by the statement.; for example if an UPDATE statement modifies column values in ten rows, the trigger will fire off ten times. If the clause is omitted, the trigger will only execute once no matter how many rows are affected.

The statement level triggers are useful for performing validation checks for the entire statement.

WHEN CLAUSE

If included, then the expression in the WHEN clause is evaluated for each row that the trigger affects.If the expression evaluates to TRUE for a row, then the trigger body executes on behalf of that row. However, if the expression evaluates to FALSE or NOT TRUE for a row (unknown, as with nulls), then the trigger body does not execute for that row. WHEN clause cannot be used with table level triggers. That is it should be used along with FOR EACH ROW clause.

Example:

SQL> create table student

(name varchar2(10),

marks number(3)

);

SQL> create table qualified

(name varchar2(10));

Trigger is created such that name is inserted into qualified table whenever marks >= 25.

create or replace trigger mytrig1

after insert or update on student

for each row

when (new.marks >= 25)

begin

dbms_output.put_line('New Values added');

insert into qualified values (:new.name);

end;

/

SQL> insert into student values('&name','&marks');

Enter value for name: arun

Enter value for marks: 44

old 1: insert into student values('&name','&marks')

new 1: insert into student values('arun ','44')

1 row created.

SQL> select * from qualified;

NAME

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

arun

STATEMENT LAVEL TRIGGER

This example creates a trigger which prints(once) whenever the student table is being updated. If row level trigger is used, it will print each time a row is updated.

create or replace trigger mytrig2

after update on student

begin

dbms_output.put_line('Student table updating');

end;

/

SQL> select * from student;

NAME MARKS

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

arun 75

bibin 49

abin 45

SQL> set serveroutput on;

SQL> update student set marks=marks+16;

Student table updating

3 rows updated.