Tuesday, May 16, 2017
TRIGGERS IN PL/SQL
Triggers
Trigger is also same as stored procedure & also it will automatically invoked whenever DML
Operation performed against table or view.
There are two types of triggers supported by PL/SQL.
1) Statement Level Trigger
2) Row Level Trigger
In Statement Level Trigger, Trigger body is executed only once for DML Statements.
In Row Level Trigger, Trigger body is executed for each and every DML Statements.
Syntax : create { or replace } trigger trigger_name
before / after trigger event
insert / update / delete on table_name
{ for each row }
{ where condition }
{ declare }
variable declarations, cursors
begin
-----
end;
Execution order in Triggers
1 ) Before Statement Level
2 ) Before Row Level
3 ) After Row Level
4 ) After Statement Level
1) Statement Level Trigger
In Statement Level Trigger, Trigger body is executed only once for each DML
Statement. Thats why generally statement level triggers used to define type based
H- No: 100/B, Ground Floor, Near S.R.Nagar Community Hall, S.R.Nagar, Hyderabad – 500 038, Web : www.k-onlines.com
Contact Ph No => Land Line : 040 – 42221320 / 65530333, Mobile : 91-8143900333
================================================================================================
condition and also used to implement auditing reports. These triggers does not contain
new, old qualifiers.
Q) Write a pl/sql statement level trigger on emp table not to perform DML Operations in
saturday and sunday?
Program) Create or replace trigger tr1 before insert or update or delete on tt
begin
if to_char(sysdate,'DY') in ('SAT','SUN')
then
raise_application_error(-20123,'we can not perform DMLs on sat and sunday');
end if;
end;
Q) Write a pl/sql statement level trigger on emp table not to perform DML Operation on last
day of the month?
Program ) create or replace trigger tt2 before insert or update or delete on tt
begin
if sysdate=last_day(sysdate) then
raise_application_error (-20111,'we can not perform dml operations on lastday ');
end if;
end;
Trigger Event ( or ) Trigger Predicate Clauses
If you want to define multiple conditions on multiple tables then all database systems
uses trigger events.
These are inserting, updating, deleting clauses
These clauses are used in either row level or statement level triggers.
Syntax : if inserting then
statements;
elsif updating then
statements;
elsif deleting then
statements;
end if;
Q ) Write a pl/sql statement level trigger on emp table not to perform any dml operation in any
days using triggering event?
Program ) create or replace trigger tr3 before insert or update or delete on tt
begin
if inserting then
raise_application_error (-20121,'we can not perform inserting operation');
elsif updating then
raise_application_error (-20122,'we can not perfrom update operation');
elsif deleting then
H- No: 100/B, Ground Floor, Near S.R.Nagar Community Hall, S.R.Nagar, Hyderabad – 500 038, Web : www.k-onlines.com
Contact Ph No => Land Line : 040 – 42221320 / 65530333, Mobile : 91-8143900333
================================================================================================
raise_application_error (-20123,'we can not perform deleting operation');
end if;
end;
Ex : Create table test ( msg varchar2(100));
create or replace trigger tr4 after insert or update or delete on tt
declare
a varchar2(50);
begin
if inserting then
a := 'rows inserted';
elsif updating then
a := 'rows updated';
elsif deleting then
a := 'rows deleted';
end if;
insert into testt values (a);
end;
2) Row Level Trigger
In Row Level Trigger, Trigger body is executed for each row for DML Statement, Thats
why we are using for each row clause in trigger specification and also data internally
stored in 2 rollback segment qualifiers are OLD & NEW
These qualifiers are used in either trigger specification or in trigger body. when we are
using these modifiers in trigger body we must use colon prefix in the qualifiers.
Syntax - :old.column_name ( or ) :new.column_name.
When we are using these qualifiers in when clause we are not allow to use colon infront
of the qualifiers.
Qualifier Insert Update Delete
:new YES YES NO
:old NO YES YES
In Before Triggers, Trigger body is executed before DML Statements are effected into
database.
In After Triggers, Trigger body is executed after DML Statements are effected into
database.
Generally if we want to restrict invalid data entry always we are using before triggers,
where as if we are performing operation on the one table those operations are effected in
another table then we are using after trigger.
Whenever we are inserting values into new qualifiers we must use before trigger
otherwise oracle server returns an error.
Q ) Write a PL/SQL Row Level Trigger on emp table whenever user inserting data into a emp
table sal should be more than 5000?
H- No: 100/B, Ground Floor, Near S.R.Nagar Community Hall, S.R.Nagar, Hyderabad – 500 038, Web : www.k-onlines.com
Contact Ph No => Land Line : 040 – 42221320 / 65530333, Mobile : 91-8143900333
================================================================================================
Program ) Create or replace trigger t90 before insert on tb
for each row
begin
if :new.sal<5000 then
raise_application_error (-20123,'salary should be more than 5000');
end if;
end;
Q ) Write a PL/SQL Row Level Trigger on emp, dept tables while implement on delete cascade
concept without using on delete cascade clause?
Program ) Create or replace trigger t1
after delete on dept
for each row
begin
delete from emp where deptno=:old.deptno;
end;
Q ) Write a PL/SQL Row Level Trigger on dept table whenever updating deptno's in dept table
automatically those deptno's modified into emp table?
Program ) Create or replace trigger t19
after update on dept
for each row
begin
update emp set deptno=:new.deptno where deptno=:old.deptno;
end;
Q ) Write a PL/SQL Row Level Trigger whenever user inserting data into ename column after
inserting data must be converted into uppercase ?
Program ) create or replace trigger t21
before insert on emp
for each row
begin
:new.ename:=upper(:new.ename);
end;
Q ) Write a PL/SQL Row Level Trigger on emp table by using below conditions?
1 ) whenever user inserting data those values stored in another table
2 ) whenever user updating data those values stored in another table
3 ) whenever user deleting data those values stored in another table
Program ) First we create 3 tables which are having the same structure of emp table.
Create or replace trigger te1
after insert or update or delete on t01
for each row
begin
H- No: 100/B, Ground Floor, Near S.R.Nagar Community Hall, S.R.Nagar, Hyderabad – 500 038, Web : www.k-onlines.com
Contact Ph No => Land Line : 040 – 42221320 / 65530333, Mobile : 91-8143900333
================================================================================================
if inserting then
insert into e1(empno,ename) values (:new.empno,:new.ename);
elsif updating then
insert into e2(empno,ename) values (:old.empno,:old.ename);
elsif deleting then
insert into e3(empno,ename) values (:old.empno,:old.ename);
end if;
end;
Q ) Write a PL/SQL Trigger on emp table whenever user deleting records from emp table
automatically display remaining number of existing record number in bottom of the delete
statment?
Program ) Create or replace trigger tp1 after delete on emp
declare
a number(10);
begin
select count(*) into a from emp;
dbms_output.put_line('remaining records are: '||a);
end;
Mutating Trigger
Ex : Create or replace trigger tp1 after delete on emp
for each row
declare
a number(10);
begin
select count(*) into a from emp;
dbms_output.put_line('remaining records are: '||a);
end;
Into a Row Level Trigger based on a table trigger body can not read data from same
table and also we can not perform DML Operations on same table.
If we are trying to this oracle server returns an error is table is mutating.
This Error is called Mutating Error
This Trigger is called Mutating Trigger
This Table is called Mutating Table
Mutating Errors are not accured in Statement Level Trigger Because through these
Statement Level Trigger when we are performing DML Operations automatically data
Committed into database.
Where as in Row Level Trigger when we are performing transaction data is not
committed and also again we are reading this data from the same table then only
mutating error is accured.
To avoid this mutating error we are using autonomous transaction in triggers.
H- No: 100/B, Ground Floor, Near S.R.Nagar Community Hall, S.R.Nagar, Hyderabad – 500 038, Web : www.k-onlines.com
Contact Ph No => Land Line : 040 – 42221320 / 65530333, Mobile : 91-8143900333
================================================================================================
Ex Create or replace trigger tp1 after delete on t01
for each row
declare
pragma autonomous_transaction;
a number(10);
begin
select count(*) into a from t01;
dbms_output.put_line('remaining records are: '||a);
commit;
end;
DDL Triggers
We can also create triggers on schema level, database level. These types of triggers are
called DDL Triggers or System Triggers.
These types of triggers are created by database administrator.
Syntax : Create or replace trigger trigger_name
Before / After
Create / Alter / Drop / Truncate / Rename
On Username.Schema
Q ) Write a PL/SQL Trigger on scott schema not to drop emp table?
Program ) Create or replace trigger td
before drop on apps.schema
begin
if ora_dict_obj_name = 'T100' and
ora_dict_obj_type = 'TABLE' then
raise_application_error(-20121,'we can not drop this table');
end if;
end;
Create or Replace trigger Trigger_test
before insert or update or delete on Siddharth
begin
if inserting then
Raise_Application_Error (-2001, 'Then you cannot make insertion on this table');-- User defined error with own Error number and message
elsif updating then
Raise_Application_Error (-2002, 'Then you cannot make updates on this table');
elsif deleting then
Raise_Application_Error (-2002, 'Then you cannot make updates on this table');
end if;
end;
/
CREATE OR REPLACE TRIGGER LOD_PUBPRTPMO_LOD_ID_TRIG BEFORE INSERT OR UPDATE ON LOD_PUBPRTPMO
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.LOD_ID IS NULL THEN
SELECT LOD_PUBPRTPMO_LOD_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
-- If this is the first time this table have been inserted into (sequence == 1)
IF v_newVal = 1 THEN
--get the max indentity value from the table
SELECT NVL(max(LOD_ID),0) INTO v_newVal FROM LOD_PUBPRTPMO;
v_newVal := v_newVal + 1;
--set the sequence to that value
LOOP
EXIT WHEN v_incval>=v_newVal;
SELECT LOD_PUBPRTPMO_LOD_ID_SEQ.nextval INTO v_incval FROM dual;
END LOOP;
END IF;
-- assign the value from the sequence to emulate the identity column
:new.LOD_ID := v_newVal;
END IF;
END;
/
CREATE OR REPLACE TRIGGER LOD_PUBLISHER_ID_TRIG BEFORE INSERT OR UPDATE ON LOD_PUBLISHER
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.LOD_ID IS NULL THEN
SELECT LOD_PUBLISHER_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
:new.LOD_ID := v_newVal;
END IF;
END;
/
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment