Problem solve Get help with specific problems with your technologies, process and projects.

Getting the table name within a trigger

Within a trigger, is there a way to get the table name for which the trigger is defined?

I have an auditing task that I'd like to create one trigger and use it on multiple tables. The trigger would take the table name and use this to determine a record to update in a separate audit table.

The auditing task is to keep track of last update date to a "Data Set". A "Data Set" could be made up of a single table or multiple related tables. This can easily be done with multiple triggers specific to the "Data Sets" and individual tables. I'd like to minimize the number of triggers to maintain.

Sorry, but there is not a way to create such a "generic" type of table level trigger. While you already know about creating the multiple table level triggers, another option could be to write a stored procedure that could do the updates to your audit table. The various triggers you create could simply call the "generic" stored procedure, passing only the table name and any other pertinent information and then doing all the work of updating the audit table from a single place (i.e. in the stored procedure). This would certainly reduce the maintenance burden if you need to make a change to the audit table update code and keep you from having to have alot of duplicate code in many individual triggers. You'd still have the individual triggers, but each trigger would execute the same "generic" stored procedure.

For More Information

Dig Deeper on Oracle and SQL