Solved

check if column is created by trigger

Posted on 2012-04-11
5
399 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
5 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 255 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 20 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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

786 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