In my previous article I have given the details about the oracle triggers and different types of oracle triggers. The triggers are nothing but the conditions where we can manage multiple events. There are two types of DML triggers that is statement level trigger and row level trigger. In this article I would like to give the example of Statement level trigger with multiple real life examples. The statement level triggers are triggers are most commonly used triggers which can be used in multiple event handling.
What you will find in this article?
- What is statement level trigger with examples?
- Where to use Statement level trigger with examples.
What is statement level trigger with examples?
The DML triggers are categorized in to two types : 1. Statement level triggers and other is row level trigger. In this section I would like to give the details about the statement level trigger with example. The Statement level trigger will be fired for every transaction not for every row. It will fire whenever the trigger event occurs on the specified table. It will not check how many rows are impacting with statement level trigger.
The default triggers are statement level triggers and we are not using for each row statement in statement level trigger.
Syntax :
Create or replace Trigger Trigger name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name
Begin
SQL statements;
END;
The above is syntax of creating the statement level trigger.
Real Life Example : You can also check examples in trigger’s article section.. In this section i would like to give you the step by step example + testing of statement level trigger.
Step 1 : Create table named Employee
create table Employee
( emp_id number,
ename varchar2(100)
);
Step 2 : Insert values in employee table
insert into Employee values (1,’Amit’);
insert into Employee values (2,’Pradnya’);
Step 3 : Write the Code for Trigger
create or replace trigger T_Statement_Level
before update —Before updating the table named Employee trigger needs to be fired
on Employee
begin
DBMS_OUTPUT.PUT_LINE(‘Trigger Fired Before Update’);
end;
Step 4 : Testing of Trigger
update Employee
set ename=’Rahul’;
The above statement will give output like,
Trigger fired Before Update
2 rows updated
Then try to fire query which has not satisfying condition,
update Employee
set ename=’Rahul’
where emp_id=5;
In above example emp_id=5 is not present in the Employee table. But trigger will be fired once and output will be,
Trigger fired Before Update
0 rows updated
Points to remember :
1.Staatement level triggers are default triggers which can be created once you create trigger without using for each row statement.
2.It is firing once for every triggering event.
3.It is also firing once even if no rows are affected.
Another Example Of Statement Level trigger :
Where to use Statement Trigger with Example?
The statement level triggers are the default triggers in oracle SQL. We can use statement level triggers when you do not require the row by row condition checking.
Use 1 : Security checks
The statement level triggers are used in security check before any data manipulation statement.
example :
I would like to give example of the account opening. If there is condition of opening account if and only if 500 rupees are there in Balance table. We require to write the trigger to check the balance before insert statement.
Use 2 : User profile check
If you want to check user profile before firing DML the statement level triggers have been useful. You can use control statements also in trigger body.
Real life Example : If you want to run the trigger in office time only and within that time user needs to have right to insert update and delete. We need to use conditional predicates here like – inserting, updating and deleting.
Create or replace trigger T_Time_check
before
insert or update or delete
on DEPARTMENTS
beginif to_number (to_char(sysdate,’hh24′) ) not between 10 and 18 then
if inserting then
raise_application_error(-20010, ‘Kindly insert data in office time -10 to 18 ‘);
elsif deleting then
raise_application_error(-20011, ‘Kindly delete data in office time -10 to 18’);
elsif updating then
raise_application_error(-20012, ‘Kindly update data in office time -10 to 18 ‘);
end if;
end if;end;
The above trigger will allow you to insert,update or delete the data in specified time window which is 10 to 18.
I hope you like this article on statement level triggers with multiple real examples. If you want more examples in detail kindly comment in comments section.