473,320 Members | 2,029 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,320 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 8279

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 constraints attached to tables were removed on accident. ...
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 you can maintain the triggers, but it's...
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 triggers, all I get is a blank file - no SQL script. I...
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. Inserts and deletes on the view work fine. We then...
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 and views which reference these user defined...
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 conventions for this in the past and i'll keep these...
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: =============== Database trigger is a PL/SQL block that is...
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: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.