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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
