sql - ORACLE Trigger on INSERT and UPDATE for table containing the trigger condition -

i have configuration table records used control process in application. have table controlled application, it's not possible (no money, no time etc.). so, table managed client sql developer or so. ensure thet table filled need trigger, because check constraints don't work custom functions. trigger on insert works fine, have trouble trigger on update, because conditions trigger check in table error oracle, table being updated @ moment, trigger can't fired.

table consists of following columns:

id, source_system, target_system, table_id, valid_from, valid_through  1,             2,             3,      455, 01.12.2011.    02.11.2013 

the condition following:

• can't have new record same source_system, target_system , table_id , dates overlap existing ones - new.valid_from , new.valid_through must outside existing period. example - both must < 01.12.2011 or > 02.11.2013.

so question is, there way make work on update too? i've read using materialized view trigger, think might have problem data between table , view not being in sync.

thanks help! al

ps. using oracle release 12.1.0.2.0

update: here's on insert trigger:

create or replace  trigger single_quellsystem_insert before insert on ctl_webadmin_abgleich each row declare      v_count_rows number; begin   dbms_output.enable (buffer_size => null);   dbms_output.put_line('start...');    if(:new.cldb_quellsystem_id = :new.cldb_zielsystem_id)     raise_application_error(-20336, 'quellsystem ist gleich dem zielsystem. bitte, korrigieren sie ihre abfrage.');   end if;    if(:new.gueltig_bis < :new.gueltig_von)     raise_application_error(-20338, 'datum bis liegt vor datum von. bitte, korrigieren sie ihre abfrage.');   end if;         select count(*) v_count_rows ctl_webadmin_abgleich;   dbms_output.put_line('anzahl der zeilen in der tabelle ctl_webadmin_abgleich: ' || v_count_rows);   if (v_count_rows >=1 )     dbms_output.put_line('mehrere zeilen in der tabelle ctl_webadmin_abgleich vorhanden. checking trigger condition...');     -- r in (select distinct cldb_quellsystem_id,cldb_webadmin_table_id, gueltig_bis, gueltig_von ctl_webadmin_abgleich gueltig_von >= sysdate )     r in (select distinct cldb_quellsystem_id,cldb_webadmin_table_id, gueltig_bis, gueltig_von ctl_webadmin_abgleich)     loop       if ((r.cldb_quellsystem_id != :new.cldb_quellsystem_id or r.cldb_quellsystem_id = :new.cldb_quellsystem_id) , r.cldb_webadmin_table_id = :new.cldb_webadmin_table_id )       dbms_output.put_line('ein anderes quellsystem wurde für das system: ' || r.cldb_quellsystem_id || ' schon spezifiziert. checking gültigkeit...');         if (r.gueltig_bis null or (:new.gueltig_bis >= r.gueltig_von , :new.gueltig_bis <= r.gueltig_bis) or              (:new.gueltig_von >= r.gueltig_von , :new.gueltig_von <= r.gueltig_bis) or              (:new.gueltig_von <= r.gueltig_von , :new.gueltig_bis >= r.gueltig_bis))             raise_application_error(-20337, 'gültigkeitsbereiche mit schon existierenden einträgen kollidieren!');         end if;       end if;     end loop;   end if; end; 

the on update trigger same, except declaration part,

create or replace  trigger single_quellsystem_update before update on ctl_webadmin_abgleich each row ... 

and error i'm getting:

ora-04091: table cldbdef.ctl_webadmin_abgleich mutating, trigger/function may not see 

ora-04091: table cldbdef.ctl_webadmin_abgleich mutating, trigger/function may not see it

mutating error occurs when statement causes trigger fire , trigger references table caused trigger.

how avoid :

1. don't use triggers
2. use "after" or "instead of" trigger
3. re-work trigger syntax
4. use autonomous transactions

reference: -

fix oracle mutating error