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

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 Received on Tue Dec 31 2002 - 07:36:46 CST