Help with Trigger Mutating

Question

I am creating a Rigger that needs to be fired through a table, even creating a COMPOUND TRIGGER it accuses error of mutating, I am aware that I cannot perform a SELECT of the table I am firing to Trigger, But shouldn’t this be ignored because it is a COMPOUND TRIGGER? I did a lot of research and didn’t get a clear answer. Example of what I’m doing:

CREATE OR REPLACE TRIGGER trg_nome_trigger
FOR INSERT OR UPDATE OR DELETE ON tabela
COMPOUND TRIGGER 

AFTER EACH ROW IS

BEGIN

INSERT INTO tabela_log
SELECT CURRENT_TIMESTAMP AS dt_hr,  tb.* 
FROM tabela tb

END AFTER EACH ROW

END

I’m using oracle 11g. Could someone help me?

Answer 1

The mistake ORA-04091: table is mutating happens when you try to access a table that is being modified within a trigger. This means that at runtime any attempt to directly access one of these tables will cause this error.

To Compound Trigger is not a directive that will simply allow you to access one of these tables, actually ta tools for you to store information throughout the process. With this, you can store information from your table in "memory" and then use it in your business rules.

The fact is, it would only really be necessary to create a Compound Trigger, if you want to analyze all the data of your table during the execution of your DML. Any other situation can be resolved using a trigger normal.

The most common way to resolve this situation would be to use the data you find in pseudo lines, with the use of :OLD and :NEW

Follow an example below:

CREATE OR REPLACE TRIGGER trg_nome_trigger
  AFTER INSERT OR UPDATE OR DELETE ON tabela FOR EACH ROW
BEGIN

  INSERT INTO tabela_log (dt_hr, codigo, descricao)
  VALUES (current_timestamp, :new.codigo, NEW :descricao);

END trg_nome_trigger;

If you still want to access your table, and your table data need not necessarily be "updated", you can use the directive PRAGMA AUTONOMOUS_TRANSACTION to open a new transaction and execute its commands without this problem occurring.

With these two tips, you can already solve 99% of problems with the error ORA-04091: table is mutating.

But if you still want to use the Compound Trigger, I suggest you take a look at this article, only then it will be something much more complex to solve.