Execute immediate 'alter trigger ... disable' with pragma autonomous

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'm using the newest version of Oracle Apex, which is Oracle APEX 22.1.0-17 at the time of writing this question.

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

CodePudding user response：

Both row-level triggers fire BEFORE UPDATE on accounts table. As if Oracle can't decide it and tries to disable a trigger which is just firing to inform you that you're trying to do something you aren't supposed to and - kind of - ends up in an infinite loop.

So, which trigger will run first? As far as I can tell, Oracle doesn't specify it, but lets you decide it using follows and precedes. Something like this:

CREATE OR REPLACE TRIGGER accounts_name_surname_update_disable
   BEFORE UPDATE OF client_name, client_surname
   ON accounts
   FOR EACH ROW
   FOLLOWS accounts_insert_update                   --> this
BEGIN
   raise_application_error (
      num  => -20002,
      msg  => 'Updating client_name and client_surname is not permitted!');
END;

This trigger can be simplified (BTW, code you posted seems to be invalid; there's no such an INSERT statement - should've been SELECT):

CREATE OR REPLACE TRIGGER accounts_insert_update
   BEFORE INSERT OR UPDATE
   ON accounts
   FOR EACH ROW
   PRECEDES accounts_name_surname_update_disable    --> this
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   EXECUTE IMMEDIATE 'ALTER TRIGGER ACCOUNTS_NAME_SURNAME_UPDATE_DISABLE DISABLE';

   SELECT client_name, client_surname
     INTO :new.client_name, :new.client_surname
     FROM clients
    WHERE client_id = :new.client_id;

   EXECUTE IMMEDIATE 'ALTER TRIGGER ACCOUNTS_NAME_SURNAME_UPDATE_DISABLE ENABLE';
END;

On the other hand - even if that works - the whole process looks wrong to me. How will accounts_name_surname_update_disable trigger know that it should - or should not - fire and allow changes to those two columns? To me, only one trigger would suffice - the second one (but modified).

So: drop trigger accounts_name_surname_update_disable as it is useless.

Recreate the 2nd trigger: no need to disable/enable anything as that another trigger doesn't exist any more. Always fetch client name from clients (so, it doesn't matter that someone might type those values - trigger will override them):

CREATE OR REPLACE TRIGGER accounts_insert_update
   BEFORE INSERT OR UPDATE
   ON accounts
   FOR EACH ROW
BEGIN
   SELECT client_name, client_surname
     INTO :new.client_name, :new.client_surname
     FROM clients
    WHERE client_id = :new.client_id;
END;

Finally, is that all? I don't think so. The whole data model is wrong. ACCOUNTS table should contain only CLIENT_ID column which is a foreign key, looking at CLIENTS.CLIENT_ID:

create table clients 
  (client_id        number primary key,
   client_name      varchar2(20) not null,
   client_surname   varchar2(20) not null
  );
  
create table accounts
  (account_id       number primary key,
   client_id        number constraint fk_acc_cli references clients (client_id)
  );

Therefore, when accounts needs to know client's name, query will perform a join and fetch that data:

select a.account_id, c.client_name
from accounts a join clients c on c.client_id = a.client_id
where a.account_id = 1234;

And that should solve your problem.