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