Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Insert & Update triggers fire together when a new record is created.

Re: Insert & Update triggers fire together when a new record is created.

From: Wade Noordink <noordw_at_hotmail.com>
Date: 30 Dec 2002 13:28:15 -0800
Message-ID: <df53c44e.0212301328.735808c7@posting.google.com>


Are their any other triggers on the table? ALso, like Karsten said, it would be "cleaner" to put them in the same trigger.

Wade
yagnesh_shah_at_hotmail.com (Yogi) wrote in message news:<272bd3a7.0212300342.3b5a36a1_at_posting.google.com>...
> "Karsten Farrell" <kfarrell_at_belgariad.com> wrote in message news:<IE_O9.90206$vb3.3475470_at_news2.west.cox.net>...
> > "Yogi" <yagnesh_shah_at_hotmail.com> wrote in message
> > news:272bd3a7.0212270422.42417649_at_posting.google.com...
> > > Hi,
> > >
> > > I have two triggers, one when an insert is performed and the other
> > > when an update is performed. I use these triggers to synchronize two
> > > billing systems. So when a new account is created I update a temp
> > > table with a tag of 'NEW' and when a account is modified I send it
> > > with a tag of 'CHG'. But I create a new record both of the triggers
> > > fire and I get both the tags 'NEW' and 'CHG' in my temp table. Is
> > > there a way I could avoid this only when I am creating new accounts. I
> > > would be grateful if someone could help me. Thanks in advance.
> > >
> > > Thanks
> > > Yogi
> > Have ONE trigger (after insert or update) with an IF statement:
> >
> > if inserting then
> > ...stuff for insert...
> > elsif updating then
> > ...stuff for update...
> > end if;
> >
> > You might also need (if required) to define it as an autonomous transaction
> > so you can commit your NEW and CHG in the trigger without commiting the
> > other stuff.
>
> The insert trigger is:
> CREATE OR REPLACE TRIGGER NewCustomers
> AFTER INSERT ON table
> REFERENCING NEW AS newRow
> FOR EACH ROW
> WHEN (newRow.account_type = 1)
> BEGIN
> INSERT INTO CUSTOMERS VALUES('NEW', :newRow.account_no, NULL);
> END NewCustomers;
>
> and the modify trigger is:
> CREATE OR REPLACE TRIGGER ModifyCustomers
> AFTER UPDATE ON table
> REFERENCING OLD AS oldRow
> FOR EACH ROW
> WHEN (oldRow.account_type = 1 AND UPPER(oldRow.chg_who) NOT LIKE '%BIP%')
> BEGIN
> INSERT INTO CUSTOMERS VALUES('CHG', :oldRow.account_no, NULL);
> END ModifyCustomers;
Received on Mon Dec 30 2002 - 15:28:15 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US