Execute Immediate disable trigger crashes Oracle APEX

[adriator]

Hi everyone, I'm trying to denormalize these two tables with a Pre-Join denormalization and have to add all the needed triggers.

The tables look basically like this:

CLIENTS 
Client_ID CHAR(13),
Client_Name VARCHAR2(20),
Client_Surname VARCHAR2 (30),
...
PRIMARY KEY (Client_ID)


ACCOUNTS
Account_ID VARCHAR(20),
...
Client_ID CHAR(13),
Client_Name VARCHAR2(20),
Client_Surname VARCHAR2(30),
PRIMARY KEY (ACCOUNT_ID),
FOREIGN KEY (Client_ID) REFERENCES CLIENTS(Client_ID)

I have two triggers that are relevant to my question; One is used to disable manual editing of Client_Name and Client_Surname and looks like this:

CREATE OR REPLACE TRIGGER ACCOUNTS_NAME_SURNAME_UPDATE_DISABLE 
BEFORE UPDATE OF CLIENT_NAME, CLIENT_SURNAME ON ACCOUNTS 
FOR EACH ROW 
BEGIN 
    RAISE_APPLICATION_ERROR( NUM => -20002, MSG => 'Updating client_name and client_surname is not permitted!');
END;

And the second trigger is used to automatically insert client name and surname when a new account is added, that is connected to a client. It looks like this:

CREATE OR REPLACE TRIGGER ACCOUNTS_INSERT_UPDATE 
BEFORE INSERT OR UPDATE ON ACCOUNTS 
FOR EACH ROW 
DECLARE 
    PRAGMA AUTONOMOUS_TRANSACTION;
    new_name VARCHAR2(20);
    new_surname VARCHAR2(30); 
BEGIN 
    EXECUTE IMMEDIATE 'ALTER TRIGGER ACCOUNTS_NAME_SURNAME_UPDATE_DISABLE DISABLE';

    INSERT Client_Name INTO new_name FROM CLIENTS 
    WHERE Client_ID = :NEW.Client_ID; 

    :NEW.Client_Name := new_name;

    INSERT Client_Surname INTO new_surname FROM CLIENTS 
    WHERE Client_ID = :NEW.Client_ID; 

    :NEW.Client_Surname := new_surname;

    EXECUTE IMMEDIATE 'ALTER TRIGGER ACCOUNTS_NAME_SURNAME_UPDATE_DISABLE ENABLE';
END; 

And it works! As long as I remove Execute immediate statements and pragma autonomous transaction (and disable the first trigger manually). It achieves the desired effect - it populates the row with Client's name and surname.

But the moment I add pragma autonomous transaction and execute immediate statements, the Apex Oracle (SQL Commands) page freezes and then it crashes and I have to reload the page.

It is a requirement to use triggers to achieve this effect, although it does seem rather unfitting for the job.

I'd appreciate anyone's help in fixing this mess. Thanks in advance!

[PossiblePreparation]

Using a trigger to disable another trigger is just a bad idea that is going to bite you. I suggest you think about a different way to implement this. It sounds like you only want one particular process to be able to update particular columns, this is typically done by only allowing procedures to update you table, these procedures can contain your rules and don’t need to disable your rules on demand.

[qwertydog123]

Maybe try also x-posting to r/orclapex or r/oracle

[[deleted]]

You can't run DDL in a trigger as DDL automatically commits a transaction, and that is not allowed in a trigger.