trigger sample code Ex#10
=============== ========
INSTEAD OF TRIGGER
----------------------------------------
[CODE=ORACLE]
create or replace trigger mytrig instead of delete or insert or update on eview
declare
a number(2);
begin
if inserting then
select count(deptno) into a from dept where deptno= :new.deptno;
if a=0 then
insert into dept values(:new.dep tno,:new.dname, :new.loc);
insert into emp ( empno,ename,sal ,deptno) values(:new.emp no,:new.ename,: new.sal,:new.de ptno);
else
insert into emp ( empno,ename,sal ,deptno) values(:new.emp no,:new.ename,: new.sal,:new.de ptno);
end if;
elsif deleting then
delete from emp where deptno= :new.deptno;
elsif updating then
if :new.deptno is not null then
raise_applicati on_error(-20003,'cannot update the deptno,dname,lo c columns OR cannot use deptno for updating');
else
update emp set empno=:new.empn o, ename= :new.ename, sal= :new.sal where empno=:new.empn o;
end if;
end if;
end;[/CODE]
Note:--instead of triggers are always written on views complex views where it is not possible to update all the base tables or insert records because of cascade dependency.
1.ALL INSTEAD OF TRIGGERS ARE OF ROW LEVEL BY DEFAULT.
2.INSTEAD OF TRIGGERS CAN BE DEFINED ONLY ON VIEWS.
Some more info regarding triggers
=============== =============
TO DISABLE / ENABLE TRIGGER :
--------------------------------------------------
ALTER TRIGGER <trig_name> ENABLE | [DISABLE];
ALTER TABLE <table_name> ENABLE | [DISABLE] ALL TRIGGERS;
TO DROP TRIGGER :
---------------------------------
DROP TRIGGER <TRIGNAME>;
TO GET DETAILS OF TRIGGERS :
---------------------------------------------------
desc USER_TRIGGERS
desc ALL_TRIGGERS
Restrictions on triggers
=============== ===
1.ONLY ONE TABLE CAN BE SPECIFIED IN THE TRIGGERING STATMENT.
2.TRIGGER CAN'T INCLUDE COMMIT,ROLLBACK AND SAVEPOINT OR ANY OF THE SET OPERATION.
3.TRIGGER BODY CAN'T DECLARE ANY LONG OR LOB DATATYPE.
--------------------------------------------------------------------------------
=============== ========
INSTEAD OF TRIGGER
----------------------------------------
[CODE=ORACLE]
create or replace trigger mytrig instead of delete or insert or update on eview
declare
a number(2);
begin
if inserting then
select count(deptno) into a from dept where deptno= :new.deptno;
if a=0 then
insert into dept values(:new.dep tno,:new.dname, :new.loc);
insert into emp ( empno,ename,sal ,deptno) values(:new.emp no,:new.ename,: new.sal,:new.de ptno);
else
insert into emp ( empno,ename,sal ,deptno) values(:new.emp no,:new.ename,: new.sal,:new.de ptno);
end if;
elsif deleting then
delete from emp where deptno= :new.deptno;
elsif updating then
if :new.deptno is not null then
raise_applicati on_error(-20003,'cannot update the deptno,dname,lo c columns OR cannot use deptno for updating');
else
update emp set empno=:new.empn o, ename= :new.ename, sal= :new.sal where empno=:new.empn o;
end if;
end if;
end;[/CODE]
Note:--instead of triggers are always written on views complex views where it is not possible to update all the base tables or insert records because of cascade dependency.
1.ALL INSTEAD OF TRIGGERS ARE OF ROW LEVEL BY DEFAULT.
2.INSTEAD OF TRIGGERS CAN BE DEFINED ONLY ON VIEWS.
Some more info regarding triggers
=============== =============
TO DISABLE / ENABLE TRIGGER :
--------------------------------------------------
ALTER TRIGGER <trig_name> ENABLE | [DISABLE];
ALTER TABLE <table_name> ENABLE | [DISABLE] ALL TRIGGERS;
TO DROP TRIGGER :
---------------------------------
DROP TRIGGER <TRIGNAME>;
TO GET DETAILS OF TRIGGERS :
---------------------------------------------------
desc USER_TRIGGERS
desc ALL_TRIGGERS
Restrictions on triggers
=============== ===
1.ONLY ONE TABLE CAN BE SPECIFIED IN THE TRIGGERING STATMENT.
2.TRIGGER CAN'T INCLUDE COMMIT,ROLLBACK AND SAVEPOINT OR ANY OF THE SET OPERATION.
3.TRIGGER BODY CAN'T DECLARE ANY LONG OR LOB DATATYPE.
--------------------------------------------------------------------------------