472,353 Members | 1,961 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,353 developers and data experts.

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 8081

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

Similar topics

11
by: raulgz | last post by:
I need audit triggers that change columns value in the same record that fire trigger. I need how to do.. Thanks..
1
by: jason_s_ford | last post by:
I have several sql server databases that were recently moved to a new server. In the process of migrating the databases, any triggers and...
4
by: Mark Flippin | last post by:
I'm just starting to use triggers in my databases and find the support in Enterpise Manager lacking. Using Enterprise Manager and Query Analyzer...
1
by: tim.pascoe | last post by:
I'm trying to generate scrips for a database, and everything so far has worked fine, except for the triggers. When I try and script existing...
4
by: stacdab | last post by:
We have a partitioned view with 4 underlying tables. The view and each of the underlying tables are in seperate databases on the same server....
5
by: Bruce | last post by:
I have several user defined functions which are referenced in triggers and views. For software upgrades, I need to be able to drop the triggers...
0
by: Bruno Lavoie | last post by:
Hello, i'm etablishing a naming convention for a new project under postgresql. For tables, sequences, views, that's ok! I used good naming...
0
debasisdas
by: debasisdas | last post by:
This thread contains some useful tips/sample codes regarding TRIGGERS in oracle, that the forum members may find useful. TRIGGERS:...
4
by: --CELKO-- | last post by:
I need to convert a bunch of DB2 triggers to Oracle. Is there any kind of tools for this?
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.