Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How to get TABLE NAME in trigger

Posted on 2005-03-29
Medium Priority
Last Modified: 2008-10-27
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!
Question by:csreeni

Assisted Solution

neo9414 earned 150 total points
ID: 13654539
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.....)

LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 150 total points
ID: 13656554
They don't like that approach.  This is a followup to:
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 75 total points
ID: 13656764
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.

Expert Comment

ID: 22803616
DECLARE @SelectSql VARCHAR(8000)

SET @TriggerID = @@PROCID;
SELECT @TableID = parent_id FROM sys.triggers  WHERE object_id = @TriggerID
LVL 35

Expert Comment

by:Mark Geerlings
ID: 22811566
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!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question