I am using Oracle 11g on Solaris platform. I have created a trigger which inserts an entry in test table for every insert/update/delete on ORDERS table. How can I use the same trigger for say 100 tables? Do I need to create 100 triggers i.e. one trigger on each table on whose I want to calculate DML operations?
Asked
Active
Viewed 2,589 times
0
-
1[Is this for auditing?](http://stackoverflow.com/q/8922032/266304) You can't use the same trigger for DML on multiple tables, but you could generate the code to create all the triggers. But first check you aren't reinventing the wheel... – Alex Poole Mar 28 '17 at 13:49
-
It depends from your requirements. In most cases the easiest and fast way will be created 100 triggers. But There are two other options. Oracle Audit and LogMiner. Level of difficulty for triggers is 1 , for audit 10, for logminer 1000 :) – Arkadiusz Łukasiewicz Mar 28 '17 at 13:50
1 Answers
1
A trigger can belong to only one table. So you need one hundred triggers in your situation. From your description it seems like they will all take the same form, so you could generate the CREATE TRIGGER statements using the data dictionary.
If the processing is complex you should wrap the logic in a stored procedure and call that from the (generated) triggers.
APC
- 144,005
- 19
- 170
- 281