13 Replies Latest reply: Oct 16, 2012 11:40 PM by Simon Greener RSS

    Update Date Column with Trigger When Geometry Updated

    Jeff Hobbs
      Hi,

      I have a column named "GEO_MOD_DATE" that is supposed to be updated whenever my geometry column is updated. My trigger code is below. Currently the trigger is firing when columns other than my geometry column are updated. I've searched the forum and many other sites. As best as I can tell, my trigger is written correctly. But I can't figure out why it's firing when columns other than my geometry column are being updated. Do you have any thoughts? Thanks in advance! Note that the trigger does fire when I update the geometry column. However it's also firing when other columns are updated. I've also tried adding the "OF GDO_GEOMETRY" clause to after "BEFORE UPDATE" and that didn't help.

      Jeff

      Trigger:

      CREATE OR REPLACE TRIGGER OPER_ZONE_VALVE_GEO_BU_T
      BEFORE UPDATE
      ON OPER_ZONE_VALVE
      REFERENCING NEW AS NEW OLD AS OLD
      FOR EACH ROW
      DECLARE
      v_foo NUMBER (9);
      BEGIN
      -- comment out starting below if you do not want to assign GEO_MOD_DATE
      BEGIN
      IF UPDATING ('GDO_GEOMETRY') and :NEW.gdo_geometry IS NOT NULL
      THEN
      :NEW.geo_mod_date := SYSDATE;
      ELSE
      :NEW.geo_mod_date := NULL;
      END IF;
      EXCEPTION
      WHEN OTHERS
      THEN
      raise_application_error
      (-20013,
      'Unable to autoassign OPER ZONE VALVE field.'
      );
      END;

      /*****/
      NULL;
      EXCEPTION
      WHEN OTHERS
      THEN
      raise_application_error
      (-20013,
      'error in execution of BEFORE INSERT TRIGGER OPER_ZONE_VALVE_GEO_BU_T;'
      );
      END;
      /

      --------------------------------------------
      And here is my table structure:

      CREATE TABLE OPER_ZONE_VALVE
      (
      MSLINK NUMBER(38),
      VALVE_NUMBER VARCHAR2(8 BYTE),
      ECM_NUMBER VARCHAR2(8 BYTE),
      VALVE_SIZE VARCHAR2(4 BYTE),
      CONNECTION_TYPE VARCHAR2(100 BYTE),
      HEADER_STREET VARCHAR2(100 BYTE),
      HEADER_FEET VARCHAR2(8 BYTE),
      HEADER_DIR VARCHAR2(4 BYTE),
      HEADER_PROP VARCHAR2(4 BYTE),
      SUB_STREET VARCHAR2(100 BYTE),
      SUB_FEET VARCHAR2(8 BYTE),
      SUB_DIR VARCHAR2(4 BYTE),
      SUB_PROP VARCHAR2(4 BYTE),
      PLAT_MAP_NUMBER VARCHAR2(4 BYTE),
      CREATED_BY VARCHAR2(50 BYTE) DEFAULT USER,
      CREATED_DATE DATE DEFAULT SYSDATE,
      MODIFIED_BY VARCHAR2(50 BYTE),
      MODIFIED_DATE DATE,
      GEO_MOD_DATE DATE,
      REMARK VARCHAR2(40 BYTE),
      GDO_GEOMETRY MDSYS.SDO_GEOMETRY
      )
        • 1. Re: Update Date Column with Trigger When Geometry Updated
          Dan Abugov
          Hi Jeff,

          You only want to fire the trigger when gdo_geometry is updated, so change the BEFORE clause to something like this (you will want to remove "INSERT OR" below if you don't need them):
          BEFORE INSERT OR UPDATE OF GDO_GEOMETRY ON OPER_ZONE_VALVE

          The other item is logic related:

          IF UPDATING ('GDO_GEOMETRY') and :NEW.gdo_geometry IS NOT NULL
          ...
          ELSE

          The else is triggered if you aren't updating GDO_GEOMETRY. Hopefully changing the BEFORE clause above means you don't have to perform the IF UPDATING ('GDO_GEOMETRY')

          Hope this helps,
          Dan
          • 2. Re: Update Date Column with Trigger When Geometry Updated
            Jeff Hobbs
            Hi Dan,

            So I think you're saying take this approach, right? This is referencing another table. But I believe this is what your'e suggesting.

            If so, this was my original trigger that was incorrectly firing. So that's why I went to the changes I documented in my original post - UPDATING ('GDO_GEOMETRY')

            CREATE OR REPLACE TRIGGER AIR_VALVE_GEO_BU_T
            BEFORE UPDATE OF gdo_geometry
            ON AIR_VALVE
            REFERENCING NEW AS NEW OLD AS OLD
            FOR EACH ROW
            BEGIN
            IF :NEW.gdo_geometry IS NOT NULL
            THEN
            :NEW.geo_mod_date := SYSDATE;
            ELSE
            :NEW.geo_mod_date := NULL;
            END IF;
            END;
            /

            I've read that the BEFORE UPDATE OF doesn't work with data types like CLOB. So I figured the same rule would apply to SDO_GEOMETRY. So the work around I found was to use the "UPDATING('GDO_GEOMETRY'). But that too doesn't' seem to help.

            Am I missing something here?

            Thanks!

            Jeff
            • 3. Re: Update Date Column with Trigger When Geometry Updated
              _jum
              Hi Jeff,

              the trigger works fine for me, so where is your problem exactly?
              DROP   TABLE oper_zone_valve;
              
              CREATE TABLE oper_zone_valve
              (
                mslink            NUMBER (38),
                valve_number      VARCHAR2 (8 BYTE),
                ecm_number        VARCHAR2 (8 BYTE),
                valve_size        VARCHAR2 (4 BYTE),
                connection_type   VARCHAR2 (100 BYTE),
                header_street     VARCHAR2 (100 BYTE),
                header_feet       VARCHAR2 (8 BYTE),
                header_dir        VARCHAR2 (4 BYTE),
                header_prop       VARCHAR2 (4 BYTE),
                sub_street        VARCHAR2 (100 BYTE),
                sub_feet          VARCHAR2 (8 BYTE),
                sub_dir           VARCHAR2 (4 BYTE),
                sub_prop          VARCHAR2 (4 BYTE),
                plat_map_number   VARCHAR2 (4 BYTE),
                created_by        VARCHAR2 (50 BYTE) DEFAULT USER,
                created_date      DATE DEFAULT SYSDATE,
                modified_by       VARCHAR2 (50 BYTE),
                modified_date     DATE,
                geo_mod_date      DATE,
                remark            VARCHAR2 (40 BYTE),
                gdo_geometry      MDSYS.sdo_geometry
              );
              
              INSERT INTO oper_zone_valve
               ( remark, gdo_geometry)
              VALUES
               ('remark', MDSYS.sdo_geometry(2001, NULL, SDO_POINT_TYPE(  0,  0,  0), NULL,NULL));
               
              COMMIT; 
              
              CREATE OR REPLACE TRIGGER air_valve_geo_bu_t
                BEFORE UPDATE OF gdo_geometry
                ON oper_zone_valve
                REFERENCING NEW AS new OLD AS old
                FOR EACH ROW
              BEGIN
                IF :new.gdo_geometry IS NOT NULL THEN
                   :new.geo_mod_date := SYSDATE;
                ELSE
                  :new.geo_mod_date := NULL;
                END IF;
              END;
              
              SHOW ERRORS
              
              Trigger created.
              No errors.
              SET SERVEROUTPUT ON SIZE 900000;
              
              SELECT remark, gdo_geometry, geo_mod_date 
                FROM oper_zone_valve;
              
              REMARK   GDO_GEOMETRY              GEO_MOD_DATE     
              ------------------------------------------------------------------------
              remark     (2001; ; (0; 0; 0); ; )     
              
              UPDATE oper_zone_valve
                 SET remark='remark1';
              
              SELECT remark, gdo_geometry, geo_mod_date 
                FROM oper_zone_valve;
              
              REMARK   GDO_GEOMETRY              GEO_MOD_DATE     
              ------------------------------------------------------------------------
              remark1    (2001; ; (0; 0; 0); ; )     
              
              UPDATE oper_zone_valve
                 SET gdo_geometry = MDSYS.sdo_geometry(2001, NULL, SDO_POINT_TYPE( 10,  0,  0), NULL,NULL),
                     geo_mod_date = sysdate;
              
              SELECT remark, gdo_geometry, geo_mod_date 
                FROM oper_zone_valve;
              
              REMARK   GDO_GEOMETRY              GEO_MOD_DATE     
              ------------------------------------------------------------------------
              remark1    (2001; ; (10; 0; 0); ; ) 15.10.2012 09:35:45
              
              UPDATE oper_zone_valve
                 SET gdo_geometry = NULL;
                 
              SELECT remark, gdo_geometry, geo_mod_date FROM oper_zone_valve;
              
              REMARK   GDO_GEOMETRY              GEO_MOD_DATE     
              ------------------------------------------------------------------------
              remark1          
              • 4. Re: Update Date Column with Trigger When Geometry Updated
                Jeff Hobbs
                Hi. Yes, that trigger does work. The issue is that it fires more often than it should.

                For instance, this workflow will update the GEO_MOD_DATE column.

                1) Update the ECM_NUMBER column with a new value
                2) Update the same same record's ECM_NUMBER column again with a new value

                I'm not always able to replicate, but if a commit isn't done between updates to a NON-geometry column, the GEO_MOD_DATE is updating even though the actual MDSYS.SDO_GEOMETRY geometry is not updated.

                It seems like a bug to me. I've tried in 10GR2 and 11GR2. So if it's a bug, it's a long-standing bug.

                My goal is to only have the GEO_MOD_DATE update when ONLY the GDO_GEOMETRY column is updated.


                Thanks!

                Jeff
                • 5. Re: Update Date Column with Trigger When Geometry Updated
                  Simon Greener
                  I've read that the BEFORE UPDATE OF doesn't work with data types like CLOB
                  Yes. But what this means for SDO_GEOMETRY objects is that you can't directly use a WHEN clause in the Trigger definition and you can't compare two geometries directly via If ( :old.geom = :new.geom ) Then.

                  So, in your trigger the conditions for update that your business rules require the trigger to implement are not quite enough.

                  Try checking if the geometry column has actually changed.
                  DROP TABLE oper_zone_valve;
                  
                  CREATE TABLE oper_zone_valve
                  (
                    mslink            NUMBER (38),
                    valve_number      VARCHAR2 (8 BYTE),
                    ecm_number        VARCHAR2 (8 BYTE),
                    valve_size        VARCHAR2 (4 BYTE),
                    connection_type   VARCHAR2 (100 BYTE),
                    header_street     VARCHAR2 (100 BYTE),
                    header_feet       VARCHAR2 (8 BYTE),
                    header_dir        VARCHAR2 (4 BYTE),
                    header_prop       VARCHAR2 (4 BYTE),
                    sub_street        VARCHAR2 (100 BYTE),
                    sub_feet          VARCHAR2 (8 BYTE),
                    sub_dir           VARCHAR2 (4 BYTE),
                    sub_prop          VARCHAR2 (4 BYTE),
                    plat_map_number   VARCHAR2 (4 BYTE),
                    created_by        VARCHAR2 (50 BYTE) DEFAULT USER,
                    created_date      DATE DEFAULT SYSDATE,
                    modified_by       VARCHAR2 (50 BYTE),
                    modified_date     DATE,
                    geo_mod_date      DATE,
                    remark            VARCHAR2 (40 BYTE),
                    gdo_geometry      MDSYS.sdo_geometry
                  );
                  
                  CREATE OR REPLACE TRIGGER air_valve_geo_bu_t
                    BEFORE UPDATE OF gdo_geometry
                    ON oper_zone_valve
                    REFERENCING NEW AS new OLD AS old
                    FOR EACH ROW
                  BEGIN
                     IF ( NOT UPDATING('GDO_GEOMETRY') ) THEN
                      RETURN;
                    END IF;
                    IF ( :old.gdo_geometry is not null and :new.gdo_geometry IS not NULL ) THEN
                      -- Check if geometry has changed internally
                       IF ( sdo_geom.relate(:old.gdo_geometry,'DETERMINE',:new.gdo_geometry,0.005) != 'EQUAL' ) Then
                         :new.geo_mod_date := SYSDATE;
                       End If;      
                    ELSIF ( ( :old.gdo_geometry is null and :new.gdo_geometry IS NOT NULL ) OR
                         ( :old.gdo_geometry is not null and :new.gdo_geometry IS NULL ) ) THEN
                       :new.geo_mod_date := SYSDATE;
                    ELSE
                      :new.geo_mod_date := NULL;
                    END IF;
                  END;
                  /
                  SHOW ERRORS
                  Some tests
                  SET NULL NULL
                  INSERT INTO oper_zone_valve ( remark, gdo_geometry) VALUES ('remark', MDSYS.sdo_geometry(2001,NULL,SDO_POINT_TYPE(0,0,0), NULL,NULL)); COMMIT; 
                  1 rows inserted.
                  commited.
                  SELECT remark,  to_char(geo_mod_date,'YYYY-MM-DD HH24:MI:SS') as geo_mod_date, gdo_geometry FROM oper_zone_valve;
                  REMARK GEO_MOD_DATE        GDO_GEOMETRY
                  ------ ------------------- -------------------------------------------------------
                  remark NULL                SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(0,0,0),NULL,NULL)
                  
                  update oper_zone_valve set gdo_geometry = sdo_geometry(2001, NULL,SDO_POINT_TYPE( 0,0,0),NULL,NULL) where remark = 'remark'; COMMIT; 
                  1 rows updated.
                  commited.
                  SELECT remark,  to_char(geo_mod_date,'YYYY-MM-DD HH24:MI:SS') as geo_mod_date, gdo_geometry FROM oper_zone_valve;
                  REMARK GEO_MOD_DATE        GDO_GEOMETRY
                  ------ ------------------- -------------------------------------------------------
                  remark NULL                SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(0,0,0),NULL,NULL)
                  
                  update oper_zone_valve set gdo_geometry = sdo_geometry(2001, NULL,SDO_POINT_TYPE(10,0,0),NULL,NULL) where remark = 'remark'; COMMIT; 
                  1 rows updated.
                  commited.
                  SELECT remark,  to_char(geo_mod_date,'YYYY-MM-DD HH24:MI:SS') as geo_mod_date, gdo_geometry FROM oper_zone_valve;
                  REMARK GEO_MOD_DATE        GDO_GEOMETRY
                  ------ ------------------- --------------------------------------------------------
                  remark 2012-10-16 09:24:53 SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(10,0,0),NULL,NULL)
                  
                  execute dbms_lock.sleep(5);
                  anonymous block completed
                  update oper_zone_valve set gdo_geometry = NULL  where remark = 'remark'; COMMIT; 
                  1 rows updated.
                  commited.
                  SELECT remark,  to_char(geo_mod_date,'YYYY-MM-DD HH24:MI:SS') as geo_mod_date, gdo_geometry FROM oper_zone_valve;
                  REMARK GEO_MOD_DATE        GDO_GEOMETRY
                  ------ ------------------- --------------------------------------------------------
                  remark 2012-10-16 09:24:58 NULL
                  That seems to me to be the answer you are after or sets you on a path to realising the correct implementation of your business rules in the trigger.

                  Points please!!

                  regards
                  Simon

                  Edited by: Simon Greener on Oct 17, 2012 3:39 PM, Fixed not equal display problem
                  • 6. Re: Update Date Column with Trigger When Geometry Updated
                    Jeff Hobbs
                    Simon - thanks for the great reply. I'm noticing you're committing every time you make a change. I'm happy to accept your answer as it's definitely working. But I'm still having the same issue.

                    The issue I'm seeing is if you modify the valve_number of any non-spatial column more than once BEFORE a commit. Then the second update to the same column is causing the GEO_MOD_DATE to be updated. This to me seems like a bug. But I wanted to get the feedback of this forum first. You can reference my reply directly above your reply to see the full details.

                    Thanks,

                    Jeff

                    Edited by: user495402 on Oct 15, 2012 7:00 PM
                    • 7. Re: Update Date Column with Trigger When Geometry Updated
                      _jum
                      Can't reproduce your issue (Oracle 11.2.0.3.0) or don't get it.
                      Please proof the problem with a piece of SQL as we did to understand and reproduce the case:
                      --used SIMONs TRIGGER
                      
                      TRUNCATE TABLE  oper_zone_valve ;
                      
                      INSERT INTO oper_zone_valve 
                              ( remark, gdo_geometry) 
                       VALUES ('remark', MDSYS.sdo_geometry(2001,NULL,SDO_POINT_TYPE(0,0,0), NULL,NULL));
                      
                      COMMIT; 
                      
                      SELECT remark,  to_char(geo_mod_date,'YYYY-MM-DD HH24:MI:SS') as geo_mod_date, gdo_geometry 
                        FROM oper_zone_valve;
                      
                      REMARK         GEO_MOD_DATE          GDO_GEOMETRY                                      
                      ------------------------------------------------------------------------------
                      remark                               (2001; ; (0; 0; 0); ; )       
                      
                      EXECUTE dbms_lock.sleep(5);
                      
                      UPDATE oper_zone_valve
                         SET gdo_geometry = MDSYS.sdo_geometry(2001, NULL, SDO_POINT_TYPE( 10,  0,  0), NULL,NULL)
                       WHERE remark = 'remark';
                       
                      COMMIT; 
                      
                      --test the date -> TRIGGER is working
                      SELECT remark,  to_char(geo_mod_date,'YYYY-MM-DD HH24:MI:SS') as geo_mod_date, gdo_geometry 
                        FROM oper_zone_valve;
                      
                      REMARK         GEO_MOD_DATE          GDO_GEOMETRY                                      
                      ------------------------------------------------------------------------------
                      remark         2012-10-16 08:12:09   (2001; ; (0; 0; 0); ; )       
                      
                      --1) Update the ECM_NUMBER column with a new value
                      UPDATE oper_zone_valve 
                         SET ECM_NUMBER  = 1
                       WHERE remark = 'remark';
                      
                      --2) Update the same same record's ECM_NUMBER column again with a new value
                      UPDATE oper_zone_valve 
                         SET ECM_NUMBER  = 2
                       WHERE remark = 'remark';
                       
                      COMMIT; 
                      
                      --test the date -> unchanged
                      SELECT remark,  to_char(geo_mod_date,'YYYY-MM-DD HH24:MI:SS') as geo_mod_date, gdo_geometry 
                        FROM oper_zone_valve;
                      
                      REMARK         GEO_MOD_DATE          GDO_GEOMETRY                                      
                      ------------------------------------------------------------------------------
                      remark         2012-10-16 08:12:09   (2001; ; (0; 0; 0); ; )       
                      
                      EXECUTE dbms_lock.sleep(5);
                      
                      --2) Update the same same record's ECM_NUMBER column 100times with a new value
                      BEGIN
                       FOR i IN 1..100
                       LOOP
                         UPDATE oper_zone_valve 
                            SET ECM_NUMBER  = i 
                          WHERE remark = 'remark';
                        END LOOP;
                      END;   
                      
                      --test the date -> unchanged
                      SELECT remark,  to_char(geo_mod_date,'YYYY-MM-DD HH24:MI:SS') as geo_mod_date, gdo_geometry 
                        FROM oper_zone_valve;
                      
                      REMARK         GEO_MOD_DATE          GDO_GEOMETRY                                      
                      ------------------------------------------------------------------------------
                      remark         2012-10-16 08:12:09   (2001; ; (0; 0; 0); ; )       
                      • 8. Re: Update Date Column with Trigger When Geometry Updated
                        Simon Greener
                        Jeff,
                        I'm noticing you're committing every time you make a change. I'm happy to accept your answer as it's definitely working.
                        Yes I do commit each time - I need to fire the trigger. If I stack them up in a single transaction the trigger will still fire for each row.
                        But I'm still having the same issue.
                        But what is the issue?
                        The issue I'm seeing is if you modify the valve_number of any non-spatial column more than once BEFORE a commit. Then the second update to the same column is causing the GEO_MOD_DATE to be updated. This to me seems like a bug. But I wanted to get the feedback of this forum first. You can reference my reply directly above your reply to see the full details.
                        Since our trigger definition does not allow for the GEO_MOD_DATE to be changed for anything other than an actual change to the geometry column, we can't see how your assertion holds up.

                        Look, my trigger will try and execute for every update of any field. There is no WHEN clause to stop it. But the very first test:
                           IF ( NOT UPDATING('GDO_GEOMETRY') ) THEN
                            RETURN;
                          END IF;
                        ... shuts off execution if the geometry column is not being updated.

                        .... but is GDO_GEOMETRY involved in an UPDATE followed by another UPDATE that includes that and the VALVE_NUMBER?

                        Ok, perhaps you could add...
                           IF ( NOT UPDATING('GDO_GEOMETRY') 
                                AND UPDATING('VALVE_NUMBER') ) THEN
                            RETURN;
                          END IF;
                        But where do you stop as there are 18+ other columns to check as well? But this is where we don't understand the RULES you are trying to code.

                        I concur with _jum, you need to provide us with a working example that shows the actual problem. We have tried to create some examples to fill the hole but we are obviously not executing things as you expect and via which the "issue" is apparent!

                        regards
                        Simon
                        • 9. Re: Update Date Column with Trigger When Geometry Updated
                          Jeff Hobbs
                          So I've spent the last 10 mins trying to make this happen at the command line. I've yet to make the issue happen. I'm able to get it to happen easily using TOAD. But before I posted to the forum, I tried using the command line and was still able to replicate. But I don't believe I tried the command line with Simon's answer. So far, this is working. So I'm going to mark this as "answered".

                          Thanks to all for your great feedback. Hugely appreciated!

                          Jeff
                          • 10. Re: Update Date Column with Trigger When Geometry Updated
                            Simon Greener
                            So I've spent the last 10 mins trying to make this happen at the command line. I've yet to make the issue happen. I'm able to get it to happen easily using TOAD.
                            It could just be that TOAD is the problem!

                            How are you editing the data in TOAD?

                            If you have the table open in a grid and are editing the records directly in the grid then pressing commit just be careful.

                            Now, if I recall things correctly (and please let me know if I my explanation is wrong) when I have used TOAD in the past my experience has
                            been that it does executes its updates via SELECT .... FOR UPDATE which does not fire triggers. SQL Developer and any SQLPlus worksheet will
                            execute updates via independent UPDATE DML. (As such I don't use TOAD I only use SQL Developer/Plus.)

                            There are ways to check what TOAD is actually doing. Perhaps this might help if you have such DBA permissions.

                            alter system flush shared_pool;
                            -- Execute the TOAD update
                            -- Then enter ....
                            select /* NOT_ME */ sql_text from v$sql where lower(sql_text) like '%oper_zone_valve%' and sql_text not like '%NOT_ME%';

                            (Idea taken from something John O'Toole showed me. H/T John.)

                            PS Thanks for the points.

                            regards
                            Simon
                            • 11. Re: Update Date Column with Trigger When Geometry Updated
                              Jeff Hobbs
                              Thanks Simon. I just upgraded my TOAD to 11.6 and the issue seems to have been resolved. I was running 11.0 earlier today.

                              The SQL is much appreciated. Good to know so I can debug TOAD in the future if needed.

                              Have a good one!


                              Jeff
                              • 12. Re: Update Date Column with Trigger When Geometry Updated
                                Jeff Hobbs
                                Just a point of clarification for others that might want to use this code. Simon used the "not equal" sign of greater than/less than. For some reason the forum strips this out. So if you copy and paste Simon's code directly, it will not compile. So here is Simon's same line, but this time I've replaced greater than/less than with != to make it more clear. The rest of Simon's trigger worked like a charm with a simple copy/paste.
                                IF ( sdo_geom.relate(:old.gdo_geometry,'DETERMINE',:new.gdo_geometry,0.005)<>'EQUAL' ) Then
                                IF ( sdo_geom.relate(:old.gdo_geometry,'DETERMINE',:new.gdo_geometry,0.005) *!=* 'EQUAL' ) Then
                                • 13. Re: Update Date Column with Trigger When Geometry Updated
                                  Simon Greener
                                  Jeff,

                                  Thanks for pointing this out. I have fixed the original just in case.

                                  regards
                                  Simon