Trigger conflict PL/SQL

Question

These 3 tables are connected, using short-circuit keys, ID_KOEFICIJENT is in table RADNIK through table RADNO_MESTO. And I have a problem with triggers. I think it's a conflict between triggers. When I want to update the table RADNIK, for example:

update radnik set ID_KOEFICIJENT=3, prezime_ime='Perica Milisav', datum_rodjenja='2020-03-23', 
zanimanje='astronaut', id_radno_mesto=3,stepen_strucne_spreme='7.3', identifikator_casova_rada=7 
where ID_RADNIK=10;

It will update everything except ID_KOEFICIJENT but it won't give back a message from trigger UIK_RADNIK if I change ID_KOEFICIJENT to 4,5,6,7... .

Table RADNIK: Primary key: ID_RADNIK, Foreign keys: ID_KOEFICIJENT and ID_RADNO_MESTO

CREATE TABLE "RADNIK"(
"ID_RADNIK" NUMBER(*,0), 
"JMBG" NUMBER(*,0), 
"PREZIME_IME" VARCHAR2(100 BYTE), 
"DATUM_RODJENJA" DATE, 
"ZANIMANJE" VARCHAR2(100 BYTE), 
"STEPEN_STRUCNE_SPREME" VARCHAR2(100 BYTE), 
"IDENTIFIKATOR_CASOVA_RADA" NUMBER(*,0), 
"ID_KOEFICIJENT" NUMBER(*,0), 
"ID_RADNO_MESTO" NUMBER

)

Table RADNO_MESTO: Primary key: ID_RADNO_MESTO, Foreign key: ID_KOEFICIJENT

CREATE TABLE "RADNO_MESTO"(
"ID_RADNO_MESTO" NUMBER(*,0), 
"NAZIV" VARCHAR2(200 BYTE), 
"BR_IZVRSILACA" NUMBER(*,0) DEFAULT 0, 
"DATUM_OD" DATE, 
"DATUM_DO" DATE, 
"ID_KOEFICIJENT" NUMBER(*,0)
)

Table KOEFICIJENT: Primary key: ID_KOEFICIJENT

CREATE TABLE "KOEFICIJENT"(
"ID_KOEFICIJENT" NUMBER(*,0), 
"BROJ" FLOAT(126), 
"DATUM_OD" DATE, 
"DATUM_DO" DATE
)

This trigger restricts directly updating column ID_KOEFICIJENT in table RADNIK. Trigger: UIK_RADNIK:

create or replace TRIGGER UIK_RADNIK
BEFORE UPDATE OF ID_KOEFICIJENT ON RADNIK 
FOR EACH ROW
BEGIN
    if :new.ID_KOEFICIJENT <> :old.ID_KOEFICIJENT then
        RAISE_APPLICATION_ERROR(-20000, 'Zabranjeno direktno ažuriranje koeficijenta radnika!');
    END IF;
END;

This trigger updates column ID_KOEFICIJENT in table RADNIK by using column ID_RADNO_MESTO. Trigger: UIK_RADNO_MESTO:

create or replace TRIGGER UIK_RADNO_MESTO 
AFTER UPDATE OF ID_KOEFICIJENT ON RADNO_MESTO 
FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    EXECUTE IMMEDIATE 'ALTER TRIGGER UIK_RADNIK DISABLE';
        UPDATE RADNIK
        SET ID_KOEFICIJENT = :NEW.ID_KOEFICIJENT
        WHERE ID_RADNO_MESTO = :NEW.ID_RADNO_MESTO;
    EXECUTE IMMEDIATE 'ALTER TRIGGER UIK_RADNIK ENABLE';
END;

Trigger: UIRM_RADNIK:

create or replace TRIGGER UIRM_RADNIK 
BEFORE UPDATE OF ID_RADNO_MESTO ON RADNIK 
FOR EACH ROW 
DECLARE
    pragma AUTONOMOUS_TRANSACTION;
    v_id_koeficijent NUMBER;
BEGIN
    SELECT ID_KOEFICIJENT INTO v_id_koeficijent
    FROM RADNO_MESTO
    WHERE ID_RADNO_MESTO = :NEW.ID_RADNO_MESTO;
    :NEW.ID_KOEFICIJENT := v_id_koeficijent;
END;

Answer 1

You can set execution precedence in your triggers:

Order of execution of trigger and statements in Oracle stored procedure

And you can unify UIK_RADNIK and UIRM_RADNIK triggers too:

how to trigger when multiple columns are updated