1 Reply Latest reply: Apr 17, 2012 8:17 AM by hitgon RSS

    Trigger not firing for streams transactions

    903260
      Hello all,

      I'm having some trouble with getting a trigger to fire, on a bi-directional streams setup running on 11.1.0.7. This trigger generates a user-defined type (esri ST_GEOMETRY) from "flat" data that may be loaded at either node, and everything works properly for local transactions; the "flat" data streams properly, and changes are replicated at each node, but the trigger is not being fired by any "streamed" inserts. I don't get a geometry object, nor do I get any errors; I can manually generate the geometry from the flat data with no problem, so I don't think there is any issue with the shape generation.

      I'm aware of the dbms_ddl.set_trigger_firing_property routine, and as far as I can tell, the trigger is set to fire for all transactions.

      There is another row-level trigger on the same table, which is set to fire once; I don't know if that would cause any problems, but this setup has run without issue in production for over a year, and I've only seen the problem since we moved our database to a new server.

      I'm sort of at a loss for how to troubleshoot this, and would welcome any suggestions.

      Thanks in advance.
      CREATE OR REPLACE TRIGGER "GYPSY"."GENERATE_TEST_SHAPE" 
      BEFORE INSERT OR UPDATE OR DELETE 
      ON "GYPSY"."TEST_FLAT" 
      REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW 
      declare
        myErrorCode NUMBER; --sql error code                    
        myErrorMessage VARCHAR2(512);                  
      begin                 
          
      if inserting then           
        insert into gypsy.test_shape (id, shape) values (:new.id,  gypsy.utm27_to_wgs84(:new.easting, :new.northing, :new.zone));               
      elsif updating then           
        update gypsy.test_shape p set shape =  gypsy.utm27_to_wgs84(:new.easting, :new.northing, :new.zone) where p.id = :old.id;           
      elsif deleting then          
        delete from gypsy.test_shape p where p.id = :old.id;          
      end if;           
              
      EXCEPTION                  
        WHEN OTHERS THEN                    
          myErrorCode := sqlcode;                    
          myErrorMessage := sqlerrm;                           
          insert into gypsy.error_summary                  
            values (null, myErrorCode, sysdate, null, null, null, null, myErrorMessage, null, null, null);                     
      END;
      /
      
      begin
        DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY(
           trig_owner         =>  'gypsy',
           trig_name          =>  'generate_test_shape',
           fire_once          =>  false);
      end;
      /
      
      anonymous block completed
      
      DECLARE
       ret boolean;
       triggers dbms_utility.uncl_array;
      begin
        triggers(1) := 'generate_test_shape';
        for i in 1..1 loop
          ret := DBMS_DDL.IS_TRIGGER_FIRE_ONCE(trig_owner => 'gypsy', trig_name => triggers(i));
          if (ret = true) then
            dbms_output.put_line(triggers(i)||' fires once.');
          else
            dbms_output.put_line(triggers(i)||' fires twice.');
          end if;
        end loop;
      end;
      /
      
      generate_test_shape fires twice.
      
      anonymous block completed