In my previous article i have given the details about the triggers in oracle and then we have given the details about statement level trigger in detail. The row level triggers are important to create the log or audit tables. These triggers are processing the data in table row by row. In this article we will learn more about how to write the row level trigger with multiple examples. In interviews interviewer will ask you direct question about statement level trigger and row level triggers. To understand row level triggers in detail you should know more about :old and :new qualifiers with example.
What you will find in this article?
- Old and new Qualifiers with example.
- Row level trigger with example
What are Old and New Qualifiers?
The row level triggers are used to process or give the condition row by row rather than giving condition for the transaction. So for this kind of processing you require to deal with Old and new values of the table. The oracle engine is using the qualifiers named :Old and :New to retrieve and process those values. Before going detailed in to row level trigger you should know practically what is :Old and :New Qualifiers and how it is used in DML statements.
Lets Take example of Insert Statement :
Insert into Employee
values(‘1′,’Amit’);
commit;
The above statement will insert the value in to employee table. So just remember that the both 1 and Amit values are newly inserted values in blank column so both have new qualifier.
:new.Empno = 1
:new.Empname= Amit
Lets discuss about updating the value statement ,
Update Employee set
Empname=’Rahul’ where empno=1;
The above example will set the value of Empname as Rahul. So Here,
:old.Empno=1
:new.Empname= Rahul
The value or identifier used for Empname is :new. Like this all the values for delete statements are :old.
How to write Row level trigger with example?
In this section i would like to give information about row level trigger or row trigger with real examples.
- The row level trigger will always used for each row statement. If the for each row statement is there then that trigger is called as row level trigger.
- It will fire once for each row affected by triggering event. It will not fire for transaction.
- It will not fire if triggering event is not impacting the rows.
These three statements are totally opposite to statement level triggers. We already checked the example of statement level trigger. Lets convert the same example to row level trigger and check whether above 3 statements are correct.
Step 1 : Lets create Employee table
drop table Employee;
create table Employee
( emp_id number,
ename varchar2(100)
);insert into Employee values (1,’Amit’);
insert into Employee values (2,’Pradnya’);
With first step we have inserted the values in employee table.
Step 2 : Lets create a trigger using for each row statement.
create or replace trigger T1_Row_level_example
before update —Before updating the employee table
on Employee
for each row
begin
DBMS_OUTPUT.PUT_LINE(‘Row Level trigger executed’);
end;
The above statement will create a row level trigger.
Step 3 : Testing the trigger
To test the trigger we require to update the employee table,
Update Employee set Ename=’Rahul’;
We are updating the Employee table without any condition. So the trigger will be fired twice.
The output will be,
Row Level trigger executed
Row Level trigger executed
Updated two rows
The trigger will run twice as two rows are processed and added :new value to those two rows.
Step 4 : Test trigger without processing rows
Lets fire the second statement,
Update Employee set Ename=’Rahul’ where 1=2;
The 1=2 is always false condition so this will not execute the trigger.
The output will be,
0 rows updated
This is all about the row level table. There are so many usages of row level trigger. One of the advantage using this trigger is to create audit tables and records. I will throw light on this in seperate article. I hope you get clear idea about row level trigger with examples. If you like this article or if you want more information on it kindly comment in comments section.