-
trigger
Hi
I have a trigger somethin like this
CREATE OR REPLACE TRIGGER trg_ext_interface
AFTER UPDATE OF status, alloc_qty ON ORDR
FOR EACH ROW
WHEN ( NEW.source IS NOT NULL AND NEW.external_ref1 IS NOT NULL
AND (NEW.status IN ( 'A','M','S','O','T','Q','L','C','W') OR (NEW.status = 'M' AND NEW.alloc_qty = NEW.quantity)))
CREATE OR REPLACE TRIGGER "testr".trg_ext_interface_mkt
AFTER UPDATE OF alloc_qty on ORDR FOR EACH ROW
WHEN ( new.source is not null and new.external_ref1 is not null
and new.status = 'M' and new.alloc_qty = new.quantity)
when we update the alloc_qty on ordr table do both the triggers fire or only one of the triggers fire ..
regards
Hrishy
-
good question that deserves being tested
I have not done so, but I guess that if only one of your triggers is fired (due to the WHEN clause), there won't be any problem, but if you update a and new.status = 'M' and both triggers should be fired, there are chances that the second trigger's execution will fail with error ORA-04091: "table %s.%s is mutating, trigger/function may not see it"...
-
Both of the triggers will fire, however there is no guarantie in which order will they be fired.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Hi jurij
why do you think both of them will fire ?thanks pipo ia m also tryin to test this..:-)
regards
Hrishy
-
Originally posted by hrishy
Hi jurij
why do you think both of them will fire ?thanks pipo ia m also tryin to test this..:-)
I'm not guessing, I simply know.
Code:
SQL> create table test (c1 number);
Table created.
SQL> insert into test(c1) values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> CREATE OR REPLACE TRIGGER trg1 AFTER UPDATE OF c1 ON TEST
2 FOR EACH ROW
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('I''m the first trigger and I''ve just been fired');
5 END;
6 /
Trigger created.
SQL> CREATE OR REPLACE TRIGGER trg2 AFTER UPDATE OF c1 ON TEST
2 FOR EACH ROW
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('I''m the second trigger and I''ve just been fired');
5 END;
6 /
Trigger created.
SQL> set serveroutput on
SQL> update test set c1 = 0;
I'm the second trigger and I've just been fired
I'm the first trigger and I've just been fired
1 row updated.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
(I'm about to display profound ignorance on how triggers work - ah well, here goes . . . . )
Couldn't the two triggers be combined into one, by using logic on old & new values to see which column has changed?
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Re: trigger
Originally posted by hrishy
Hi
I have a trigger somethin like this
CREATE OR REPLACE TRIGGER trg_ext_interface
AFTER UPDATE OF status, alloc_qty ON ORDR
FOR EACH ROW
WHEN ( NEW.source IS NOT NULL AND NEW.external_ref1 IS NOT NULL
AND (NEW.status IN ( 'A',[b]'M'[b],'S','O','T','Q','L','C','W') OR ( NEW.status = 'M' AND NEW.alloc_qty = NEW.quantity)))
regards
Hrishy
Hrishy i dont usderstand ur logic here, but the OR simply is useless...or rather i cud put in words....irrespective of this quantity "NEW.alloc_qty = NEW.quantity" being equal or not ur trigger will fire...
And S logically both trigger wud fire for status='M'
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Hi All
well thats my programmers..i am just tryin to help debug wantin to find out which trigger would fire..so i wrote a geenric DEBUG program with util_file i am handing it over to the developers..Hmm abhay i see dude you and Dapi have got a very valid point there..i need to discuss with the developers tomorrow..
regards
Hrishy
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|