AnsweredAssumed Answered

Database triggers on versioned feature classes

Question asked by blake.terhune on Jul 12, 2016
Latest reply on Feb 24, 2017 by blake.terhune

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

Outcomes