Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

check if column is created by trigger

Posted on 2012-04-11
5
Medium Priority
?
406 Views
Last Modified: 2012-04-12
hi

i want to know if the prod_id column in my Product table is being generated by a trigger.

I am using this sql but no luck. Any clue what i am doing wrong?



SELECT * FROM all_dependencies WHERE  referenced_name = 'PRODUCT.PROD_ID' AND type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TYPE','TRIGGERS');
0
Comment
Question by:royjayd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1020 total points
ID: 37837115
I don't think you can get the 'column' but you can at least find the trigger then look at the trigger code:

SELECT * FROM all_dependencies WHERE  referenced_name = 'PRODUCT' AND type IN ('TRIGGER');
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 80 total points
ID: 37837254
The all_dependencies view in Oracle does not track dependencies down to the column level, just to the table level.  So, you need to use a query more like what slightwv suggested, then review the PL\SQL code for the objects that query returns to see if they include references to the column you are interested in.
0
 

Author Comment

by:royjayd
ID: 37837478
>>SELECT * FROM all_dependencies WHERE  referenced_name = 'PRODUCT' AND type IN ('TRIGGER');


ok.

I assume i can use the same query
SELECT * FROM all_dependencies WHERE  referenced_name = 'PRODUCT' AND type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TYPE','TRIGGERS');

which will give me references in triggers and other places.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37837489
There is no 'TRIGGERS', change it to 'TRIGGER'.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 37838341
Perhaps instead of looking at the actual trigger code, just query: DBA_TRIGGER_COLS...
:p
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

730 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