Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Insert & Update triggers fire together when a new record is created.
"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 - 05:42:27 CST