472,995 Members | 1,599 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,995 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 8224

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: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
SueHopson
by: SueHopson | last post by:
Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...

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.