By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,854 Members | 849 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

TRIGGERS - 3

debasisdas
Expert 5K+
P: 8,127
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
Share this Article
Share on Google+