Triggers
It is a stored PL/SQL program unit associated with a specific database table
Oracle executes (fires) the trigger automatically whenever a given SQL operation affects the table
They are invoked implicitly
They are useful for customizing a database
They should be used only when necessary
Can automatically generate derived column values
Can prevent invalid transactions
Can enforce complex security authorizations
Can enforce referential integrity across nodes in a distributed database
Can enforce complex business rules
Can provide transparent event logging
Can provide sophisticated auditing
Can maintain synchronous table replicates
Can gather statistics on table access
Can derive column values automatically
Can restrict DML operations to regular business hours
Syntax --
CREATE [OR REPLACE] TRIGGER
BEFORE|AFTER
INSERT|DELETE|UPDATE OF ON
[FOR EACH ROW]
WHEN ()
Name in the ON clause identifies the database table associated with the trigger
The trigger event specifies the SQL DML statement (INSERT, DELETE or UPDATE) that affects the table
AFTER specifies that the trigger fires after the manipulation is done
BEFORE specifies that the trigger fires before the manipulation is done
By default, a trigger fires once per table
FOR EACH ROW specifies that the trigger fires once per row
For the trigger to fire, the Boolean expression in the WHEN clause must evaluate to TRUE
REPLACE can be added to the CREATE statement to drop and re-create the trigger automatically
CREATE TRIGGER flight_update
AFTER INSERT ON reservation
FOR EACH ROW
BEGIN
IF :new.class = ‘F’ THEN
statements;
ELSIF :new.class = ‘B’ THEN
statements;
END IF;
END;
Prefix :new is a correlation name that refers to the newly updated column value
Within a trigger, the :new and :old values of changing rows can be referenced
A single trigger can handle more than one operation
Use conditional predicates to identify the type of statement used to invoke the section of code
Examples of Triggers - Row Level
AFTER clause
1)
-- Main table
create table temp
as
select * from emp;
-- Table to transfer inserted record
create table instemp
as
select ename,sal
from emp
where 1=2;
Whenever a row is inserted in the temp table then that new row should be transferred in the instemp table
Create or replace trigger trig1
After INSERT on temp
For Each Row
Begin
Insert into InsTemp
Values(:new.ename, :new.sal);
Dbms_output.put_line('Record inserted');
End;
2 ) Table to transfer deleted record
create table deltemp
as
select ename,sal
from emp
where 1=2;
--Whenever a row is deleted from temp table then that row should be transferred --in Deltemp table
Create or replace trigger Trig2
After DELETE on Temp
For Each Row
Begin
Insert into Deltemp
Values(:old.ename, :old.sal);
Dbms_output.put_line('Record deleted………');
End;
3) Table to transfer the old record before updations
create table uptemp
as
select ename,sal
from emp
where 1=2;
Whenever a record is updated from the temp table then the old record shoul go in uptemp table
create or replace trigger trig3
After UPDATE on temp
For Each Row
Begin
Insert into Uptemp
Values(:old.ename, :old.sal);
End;
Referencing old and new values with some other identifier.
create or replace trigger on1
after insert on temp
referencing old as o new as n
for each row
begin
if :n.sal <= 4000 then
raise_application_error(-200012,'sss');
end if;
end;
Use Of Before Clause
1) When a new record is inserted then it should have sal >= 5000
Create or replace trigger trig7
Before INSERT on temp
For Each Row
Begin
If :new.sal < 5000 then
raise_application_error(-20009,'New record sal should be above 5000');
End If;
End;
2) --To prevent user from deleting records of sal >= 3500
Create or replace trigger trig4
Before DELETE on temp
For Each Row
Begin
if :old.sal >= 3500 Then
raise_application_error(-20005,'Cannot delete records of sal greater than or equal to 3500');
End if;
End;
3) If a salary is updated then it should be always greater than the old salary.
create or replace trigger check_sal
before update of sal
on temp
for each row
begin
if :new.sal < :old.sal then
raise_application_error(-20009,'Not possible');
end if;
end;
4) Whenever a new record is inserted then the cost value should be always greater than all the existing cost values in the table.
create table cost_chart
(year varchar2(20) unique,
cost number);
create or replace trigger check_cost
before insert on cost_chart
for each row
declare v_cost number;
begin
select max(cost) into v_cost from cost_chart;
if :new.cost < v_cost then
raise_application_error(-20010,'Cost cannot be less than the previous cost');
end if;
end;
Use of When Cluase
create or replace trigger useofwhen
after insert on temp
for each row
when (new.job = 'CLERK')
/*After any clerk record is inserted in the temp table, the date and time of entry will be inserted in the table recofclerk*/
begin
insert into recofclerk
values(sysdate, to_char(sysdate,'hh:mi:ss'));
dbms_output.put_line('All the tables are populated.');
end;
Demo of sequence of execution of row level and statement level triggers
create table trigtab(a number);
create or replace trigger tt1
before insert on trigtab
begin
dbms_output.put_line('Before statement level');
end;
create or replace trigger tt2
before insert on trigtab
for each row
begin
dbms_output.put_line('Before row level');
end;
create or replace trigger tt3
after insert on trigtab
for each row
begin
dbms_output.put_line('After row level');
end;
create or replace trigger tt4
after insert on trigtab
begin
dbms_output.put_line('After Statement level');
end;
Statement OR Table Level Triggers
1) -- To prevent a new record to be inserted in temp table
create or replace trigger trig6
Before INSERT on temp
Begin
raise_application_error(-20006,'Cannot insert a new record');
End;
2) -- To prevent any record to get deleted from temp table
create or replace trigger trig8
Before DELETE on temp
Begin
raise_application_error(-20011,'Cannot delete');
End;
3) -- No transaction should take place on SUNDAY
create or replace trigger trig9
Before INSERT or UPDATE or DELETE
on temp
Begin
If rtrim(to_char(Sysdate,'DAY') )= 'SUNDAY' Then
raise_application_error(-20015,'No transactions on Sunday');
End If;
End;
4) Whenever a record is deleted from emp table then the count of records remaining should be refreshed in the a log table count_emp
create table count_emp (no_of_records number);
create or replace trigger EmpCount
After Delete On Emp
Declare n number;
Begin
select count(*) into n
from emp;
Delete from count_emp;
Insert into count_emp values(n);
dbms_output.put_line('There are now ' || n || ' employees in theemp table');
end;
Combining DML events in one Trigger
--Combining all 3DML in one Begin block
--Any DML should be traced in the table KeepTrace
create table KeepTrace
(username varchar2(30),
Operation varchar2(30),
Date_of_Operation date,
Time_of_operation varchar2(40));
Create or replace trigger trig10
After INSERT or DELETE or UPDATE
On Temp
For each row
Begin
If Inserting Then
Insert into KeepTrace
Values(user, 'Record is Inserted', Sysdate, to_char(sysdate,'hh:mi:ss'));
ElsIf Deleting Then
Insert into KeepTrace
Values(user, 'Record is Deleted',Sysdate, to_char(sysdate,'hh:mi:ss'));
ElsIf Updating Then
Insert into KeepTrace
Values(user,'Record is Updated',Sysdate, to_char(sysdate,'hh:mi:ss'));
End If;
End;
Calling a procedure inside a trigger –
create table EmpSummary
(Total_Salary number);
Procedure Code –
create or replace procedure TotSal
is
vsal number;
Begin
delete from EmpSummary;
Select sum(sal) into vsal
from emp;
Insert into EmpSummary
Values(vsal);
End;
create or replace trigger EmpUpdates
After insert or update on emp
CALL TOTSAL
Mutating table error
When a row level trigger is based on a table then the trigger body cannot read data from the same table. Also DML on the same table is not possible.
create or replace trigger trig11
after delete on temp1
for each row
declare
x number;
begin
select sal into x from temp1
where ename = 'SMITH';
dbms_output.put_line(x);
end;
delete from temp1;
create or replace trigger trig11
after delete on temp1
for each row
begin
insert into temp1 (empno,ename) values(78,'pqr');
end;
delete from temp1 where ename = 'KING';
For a statement level trigger there will be no error.
create or replace trigger trig11
after delete on temp1
declare
x number;
begin
select sal into x from temp1
where ename = 'SMITH';
dbms_output.put_line(x);
end;
delete from temp1
where ename = 'MARTIN';
create or replace trigger trig11
after delete on temp1
begin
insert into temp1 (empno,ename) values(78,'pqr');
end;
/
delete from temp1 where ename = 'KING';
On Update Cascade
Similar to on delete cascade built in for primary and foreign key integrity.
create table st
(roll number,
name varchar2(40));
create table res
(roll number,
marks number);
insert into st values(1,'A');
insert into st values(2,'B');
insert into st values(3,'C');
insert into res values(1,90);
insert into res values(2,92);
insert into res values(3,94);
Commit;
create or replace trigger on_update_cascade
after update of roll on st
for each row
Begin
Update res
set res.roll = :new.roll
where res.roll = :old.roll;
End;
Instead Of Triggers
Provides a transparent way of modifying views that cannot be modified directly through INSERT, UPDATE or DELETE statements because underlying tables contain joins
It is called INSTEAD OF because Oracle fires the trigger instead of the triggering statement(s)
Users can be transparent to the trigger because they write normal DML statements against the view and the INSTEAD OF trigger is used to take care of the modifications
It can be placed on Object Views to insert, update or delete data in the underlying relational tables
CREATE TRIGGER emp_insert
INSTEAD OF INSERT ON emp_view
BEGIN
statements;
END;
1) Example on Instead of Update on View -
--Instead of triggers can be used only with views.
--Effective for joins which are based on equi join
-- To have an cascading effect of update on both the tables if columns are --matching
--Also to update uncommon columns through equi join views
--Step 1 Creating tables s and r;
create table s
(rollno number,
name varchar2(20));
create table r
(rollno number,
marks number);
--Step 2 Inserting records in s and r.
insert into s
values(1,'a');
insert into s
values(2,'b');
insert into r
values(1,90);
insert into r
values(2,87);
--Step 3 Creating an Equijoin View on s and r
Create or replace view SR
as
select s.rollno,s.name,r.marks
from s,r
where s.rollno =r.rollno;
--Step 4 Now creating the Instead of Trigger for update on the view SR
Create or replace trigger tig12
Instead Of UPDATE on SR
For Each Row
Begin
/* Updating roll numbers from both the tables s and r */
Update s
set rollno = :new.rollno
where rollno = :old.rollno;
Update r
set rollno = :new.rollno
where rollno = :old.rollno;
/* Updating name column of s table through view.*/
Update s
set name = :new.name
where name = :old.name;
/*Updating marks column of r table through view.*/
Update r
set marks = :new.marks
where marks = :old.marks;
End;
--
2) Example of Instead of Insert
Create or replace trigger tig14
Instead Of INSERT on SR
For Each Row
Begin
/*Inserting the new record into both the tables s and r.*/
Insert into s
values(:new.rollno, :new.name);
Insert into r
values(:new.rollno, :new.marks);
End;
3) Example of Instead of Delete
create or replace trigger trig15
Instead of DELETE on SR
For Each Row
Begin
/*Deleting the record from both the tables s and r.*/
Delete from s
where rollno = :old.rollno;
Delete from r
where rollno = :old.rollno;
End;
DDL EVENT TRIGGERS (Can be created by DBA)
1) To prevent Scott from dropping table TT
create or replace trigger prevent_drop
Before Drop on Scott.Schema
Begin
If ora_dict_obj_owner = 'SCOTT'
and
ora_dict_obj_name = 'TT'
and
ora_dict_obj_type = 'TABLE'
Then
raise_application_error(-20020,'Cannot drop table TT');
End If;
End;
2) --To keep a track of Scott new objects
--The new objects should get added in a table ScottObjects
--ScottObjects is owned by DBA
create table ScottObjects
(Object_name varchar2(30),
Date_of_Creation date);
create or replace trigger Put_New_Objs
After CREATE ON Scott.Schema
Begin
Insert into ScottObjects
Values(ora_dict_obj_name,Sysdate);
End;
3) --To keep a track of Scott's dropped objects
--The dropped objects should get added in a table ScottDrop
create table ScottDrop
(Object_name varchar2(30),
Date_of_Creation date);
create or replace trigger Put_Drop_Objs
After DROP ON Scott.Schema
Begin
Insert into Scottdrop
Values(ora_dict_obj_name,Sysdate);
End;
4) --Preventing Scott to drop column h2 of table hh
--Step 1 Login as scott
--create table hh
--(h1 number,
--h2 number,
--h3 number);
-- Step 2 Log in as sysdba
Create or Replace Trigger Prev_Drop_Col
Before ALTER on Scott.Schema
Begin
If ora_dict_obj_name = 'HH'
and
ora_is_drop_column('H2')
Then
raise_application_error(-20067,'Cannot drop column h2');
End If;
End;
5) --To prevent Scott from modifying the data type of column h3 of hh table
--Log in as DBA
create or replace trigger Prev_Modify
Before ALTER on Scott.Schema
Begin
If ora_dict_obj_name = 'HH'
and
ora_is_alter_column('H3')
Then
raise_application_error(-20045,'Cannot modify column H3');
End If;
End;
Discussion about this article
| Author | Body |
Rahul
8/29/2009 10:17 PM
|
Please provide feedback about this article here.
To participate in this discussion Sign up for free membership of 24x7code.
To Signup click on Login , Use create user link & the follow the instructions.
Thank you
|