This discussion is archived
13 Replies Latest reply: Oct 16, 2012 9:40 PM by Simon Greener RSS

Update Date Column with Trigger When Geometry Updated

498405 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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
    498405 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    498405 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    498405 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    498405 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    498405 Newbie
    Currently Being Moderated
    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
    498405 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Jeff,

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

    regards
    Simon

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points