trigger sample code Ex#10
=======================
INSTEAD OF TRIGGER
----------------------------------------
-
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.deptno,:new.dname,:new.loc);
-
insert into emp ( empno,ename,sal,deptno) values(:new.empno,:new.ename,:new.sal,:new.deptno);
-
else
-
insert into emp ( empno,ename,sal,deptno) values(:new.empno,:new.ename,:new.sal,:new.deptno);
-
end if;
-
-
elsif deleting then
-
delete from emp where deptno= :new.deptno;
-
-
elsif updating then
-
if :new.deptno is not null then
-
raise_application_error(-20003,'cannot update the deptno,dname,loc columns OR cannot use deptno for updating');
-
else
-
update emp set empno=:new.empno, ename= :new.ename, sal= :new.sal where empno=:new.empno;
-
end if;
-
end if;
-
end;
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.
--------------------------------------------------------------------------------
