CODEWITHSUNDEEP
sqloracle-databaseplsqltriggersoracle12c
nhle
nhle

Reputation: 57

Oracle PL/SQL Trigger: Reset column value automatically after an update statement

For testing purpose I would like to have the following scenario to be automated:

  1. Set column Status of my table to the value closed (using an update statement)

  2. Commit the update so that other user can be seeing new value of Status

  3. Wait for 1 minutes

  4. Reset column Status of my table to its original value init (using a trigger)

  5. Commit the update so that other user can be seeing the original value of Status

I've tried using this trigger but it won't work and I got

ORA-04091: table name is mutating, trigger/function may not see it

CREATE OR REPLACE TRIGGER RESET_COLUMN
AFTER UPDATE OF STATUS ON MY_TABLE
FOR EACH ROW
WHEN (NEW.STATUS != 'INIT')
BEGIN
    DBMS_LOCK.SLEEP(60);  
    UPDATE MY_TABLE SET STATUS = 'INIT';
    COMMIT;
END;
/

Since it is not allowed to commit inside a trigger, is there any other approach to solve this problem? Using a job based on event?

Upvotes: 2

Views: 1452

Answers (2)

APC
APC

Reputation: 146249

To run this as a job you need a procedure you can call. I presume you want to do this for a specific record not the whole table, hence the ID parameter.

create or replace procedure reset_my_table_status
    ( p_id in number )
is
begin 
    update my_table
    set status = 'INIT'
    where id = p_id;
    commit;
end;
/

Then, from your trigger submit a job to call that procedure after sixty seconds:

CREATE OR REPLACE TRIGGER RESET_COLUMN
AFTER UPDATE OF STATUS ON MY_TABLE
FOR EACH ROW
WHEN (NEW.STATUS != 'INIT')
DECLARE
    jn number;
    pragma autonomous_transaction;
BEGIN
    dbms_job.submit(jn 
                   , what=>'reset_my_table_status('||:new.id||');'
                   , next_date => sysdate + 60/86400
      );
    commit;
END;
/

Setting the next_date parameter means the job will trigger in sixty seconds, so no need for the sleep() call. Remember, for jobs to run you need JOB_QUEUE_PROCESSES init parameter to have value > 0. We have to commit to submit a job; so we need to have an autonomous transaction because normally we cannot issue commits from triggers.

Alternatively, you could just build a procedure (or even an anonymous block).

create or replace procedure my_table_status_test
    ( p_id in number )
is
begin 
    update my_table
    set status = 'MEH'
    where id = p_id;
    commit;

    DBMS_LOCK.SLEEP(60);  

    update my_table
    set status = 'INIT'
    where id = p_id;
    commit;
end;
/

Then just run the procedure for whatever ID you want to test.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270361

This seems like such a bad idea. sleep() inside of a trigger? That will just compound locks and tie up resources.

Instead, you can use a view or virtual columns. Store the closeDate as a column in the table (you can use a trigger to set this if you like).

alter table my_table
    add new_status as (case when closeDate > sysdate - 1 / (24*60) then 'closed' else status end);

Upvotes: 1

Related Questions