Database triggers on versioned feature classes

1729
1
07-12-2016 04:15 PM
Highlighted
MVP Regular Contributor

Our organization has a need to create database triggers (in Oracle 11g) that generate a unique number from a sequence. Currently, the triggers look something like this (EID is the unique number we're generating from a sequence).

CREATE OR REPLACE TRIGGER WS.SYSTEM_VALVE_EID_TRG  -- TRIGGER NAME 

BEFORE INSERT ON WS.A13246  -- ADDs TABLE

for each row

declare

max_objectid A13246.objectid%type:=0;

begin

select max(objectid) into max_objectid from SYSTEM_VALVE_PNT;

--determine if the new objectid is greater then the max from base table

if (max_objectid <:new.objectid) then

SELECT SYSVALVE_EID_SEQ.NEXTVAL INTO :NEW.EID FROM DUAL; 

end if;

--if new eid is null, new asset

if (:new.eid is null) then

SELECT SYSVALVE_EID_SEQ.NEXTVAL INTO :NEW.EID FROM DUAL; 

end if;

end;

/

I tried to alter the SQL to query max objectid from the base table and the A table but I started getting

Underlying DBMS error [ORA-040941: table WS.A13246 is mutating, trigger/function may not see it

Has anyone done stuff like this? Any advice on how to successfully make triggers on versioned feature classes (besides "don't use triggers")?

1 Reply
Highlighted
MVP Regular Contributor

After working with someone more experienced with PL-SQL, we found the "mutating" issue was with querying the A table that the trigger was supposed to be modifying. Our solution was to create a separate function to query the A table for the max ObjectID so the trigger didn't have to do it directly.

CREATE OR REPLACE FUNCTION GIS.MaxObjectID(p_owner VARCHAR2, p_tablename VARCHAR2) RETURN NUMBER IS
max_objectid NUMBER;
regid NUMBER;
vc2SqlStr VARCHAR2(1000);
/******************************************************************************
PURPOSE: Allow EID triggers to find the max ObjectID in the base table
and the add table when determining if the EID value should be
updated. Without this check, modifying new features that have
not been compressed to the base table will get a new EID each
time an edit is saved (when a record is inserted into the add
table).
In the case of a brand new feature class without rows, the
NVL statement is needed to return 0 instead of -1 for the very
first record.
This function is meant to be used by EID triggers
across all schemas.

Takes input of schema owner and table name as separate
parameters. Has look-up in table registry for add table.

Returns the maximum ObjectID as a number or -1 if error.
******************************************************************************/

BEGIN
    BEGIN
       
        select tr.REGISTRATION_ID
        into regid
        from SDE.TABLE_REGISTRY tr
        where tr.OWNER = p_owner and tr.TABLE_NAME = p_tablename;
       
        EXCEPTION WHEN OTHERS THEN
            regid := 0;

    END;
   
    vc2SqlStr := 'select NVL(max(objectid), 0) from (
        select max(OBJECTID) as objectid from '
||p_owner||'.'||p_tablename||
        ' union all
        select max(OBJECTID) as objectid from '
||p_owner||'.A'||regid||
        ')';
   
    BEGIN
   
        EXECUTE IMMEDIATE vc2SqlStr INTO max_objectid;
        EXCEPTION WHEN OTHERS THEN
            max_objectid := -1;
   
    END;
    RETURN max_objectid;
END MaxObjectID;
/‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

It returns the max ObjectID of whatever table you want or -1 if there was an error. Every trigger calls this function so we made it in a generic "GIS" schema. Here's an example of a trigger:

CREATE OR REPLACE TRIGGER WS.HYDRANT_EID_TRG
BEFORE INSERT ON WS.A658  -- Add Table Name
for each row
declare
    max_objectid WS.A658.objectid%type:=0;
begin
max_objectid := GIS.MaxObjectID('WS', 'HYDRANT_PNT');  -- Base Table Owner, Name
if max_objectid <> -1 and (:new.eid is NULL or :new.objectid > max_objectid) then
    SELECT HYDRANT_EID_SEQ.NEXTVAL INTO :NEW.EID FROM DUAL;
end if;
end;
/