As suggested before, but to be really certain: is this the only trigger on
the table?
Query USER_TABLES where table_name='...' to verify this.
Yogi <yagnesh_shah_at_hotmail.com> schreef in berichtnieuws
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 Tue Dec 31 2002 - 09:12:05 CST
