469,347 Members | 1,809 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

TRIGGERS - 3

debasisdas
8,127 Expert 4TB
trigger sample code Ex#10
=======================
INSTEAD OF TRIGGER
----------------------------------------
Expand|Select|Wrap|Line Numbers
  1. create or replace trigger mytrig instead of delete or insert or update on eview
  2. declare
  3. a number(2);
  4. begin
  5.  
  6. if inserting then
  7. select count(deptno) into a from dept where deptno= :new.deptno;
  8.     if a=0 then
  9.     insert into dept values(:new.deptno,:new.dname,:new.loc);
  10.     insert into emp ( empno,ename,sal,deptno) values(:new.empno,:new.ename,:new.sal,:new.deptno);
  11.     else
  12.     insert into emp ( empno,ename,sal,deptno) values(:new.empno,:new.ename,:new.sal,:new.deptno);
  13.     end if;
  14.  
  15. elsif deleting then
  16. delete from emp where deptno= :new.deptno;
  17.  
  18. elsif updating then
  19.     if :new.deptno is not null then
  20. raise_application_error(-20003,'cannot update the deptno,dname,loc columns OR cannot use deptno for updating');
  21.     else
  22.     update emp set empno=:new.empno, ename= :new.ename, sal= :new.sal where empno=:new.empno;
  23.     end if;
  24. end if;
  25. 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.

--------------------------------------------------------------------------------
Jun 13 '07 #1
0 7764

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

11 posts views Thread by raulgz | last post: by
1 post views Thread by jason_s_ford | last post: by
4 posts views Thread by Mark Flippin | last post: by
1 post views Thread by tim.pascoe | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
4 posts views Thread by --CELKO-- | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.