Database triggers on versioned feature classes

[BlakeTerhune]

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")?

[BlakeTerhune]

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;
/‍‍‍‍‍‍‍‍‍‍‍‍