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

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Tue, 31 Dec 2002 16:22:43 GMT
Message-ID: <nvjQ9.2385$ph2.188733492@newssvr21.news.prodigy.com>

Yogi wrote:
> 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;

>
>
> Even if I use your suggestion, which is having them in one trigger, I
> get both the tags 'NEW' and 'CHG' in my temp table. Here is my
> combined triggerL
>
> CREATE OR REPLACE TRIGGER NewModifyCustomers
> AFTER INSERT OR UPDATE ON table
> REFERENCING NEW AS new
> OLD AS old
> FOR EACH ROW
> BEGIN
> if INSERTING AND :new.account_type = 1 then
> INSERT INTO CUSTOMERS VALUES('NEW', :new.account_no, NULL);
> else if UPDATING AND :old.account_type = 1 AND UPPER(:old.chg_who)
> NOT LIKE '%BIP%' then
> INSERT INTO CUSTOMERS VALUES('CHG', :old.account_no, NULL);
> end if;
> end if;
> END NewModifyCustomers;
>
> Please advise as to what I should do so that I only get 'NEW' instead
> of both when I create a new account.
> Thanks

I assume that 'table' is not the real table name that causes the trigger to fire. Something must be causing Oracle to think you are doing an UPDATE and INSERT every time you think you're doing an INSERT only.

I think the problem is *not* in the trigger, but in the code that is firing the trigger. It's a common coding technique to attempt an UPDATE (since most of the time the record will be there); but if an error is thrown, do an INSERT instead. You might want to see if this is the case.

I can see nothing inherently wrong with the trigger, so you need to look elsewhere. Received on Tue Dec 31 2002 - 10:22:43 CST