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.