Database Triggers:
- A set of pl/sql statements stored permenantly in database and "automatically" activated when ever an event raising statement ( DML ) is executed.
- They are stored in "User_Triggers" system table.
- They r used to impose Business rules / user defined restrictions on Table.
- They r also activated when Tables are manipulated by other users or by other application s/w tools.
- They provide high security on tables.
Trigger Parts: 4 Parts
1. Triggering Event : Indicates when to activate the trigger .
- A set of pl/sql statements stored permenantly in database and "automatically" activated when ever an event raising statement ( DML ) is executed.
- They are stored in "User_Triggers" system table.
- They r used to impose Business rules / user defined restrictions on Table.
- They r also activated when Tables are manipulated by other users or by other application s/w tools.
- They provide high security on tables.
Trigger Parts: 4 Parts
1. Triggering Event : Indicates when to activate the trigger .
Before -- insert / update / delete
After -- insert / update / delete
2. Trigger Types: 2 Types
i) Row Triggers : Activates the trigger for every row manipulated by DML statement.
ii) Statement Triggers: Activates the trigger for only once for 1 DML statement ( Default Type )
3. Trigger Restriction:
Used to stop the activation of trigger based on condition. If condition is True trigger is active.
4. Trigger Body: A set of pl/sql statements
Syntax:
Create or replace Trigger < Trigger name >
before/after insert or update or delete
[ of <columns> ] on < Table name >
[ for each row
when (< condition >) * if condition is True trigger is executed
declare
< variable declaration >; ]
begin
< exec stmts >;
[ exception
<exec stmts >; ]
end;---------------------------------------------------------------
After -- insert / update / delete
2. Trigger Types: 2 Types
i) Row Triggers : Activates the trigger for every row manipulated by DML statement.
ii) Statement Triggers: Activates the trigger for only once for 1 DML statement ( Default Type )
3. Trigger Restriction:
Used to stop the activation of trigger based on condition. If condition is True trigger is active.
4. Trigger Body: A set of pl/sql statements
Syntax:
Create or replace Trigger < Trigger name >
before/after insert or update or delete
[ of <columns> ] on < Table name >
[ for each row
when (< condition >) * if condition is True trigger is executed
declare
< variable declaration >; ]
begin
< exec stmts >;
[ exception
<exec stmts >; ]
end;---------------------------------------------------------------
* Trigger converts student name to upper case automatically. create or replace Trigger up_con
before insert on stu_info
for each row
begin
:new.sname = upper(:new.sname);
end;
insert into stu_info values(101,'ram','ORACLE',2500);
>select * from stu_info;
101 RAM ORACLE 2500
---------------------------------------------------------------
before insert on stu_info
for each row
begin
:new.sname = upper(:new.sname);
end;
insert into stu_info values(101,'ram','ORACLE',2500);
>select * from stu_info;
101 RAM ORACLE 2500
---------------------------------------------------------------
New & Old (Pseudo Columns):-------------------------------------
Used to retrieve data from DML stmt temporary buffer. * valid with "ROW" triggers only.
INSERT UPDATE DELETE
NEW valid valid invalid
OLD invalid valid valid
* They r refered with : (colon) to indicate as Bind variables.
They r accessed from SQL buffer to Pl/sql block.
---------------------------------------------------------------
Triggering Events: Before insert Row, Before update Row,
Before delete Row
Before insert Stmt, Before update Stmt,
Before delete Stmt
After insert Row, After update Row,
After delete Row
After insert Stmt, After update Stmt,
After delete Stmt
12 Events per Table are allowed.
---------------------------------------------------------------
* Modifying existing trigger create or replace Trigger up_con
before insert on stu_info
for each row
begin
if :new.course = 'oracle' and :new.fee_paid = 2500 then
:new.sname := upper(:new.sname); * stu_info
else ----------
:new.sname := lower(:new.sname); roll
end if; sname
end; course
fee_paid
insert into stu_info values(111,'sekhar','oracle',2500);
insert into stu_info values(112,'SRINATH','oracle',1000);
select * from stu_info;
111 SEKHAR oracle 2500
112 srinath oracle 1000
---------------------------------------------------------------
* Trigger Checks for valid increment on employ salary :>create or replace trigger chk_incr
before update of sal on emp
for each row
begin
if :new.sal <= :old.sal then
raise_application_error(-20300,' Increment must be more than existing salary ');
end if;
end;
>update emp set sal = sal - 3000
where empno = 7900;
>update emp set sal = sal - sal *.35;
---------------------------------------------------------------
Note: Column Specification [ of sal ] is supported with UPDATE event only. Not valid with Insert and Delete Operations ( Row Level Operations ).
* Raise_application_error(error no,error message); Built_in sub program stops the DML stmt execution
and displays the error message.
Error No Range : -20001 to -20999
Oracle Reserved Error Nos: -20000 to +20000
---------------------------------------------------------------
Used to retrieve data from DML stmt temporary buffer. * valid with "ROW" triggers only.
INSERT UPDATE DELETE
NEW valid valid invalid
OLD invalid valid valid
* They r refered with : (colon) to indicate as Bind variables.
They r accessed from SQL buffer to Pl/sql block.
---------------------------------------------------------------
Triggering Events: Before insert Row, Before update Row,
Before delete Row
Before insert Stmt, Before update Stmt,
Before delete Stmt
After insert Row, After update Row,
After delete Row
After insert Stmt, After update Stmt,
After delete Stmt
12 Events per Table are allowed.
---------------------------------------------------------------
* Modifying existing trigger create or replace Trigger up_con
before insert on stu_info
for each row
begin
if :new.course = 'oracle' and :new.fee_paid = 2500 then
:new.sname := upper(:new.sname); * stu_info
else ----------
:new.sname := lower(:new.sname); roll
end if; sname
end; course
fee_paid
insert into stu_info values(111,'sekhar','oracle',2500);
insert into stu_info values(112,'SRINATH','oracle',1000);
select * from stu_info;
111 SEKHAR oracle 2500
112 srinath oracle 1000
---------------------------------------------------------------
* Trigger Checks for valid increment on employ salary :>create or replace trigger chk_incr
before update of sal on emp
for each row
begin
if :new.sal <= :old.sal then
raise_application_error(-20300,' Increment must be more than existing salary ');
end if;
end;
>update emp set sal = sal - 3000
where empno = 7900;
>update emp set sal = sal - sal *.35;
---------------------------------------------------------------
Note: Column Specification [ of sal ] is supported with UPDATE event only. Not valid with Insert and Delete Operations ( Row Level Operations ).
* Raise_application_error(error no,error message); Built_in sub program stops the DML stmt execution
and displays the error message.
Error No Range : -20001 to -20999
Oracle Reserved Error Nos: -20000 to +20000
---------------------------------------------------------------
* Trigger checks for null value in dept name. >create or replace trigger chk_null
before insert or update of dname on dept
for each row
begin
if :new.dname is null then
raise_application_error(-20301,
' Department name cannot be blank ');
end if;
end;
>insert into dept values(88,null,'Hyd');
>update dept set dname = null
where deptno = 30;
---------------------------------------------------------------
Note: Constraint Vs Trigger Constraints will check for existing rows also
Triggers will not check for existing rows
Constraints provide standard error messages
Triggers provide user friendly error messages
Constraints provides pre-defined rules on table columns.
Triggers provides user-defined rules on table columns
* If constraint & Trigger are defined on table at a time only Trigger will be activated.---------------------------------------------------------------
> Create table job_pays
( job varchar2(20), losal number(10),
hisal number(10));
insert into job_pays values
('CLERK' , 3000 , 10000);
insert into job_pays values
('SALESMAN' , 5000 , 15000);
insert into job_pays values
('MANAGER' , 8000 , 20000);
insert into job_pays values
('ANALYST' , 10000 , 30000);
insert into job_pays values
('EXECUTIVE' , 10000 , 25000);
* Trigger Checks for valid salary according to the Job. create or replace trigger chk_sal
before insert on emp for each row
-- if condition is True trigger will be executed
when (new.job <> 'PRESIDENT')
declare
minpay number(10);
maxpay number(10);
begin
select losal,hisal into minpay,maxpay from job_pays where job = :new.job;
if :new.sal < minpay or :new.sal > maxpay then
raise_application_error(-20200,' Salary must be between '||minpay||' and '||maxpay||' for Job '||:new.job);
end if;
exception
when no_data_found then
raise_application_error(-20201,' No such job exists '||:new.job);
end;
INSERT INTO EMP(EMPNO,ENAME,JOB,SAL)
VALUES(&1,'&2','&3',&4);
---------------------------------------------------------------
before insert or update of dname on dept
for each row
begin
if :new.dname is null then
raise_application_error(-20301,
' Department name cannot be blank ');
end if;
end;
>insert into dept values(88,null,'Hyd');
>update dept set dname = null
where deptno = 30;
---------------------------------------------------------------
Note: Constraint Vs Trigger Constraints will check for existing rows also
Triggers will not check for existing rows
Constraints provide standard error messages
Triggers provide user friendly error messages
Constraints provides pre-defined rules on table columns.
Triggers provides user-defined rules on table columns
* If constraint & Trigger are defined on table at a time only Trigger will be activated.---------------------------------------------------------------
> Create table job_pays
( job varchar2(20), losal number(10),
hisal number(10));
insert into job_pays values
('CLERK' , 3000 , 10000);
insert into job_pays values
('SALESMAN' , 5000 , 15000);
insert into job_pays values
('MANAGER' , 8000 , 20000);
insert into job_pays values
('ANALYST' , 10000 , 30000);
insert into job_pays values
('EXECUTIVE' , 10000 , 25000);
* Trigger Checks for valid salary according to the Job. create or replace trigger chk_sal
before insert on emp for each row
-- if condition is True trigger will be executed
when (new.job <> 'PRESIDENT')
declare
minpay number(10);
maxpay number(10);
begin
select losal,hisal into minpay,maxpay from job_pays where job = :new.job;
if :new.sal < minpay or :new.sal > maxpay then
raise_application_error(-20200,' Salary must be between '||minpay||' and '||maxpay||' for Job '||:new.job);
end if;
exception
when no_data_found then
raise_application_error(-20201,' No such job exists '||:new.job);
end;
INSERT INTO EMP(EMPNO,ENAME,JOB,SAL)
VALUES(&1,'&2','&3',&4);
---------------------------------------------------------------
* Trigger adds the increment amount automatically to Employ salary. ( Using IMPLICIT Cursor )
create or replace trigger add_incr
before insert on incr for each row
begin
update emp set sal = sal + :new.amt
where empno = :new.empno;
-- Using implicit cursor
if sql%notfound then
raise_application_error(-20111,' No such employ exists with employ code '||:new.empno);
end if;
end;
create table incr(empno number(4), amt number(10));
select empno,ename,sal from emp
where empno = 7900;
insert into incr values(7900,4000);
select empno,ename,sal from emp
where empno = 7900;
insert into incr values(799,2000);
---------------------------------------------------------------
create or replace trigger add_incr
before insert on incr for each row
begin
update emp set sal = sal + :new.amt
where empno = :new.empno;
-- Using implicit cursor
if sql%notfound then
raise_application_error(-20111,' No such employ exists with employ code '||:new.empno);
end if;
end;
create table incr(empno number(4), amt number(10));
select empno,ename,sal from emp
where empno = 7900;
insert into incr values(7900,4000);
select empno,ename,sal from emp
where empno = 7900;
insert into incr values(799,2000);
---------------------------------------------------------------
Student Fee_instalments
--------- --------------------
roll - 101 roll prev_fee curr_fee DOP
name - RAM 101 0 1000 ---
course - Oracle9i 101 1000 2000 ---
fee - 5000 101 3000 2000 ---
* Trigger adds the fee instalment amount automatically into fee_instalments table .
create or replace trigger add_fee
before insert or update on student
for each row
begin
if inserting then
insert into fee_instalments values
(:new.roll, 0, :new.fee, sysdate);
elsif updating then
insert into fee_instalments values
(:old.roll, :old.fee, :new.fee - :old.fee, sysdate);
end if;
end;
insert into student values(101,'RAM','oracle9i',1000);
update student set fee = fee + 2000
where roll = 101;
update student set fee = fee + 2000
where roll = 101;
select * from fee_instalments;
* Keywords: inserting, updating, deleting
-- Represents insert,update and delete operations
-- Valid in Triggers only
---------------------------------------------------------------
Transaction -- Table
--------------
Accno tran_type amt
101 D 5000
102 D 7500
103 D 1000
104 W 1000
105 W 9000
* Statement Level Trigger:
* Triggers Locks the Transaction table for update and delete operations. ( Only insert allowed )
create or replace trigger lock_trans
before update or delete on transaction
begin
raise_application_error(-20555, ' Invalid operation on Transaction Table ');
end;
delete from transaction; update transaction set amt = amt + 1000;
--------------------------------------------------------------- Disabling Triggers:
>Alter table transaction disable all triggers;
>Alter table bankmaster disable all triggers;
Enabling Triggers: >Alter table transaction enable all triggers;
>Alter table bankmaster enable all triggers;
Removing Triggers: Drop trigger < Trigger name >;
>Drop trigger CHK_SAL;
Checking for Existing Triggers: >desc User_triggers
>select trigger_name , triggering_event from
user_triggers where table_name = 'EMP';
>select * from user_triggers
where trigger_name = 'CHK_SAL';
>select text from USER_SOURCE
where name = 'CHK_SAL';
> desc user_source
--------- --------------------
roll - 101 roll prev_fee curr_fee DOP
name - RAM 101 0 1000 ---
course - Oracle9i 101 1000 2000 ---
fee - 5000 101 3000 2000 ---
* Trigger adds the fee instalment amount automatically into fee_instalments table .
create or replace trigger add_fee
before insert or update on student
for each row
begin
if inserting then
insert into fee_instalments values
(:new.roll, 0, :new.fee, sysdate);
elsif updating then
insert into fee_instalments values
(:old.roll, :old.fee, :new.fee - :old.fee, sysdate);
end if;
end;
insert into student values(101,'RAM','oracle9i',1000);
update student set fee = fee + 2000
where roll = 101;
update student set fee = fee + 2000
where roll = 101;
select * from fee_instalments;
* Keywords: inserting, updating, deleting
-- Represents insert,update and delete operations
-- Valid in Triggers only
---------------------------------------------------------------
Transaction -- Table
--------------
Accno tran_type amt
101 D 5000
102 D 7500
103 D 1000
104 W 1000
105 W 9000
* Statement Level Trigger:
* Triggers Locks the Transaction table for update and delete operations. ( Only insert allowed )
create or replace trigger lock_trans
before update or delete on transaction
begin
raise_application_error(-20555, ' Invalid operation on Transaction Table ');
end;
delete from transaction; update transaction set amt = amt + 1000;
--------------------------------------------------------------- Disabling Triggers:
>Alter table transaction disable all triggers;
>Alter table bankmaster disable all triggers;
Enabling Triggers: >Alter table transaction enable all triggers;
>Alter table bankmaster enable all triggers;
Removing Triggers: Drop trigger < Trigger name >;
>Drop trigger CHK_SAL;
Checking for Existing Triggers: >desc User_triggers
>select trigger_name , triggering_event from
user_triggers where table_name = 'EMP';
>select * from user_triggers
where trigger_name = 'CHK_SAL';
>select text from USER_SOURCE
where name = 'CHK_SAL';
> desc user_source
* User_source -- system table holds the details of Triggers .---------------------------------------------------------------
* Statement Level Trigger:
* Trigger Locks the table for DML operations to provide security:
* Trigger checks for i> Valid Timings ii> Weekends
iii> Public Holidays
create or replace trigger security_chk
before insert or update or delete on Bankmaster
declare
a number;
begin
-- check for valid Timings
if to_char(sysdate,'hh24') not in (10,11,12,13,14,15,16,17) then
raise_application_error(-20111,' No operations allowed - Invalid Timings ');
end if;
-- check for weekends
if to_char(sysdate,'dy') in ('sat','sun') then
raise_application_error(-20112,' No operations allowed - Weekends ');
end if;
-- check for Public Holidays
select count(*) into a from holiday
where to_date(hdate) = to_date(sysdate);
if a > 0 then
raise_application_error(-20113,' No operations allowed - Public Holiday ');
end if;
end;
Holiday
--------
hdate --- '15-aug-10'
description --- 'Independence Day'
---------------------------------------------------------------
* Trigger updates Transactions automatically to Master table.
Bankmaster Transaction
------------- --------------
accno accno
name tran_type
acc_type tran_date
curr_bal amt
insert into bankmaster values(101,'RAM','S',20000);
create or replace trigger add_deposit
After insert on transaction for each row
begin
if :new.tran_type = 'D' then
update bankmaster set curr_bal = curr_bal + :new.amt where accno = :new.accno;
end if;
end;
insert into transaction values(101,'D',sysdate,10000);
insert into transaction values(101,'D',sysdate,30000);
select * from bankmaster;
101 RAM S 60000
---------------------------------------------------------------
Instead of Triggers: [ 8i ]
- Triggers supported only on "views"
* - Used to perform DML operations on Join views
* Instead of --- insert, update, delete
Ex: 1
>create view v1 as select * from emp;
> create trigger t1 instead of delete on v1
for each row
begin
dbms_output.put_line(' Record Removed ');
end;
> delete from v1 where empno = 7900;
> select * from v1 where empno = 7900;
create trigger t2 instead of delete on v1
for each row
begin
insert into del_tab values(:old.empno,:old.ename,sysdate,user);
end;
---------------------------------------------------------------
* Statement Level Trigger:
* Trigger Locks the table for DML operations to provide security:
* Trigger checks for i> Valid Timings ii> Weekends
iii> Public Holidays
create or replace trigger security_chk
before insert or update or delete on Bankmaster
declare
a number;
begin
-- check for valid Timings
if to_char(sysdate,'hh24') not in (10,11,12,13,14,15,16,17) then
raise_application_error(-20111,' No operations allowed - Invalid Timings ');
end if;
-- check for weekends
if to_char(sysdate,'dy') in ('sat','sun') then
raise_application_error(-20112,' No operations allowed - Weekends ');
end if;
-- check for Public Holidays
select count(*) into a from holiday
where to_date(hdate) = to_date(sysdate);
if a > 0 then
raise_application_error(-20113,' No operations allowed - Public Holiday ');
end if;
end;
Holiday
--------
hdate --- '15-aug-10'
description --- 'Independence Day'
---------------------------------------------------------------
* Trigger updates Transactions automatically to Master table.
Bankmaster Transaction
------------- --------------
accno accno
name tran_type
acc_type tran_date
curr_bal amt
insert into bankmaster values(101,'RAM','S',20000);
create or replace trigger add_deposit
After insert on transaction for each row
begin
if :new.tran_type = 'D' then
update bankmaster set curr_bal = curr_bal + :new.amt where accno = :new.accno;
end if;
end;
insert into transaction values(101,'D',sysdate,10000);
insert into transaction values(101,'D',sysdate,30000);
select * from bankmaster;
101 RAM S 60000
---------------------------------------------------------------
Instead of Triggers: [ 8i ]
- Triggers supported only on "views"
* - Used to perform DML operations on Join views
* Instead of --- insert, update, delete
Ex: 1
>create view v1 as select * from emp;
> create trigger t1 instead of delete on v1
for each row
begin
dbms_output.put_line(' Record Removed ');
end;
> delete from v1 where empno = 7900;
> select * from v1 where empno = 7900;
create trigger t2 instead of delete on v1
for each row
begin
insert into del_tab values(:old.empno,:old.ename,sysdate,user);
end;
---------------------------------------------------------------
** Manipulating Join views thru Instead of Triggers Ex: 2
>create view edept as select empno, ename, sal, job, emp.deptno, dept.deptno dno, dname, loc from emp, dept
where emp.deptno = dept.deptno;
> insert into edept values(4411,'VIJAY',20000,'MANAGER',
99,99,'TECHNICAL','HITECH'); --- error
>create trigger t2 instead of insert on edept
for each row
begin
insert into dept values(:new.dno,:new.dname,:new.loc);
insert into emp(empno,ename,sal,job,deptno) values (:new.empno,:new.ename,:new.sal, :new.job, :new.deptno);
end;
/
>insert into edept values (......); --- repeat step 2
>select * from edept;
---------------------------------------------------------------
Ex : 3
>delete from edept where deptno = 99;
( Removes data from emp table only - key preserved table )
* Trigger Removes records from 2 tables create or replace trigger trig11
instead of delete on edept
for each row
begin
delete from emp where deptno = :old.deptno;
delete from dept where deptno = :old.deptno;
end;
delete from edept where deptno = 99; ( Removes data from emp ,dept table also )
---------------------------------------------------------------
>create view edept as select empno, ename, sal, job, emp.deptno, dept.deptno dno, dname, loc from emp, dept
where emp.deptno = dept.deptno;
> insert into edept values(4411,'VIJAY',20000,'MANAGER',
99,99,'TECHNICAL','HITECH'); --- error
>create trigger t2 instead of insert on edept
for each row
begin
insert into dept values(:new.dno,:new.dname,:new.loc);
insert into emp(empno,ename,sal,job,deptno) values (:new.empno,:new.ename,:new.sal, :new.job, :new.deptno);
end;
/
>insert into edept values (......); --- repeat step 2
>select * from edept;
---------------------------------------------------------------
Ex : 3
>delete from edept where deptno = 99;
( Removes data from emp table only - key preserved table )
* Trigger Removes records from 2 tables create or replace trigger trig11
instead of delete on edept
for each row
begin
delete from emp where deptno = :old.deptno;
delete from dept where deptno = :old.deptno;
end;
delete from edept where deptno = 99; ( Removes data from emp ,dept table also )
---------------------------------------------------------------
Invalid Triggers : 1. >create table temp1(c1 number(3),
c2 varchar2(10));
2. >create trigger trig1
before insert on temp1 for each row
begin
insert into temp1 values(102,'SIVA');
end;
/
3. >insert into temp1 values(101,'RAM');
Error: It leads to an infinite loop
---------------------------------------------------------------
c2 varchar2(10));
2. >create trigger trig1
before insert on temp1 for each row
begin
insert into temp1 values(102,'SIVA');
end;
/
3. >insert into temp1 values(101,'RAM');
Error: It leads to an infinite loop
---------------------------------------------------------------
>create trigger trig2
before insert on temp1 for each row
begin
update temp1 set c2 = 'HARI';
end;
>insert into temp1 values (102,'RAVI');
It will not perform update in Trigger body becoz 2 DML operations cannot be performed on table at once. It leads to a " Trigger Mutuation " error while working in other s/w tools.
---------------------------------------------------------------
before insert on temp1 for each row
begin
update temp1 set c2 = 'HARI';
end;
>insert into temp1 values (102,'RAVI');
It will not perform update in Trigger body becoz 2 DML operations cannot be performed on table at once. It leads to a " Trigger Mutuation " error while working in other s/w tools.
---------------------------------------------------------------
Note:
** TCL commands are not allowed in Triggers.
Becoz Trigger will be executed while performing DML stmt but TCL has to be given after DML stmt execution.
---------------------------------------------------------------
** TCL commands are not allowed in Triggers.
Becoz Trigger will be executed while performing DML stmt but TCL has to be given after DML stmt execution.
---------------------------------------------------------------
Advantage of Database Triggers: - Automatic execution of code based on event .
- Used to impose user defined restrictions on Tables .
- Provides Security on Tables while Manipulating data from any where .
---------------------------------------------------------------
- Used to impose user defined restrictions on Tables .
- Provides Security on Tables while Manipulating data from any where .
---------------------------------------------------------------
No comments:
Post a Comment