TRIGGERS - 3

[debasisdas]

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.

--------------------------------------------------------------------------------