Database triggers on versioned feature classes

Question

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

blake.terhune · Answer

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