DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: trigger

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    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

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    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"...

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi jurij

    why do you think both of them will fire ?thanks pipo ia m also tryin to test this..:-)

    regards
    Hrishy

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    (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

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    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"

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    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
  •  


Click Here to Expand Forum to Full Width