How to get TABLE NAME in trigger

I have come up with a trigger(insert delete update on TAB) which calls a standardized package that inserts query information into a custom table. We want to use this same package for triggers on multiple tables. I am having problems capturing the table name.

Is there any function that gives me the name of triggering table.... in our case TAB
I tried dbms_stanadard.dictionary_obj_name, tried ora_dict_obj_name too..... both are not working. I see the table column as empty.

Any ideas!
Thx
csreeniAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

neo9414Commented:
This is what I understand..
You have triggers on each table.
you have a standard package (which is being called from the triggers).

If this is correct..then why don't you just pass the table name as parameter to the package (you already know the table name from the trigger info..create trigger...on tablename.....)

0
slightwv (䄆 Netminder) Commented:
neo:
They don't like that approach.  This is a followup to:
http://www.experts-exchange.com/Databases/Oracle/Q_21364106.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
Usually in Oracle, table triggers are "hard-coded" for each table with the table and column names fixed (not dynamic).  This is in keeping with PL\SQL's custom of using "early binding" of object names in SQL statements in PL\SQL code to database objects at compile time, to support faster execution times.

If you are willing to give up some runtime performance for the flexiblity of dynamic SQL in PL\SQL triggers and/or procedures, that may be possible, but it is likely more of a problem in triggers than in procedures.  Also, if you do find a way to do this in triggers, you will want to test the performance difference between dynamic and standard (static) SQL code in PL\SQL, especially if you have an OLTP system, to make sure that you don't introduce a performance penalty that the users do not like.
0
AdissonRuizCommented:
DECLARE @TriggerID INT
DECLARE @SelectSql VARCHAR(8000)

SET @TriggerID = @@PROCID;
SELECT @TableID = parent_id FROM sys.triggers  WHERE object_id = @TriggerID
SELECT @TableName = OBJECT_NAME(@TableID)
0
Mark GeerlingsDatabase AdministratorCommented:
To AddisonRuiz:
The syntax in your suggestion is *NOT* valid Oracle syntax.  Maybe that works in SQL Server, but it certainly will not work in an Oracle database!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.