Is it possible to know what triggers would fire given a query?

Question

I have a database with (too) many triggers. They can cascade.

I have a query, which seems simple, and by no means I can remember the effect of all triggers. So, that simple query might actually be not simple at all and not do what I expect.

Is there a way to know what triggers would fire before running the query, or what triggers have fired after running it (not committed yet)?

I am not really interested in queries like SELECT … FROM user_triggers WHERE … because I know them already, and also because it does not tell me whether the firing conditions of the triggers will be met in my query.

Thanks

Answer 1

"I have a database with (too) many triggers. They can cascade."

This is just one of the reasons why many people anathematize triggers.

"Is there a way to know what triggers would fire before running the query"

No. Let's consider something which you might find in an UPDATE trigger body:

if :new.sal > :old.sal * 1.2 then
    insert into big_pay_rises values (:new.empno, :old.sal, :new.sal, sysdate);
end if;

How could we tell whether the trigger on BIG_PAY_RISES will fire? It might, it might not depending on an algorithm we cannot parse out of the DML statement.

So, the best you can hope for is a recursive search of DBA_TRIGGERS and DBA_DEPENDENCIES to identify all the triggers which might feature in your cascade. But it's going to be impossible to identify which ones will definitely fire in any given scenario.

" or what triggers have fired after running it (not committed yet)?"

As others have pointed out, logging is one option. But if you are using Oracle 11g you have another option: the PL/SQL Hierarchical Profiler. This is a non-intrusive tool which tracks all the PL/SQL program units touched by a PL/SQL call, including triggers. One of the cool features of the Hierarchical Profiler is that it includes PUs which belong in other schemas, which might be useful with cascading triggers.

So, you just need to wrap your SQL in an anonymous block and call it with the Hierarchical Profiler. Then you can filter you report to reveal only the triggers which fired. Find out more .

Answer 2

Is there a way to know what triggers would fire before running the query, or what triggers have fired after running it (not committed yet)?

To address this I would run the query inside an anonymous block using a PL/SQL debugger.