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

Please list code for all triggers as following: a) on the table you are inserting to
b) the temp table you are referring to.

To get the trigger code we need to check use the following

select * from dba_triggers where table_name in ('INSERT_TABLENAME','TEMPTABLENAME');

Do it as dba user (e.g system) in case someone else added the trigger.

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:<q9jQ9.180239$qF3.12819_at_sccrnsc04>...
> Are you testing this from sqlplus or from an application? I wonder if the
> application is generating an insert and then an update. It is just a guess.
> Jim
> "Yogi" <yagnesh_shah_at_hotmail.com> wrote in message
> news:272bd3a7.0212310536.42f53cfe_at_posting.google.com...
> > 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 Fri Jan 03 2003 - 16:51:40 CST