view ddl for all triggers

[deb platt]

I'm working on a legacy web application which uses an Oracle8i database. I usually interact with the database using JDBC or by using a GUI client. At this point, I'm a n00b when it comes to sqlplus.

The database has 80+ triggers which I can view one at a time selecting a specific trigger with the GUI client, then indicating that I want the GUI to display the trigger's properties. However if I need to be able to search all the triggers to see if any impact a specific column of a specific table, how do I do this with (presumably with sqlplus?)

I'd also like to be able to dump as a text file all the create statements that defined the the triggers to begin with.

Any suggestions would be appreciated.

Deb

[Paul Campbell]

select *
from all_dba_objects
where object_name = "TRIGGER";

[Avi Abrami]

Paul,
I believe the WHERE clause in your query should be:

where OBJECT_TYPE = "TRIGGER"

Deb, the Oracle documentation will probably be helpful to you.
You can access it via:

http://www.oracle.com/technology

Good Luck,
Avi.

[Paul Campbell]

Originally posted by Avi Abrami:
Paul,
I believe the WHERE clause in your query should be:

where OBJECT_TYPE = "TRIGGER"

Deb, the Oracle documentation will probably be helpful to you.
You can access it via:

http://www.oracle.com/technology

Good Luck,
Avi.

Ahh... yes... i was in a hurry and turned my brain off for a moment.

[deb platt]

Thanks for your input. Here's what's happening now. I tried executing this sql query while using sqlplus:

 
select *
from all_dba_objects
where OBJECT_TYPE = "TRIGGER"   

No luck. I was told the table/view didn't exist. So I tried

 describe all_dba_objects   

Again I was informed that the table/view didn't exist. Perhaps this is a table that came into being for some version of Oracle following Oracle8.

I actually have a hardbound copy of Oracle8: The Complete Reference, and I have read Chapter 23 on Triggers, but this chapter doesn't tell you how to query for existing triggers. Chapter 32 is The Hitchhiker's Guide to the Oracle8 Data Dictionary. It tells me there is a table called USER_TRIGGERS. Using sqlplus, I can successfully do this query:

 
select trigger_name 
from user_triggers; 

I can also successfully do this query:

select trigger_body
from user_triggers;    

However when I try to see the body associated with a trigger name by querying:

select trigger_name, trigger_body
from user_triggers;

the trigger_name mysteriously disappears. sqlplus prints the heading, "TRIGGER_NAME" followed by no data, then the the heading "TRIGGER_BODY" followed by data for the trigger body, then the next "TRIGGER_NAME" without data, then the next "TRIGGER_BODY" followed by data, etc.

If I do this query:

 
select trigger_name, trigger_type, triggering_event
from user_triggers;   

All data is absent except for the triggering_event. If I do this query:

 select trigger_name, trigger_type, triggering_event, trigger_body
from user_triggers;     

All data is absent except for the trigger_body. I don't understand why sqlplus is not displaying data for all the columns for which I am querying.
[ November 25, 2008: Message edited by: deb platt ]

[deb platt]

Regarding the disappearing fields: my error. The data returned from my query wasn't being formatted the way that I was expecting it. To help me understand the results of my query, I labeled the query columns like this:

 
select 'name: ' || TRIGGER_NAME, 'event: ' || TRIGGERING_EVENT, 'when_clause: ' || WHEN_CLAUSE, TRIGGER_BODY from USER_TRIGGERS;   

I am now seeing the name, event, when_clause and body in the response to my query. However I just noticed that the trigger_body (data type: LONG) is being truncated. For instance if I use my GUI client to inspect a specific body, I get this:

 
  DECLARE
    old_total   NUMBER(12,2);
  BEGIN
    /*
     * set totals on order, if applicable
     */
    IF(   :-o	  ld.description != 'Reverse Commission' ) THEN
 
      old_total :=   :-o	  ld.total_net +   :-o	  ld.adjustment;
 
      UPDATE orders
         SET total_invoiced = total_invoiced - old_total,
             shipping_cost = shipping_cost -   :-o	  ld.ship_handling
       WHERE class = 'Purchase Order'
         AND order_id =   :-o	  ld.order_id;
 
    END IF;
 
  END;

However if I use sqlplus to query just for this same body, I get this:

DECLARE
    old_total   NUMBER(12,2);
  BEGIN
    /*
     * set totals on order,

After looking at a number of these trigger_bodies, it would appear that only a set number of characters are displayed. How do I get sqlplus to display all the data in this LONG column?
[ November 25, 2008: Message edited by: deb platt ]

[Paul Campbell]

I think the DESCRIPTION column in user_triggers is what you are actually looking for... longs can not be converted in their entirety to a VARCHAR2 because of the size disparity... you could instert it into a BLOB... but I think the description column is what you really are after.

sorry about the all_dba_objects... you probably don't have rights to it... you do probably have privileges to all_objects, all_tab_columns, etc. though.

[deb platt]

The trigger_body is a LONG, which I believe is a type of binary data. This prevents me from formulating a where clause like this:

where trigger_body like '%reconciled_qty%'

And I have been unable to list the CREATE statements that created the triggers via sqlplus, though I'd still really love to do so. However I did find an Oracle Tip of the Week which listed two PL/SQL procedures that facilitated searhing USER_TRIGGERS.TRIGGER_BODY for a text string. I've tried both of them, and they seem to work. So this at least helps me to know if a table or column that I'm accessing via JDBC is being modifed by a trigger and that's a big help.
[ November 25, 2008: Message edited by: deb platt ]

[deb platt]

I just caught Paul's post on using the DESCRIPTION column of USER_TRIGGERS. I just tried that, and unfortunately it doesn't have the information I need. for instance, here is a typical description:

  AFTER UPDATE OF status
  ON tasks
  FOR EACH ROW

It basically tells me what event causes the trigger to fire, but it doesn't say what happens when it does. I need to know whether it updates another table when it fires, and if so, what column. That information is in the TRIGGER_BODY, but not in the DESCRIPTION.

I also tried this:

 
select *
from all_objects
where OBJECT_TYPE = 'TRIGGER'

I do have access to the all_objects table, but this query doesn't provide me any information as to what tables this trigger modifies.

Nonetheless, I appreciated all responses.

[Paul Campbell]

Deb,

I apologize... I should of reread your post from the start... I have some time to think about this tomorrow afternoon.

If I'm understanding you correctly (this time), you want to see the trigger, its dependencies (not necessarily a table... there are other objects triggers can be dependent on), trigger type (On insert, on update, etc).

Is that basically correct?

Part of the issue is that you are basically wanting to convert a long datatype to a varchar2 datatype (you can't do it). So we need to come up with an alternate that will get enough information.

Paul
[ November 25, 2008: Message edited by: Paul Campbell ]

[Avi Abrami]

Deb,
I searched the Oracle documentation Web site and couldn't find the documentation for Oracle 8i.
However, I did find documentation for Oracle 9i Release 1
and I believe that regarding the data dictionary views, it should be the
same as for Oracle 8i.

http://download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90190/toc.htm

Note that trigger body code is also stored in view USER_SOURCE as well as
in USER_TRIGGERS.

For triggers, relevant data dictionary views are:

USER_SOURCE

USER_TRIGGER_COLS

USER_TRIGGERS

Good Luck,
Avi.

[Paul Campbell]

Try this:

SELECT b.name, a.referenced_type, a.referenced_name, b.line, b.text
  FROM user_source b
     , (Select *
        FROM all_dependencies
        WHERE referenced_name != 'DUAL') a
 WHERE a.type = 'TRIGGER'
   AND a.referenced_type NOT IN ('NON-EXISTENT','PACKAGE')
-- AND a.referenced_type IN ('TABLE','SEQUENCE') 
   AND a.name = b.name
ORDER BY a.referenced_type,b.name, b.type, b.line

Let me know what it is missing or needs... each line of text will equal a line with in the trigger source.
[ November 26, 2008: Message edited by: Paul Campbell ]

[deb platt]

Thanks to both of you for your additional input. Via sqlplus, when I execute

  describe user_source;  

I get the anticipated output. However when I execute:

  select * from user_source;  

zero rows are returned;

Here's my motivation for learning more about triggers. I am a Java programmer who was hired to maintain a many-years old web-based application with an Oracle8i backend. A typical work scenario might be a sales person reporting that under certain conditions, when they delete an ordered item, the total item count for the order does not get updated. Since the web app uses JDBC, I first try searching the Java source code to find the relevant embeded sql. Except many times there isn't any. So speaking more generally, I can conclude that TABLE.COLUMN is being changed by a trigger, but I don't know which trigger and/or event is causing the update. Prior to finding the Oracle8i tip of the week, I had to look at various triggers, one at a time, via my GUI client. I picked which trigger to look at by guessing from the trigger name that this or that one might be relevant. And after I found one that did update TABLE.COLUMN, I didn't know if I could stop looking or whether there might be another trigger that also impacts TABLE.COLUMN. However since I am connecting to the database with the same user name whether I am using the GUI client or sqlplus, I would think the same data should be available if I only knew how to formulate the sqlplus query. But maybe the GUI client is using the USER_TRIGGERS table, and it's just doing all the work required to convert the LONG data into text.

As an aside, I inherited a very large piece of poster paper with an incomplete entity relationship diagram sketched in pencil. So the documentation on the database isn't as complete as I would have liked.

[Paul Campbell]

Originally posted by deb platt:
Thanks to both of you for your additional input. Via sqlplus, when I execute

  describe user_source;  

I get the anticipated output. However when I execute:

  select * from user_source;  

zero rows are returned;

Here's my motivation for learning more about triggers. I am a Java programmer who was hired to maintain a many-years old web-based application with an Oracle8i backend. A typical work scenario might be a sales person reporting that under certain conditions, when they delete an ordered item, the total item count for the order does not get updated. Since the web app uses JDBC, I first try searching the Java source code to find the relevant embeded sql. Except many times there isn't any. So speaking more generally, I can conclude that TABLE.COLUMN is being changed by a trigger, but I don't know which trigger and/or event is causing the update. Prior to finding the Oracle8i tip of the week, I had to look at various triggers, one at a time, via my GUI client. I picked which trigger to look at by guessing from the trigger name that this or that one might be relevant. And after I found one that did update TABLE.COLUMN, I didn't know if I could stop looking or whether there might be another trigger that also impacts TABLE.COLUMN. However since I am connecting to the database with the same user name whether I am using the GUI client or sqlplus, I would think the same data should be available if I only knew how to formulate the sqlplus query. But maybe the GUI client is using the USER_TRIGGERS table, and it's just doing all the work required to convert the LONG data into text.

As an aside, I inherited a very large piece of poster paper with an incomplete entity relationship diagram sketched in pencil. So the documentation on the database isn't as complete as I would have liked.

Deb,

User_source, user_triggers (basically user anything) only shows information for the current user... not triggers created by other users. You need to be logged in with the web app user (likely the schema owner) to see what you're looking for in that manner (and something that is likely a security issue for your DBA in granting you that schema information).

The all_dependencies view shows you information about oracle objects and the objects they are dependent on... it will take you to the table level... not the table.column level.

The detail level you're looking for requires access to the dba views... dba_objects, etc.

You could try something like this.

http://www.oracle.com/technology/oramag/code/tips2004/091304.html

select lvl, u.object_id, u.object_type, LPAD(' ',lvl)||object_name obj
 
FROM
 
(
 
SELECT level lvl, object_id
 
FROM SYS.public_dependency s
 
START WITH s.object_id = (select object_id 
 
from user_objects 
 
where object_name=UPPER('&OBJECT_NAME')
 
and object_type =UPPER( '&OBJECT_TYPE' )
 
)
 
CONNECT BY s.object_id = PRIOR referenced_object_id
 
GROUP BY level, object_id
 
)tree,
 
user_objects u
 
WHERE
 
tree.object_id = u.object_id
 
ORDER BY lvl 
 
/

[Shailesh Chandra]

This one query will give you DDLs of all triggers, I have tested in on 10g, it should work for 8i also

SELECT TO_CHAR(
DBMS_METADATA.GET_DDL('TRIGGER', TRIGGER_NAME, '<<PUT SCHEMA NAME>>')
)
FROM ALL_TRIGGERS WHERE OWNER = '<<PUT OWNER>>';

Shailesh

[deb platt]

I tried pursuing Shailesh's suggestion without luck. Further google-ing around led me to this information at oracle-base.com:

Prior to Oracle9i metadata could only be extracted using SQL statements, export utilities and the OCIDescribeAny interface, all of which are limited...To solve these issues Oracle9i has introduced the DBMS_METADATA package which can be used to retrieve object definitions as XML or SQL DDL

The above statement leads me to believe that unfortunately Oracle8i does not have the DBMS_METADATA utility package.

Nonetheless, thanks for your input.

[deb platt]

I just tried Paul's 11/29 suggestion. While I have concealed company-specific data, here's the outcome for one such query, which I've reformatted:

LVL: 1
OBJECT_ID: a_specific_number
OBJECT_TYPE: TRIGGER
OBJ: a_specific_trigger_name

Unfortunately, this doesn't provide me with specific information regarding the trigger's body.

As an aside, in every case where I connect with the database, whether I'm using JDBC, sqlplus, or an database GUI client, I always connect with the application's username and password since the application is the owner of the corresponding schema.

[Paul Campbell]

Originally posted by deb platt:
I tried pursuing Shailesh's suggestion without luck. Further google-ing around led me to this information at oracle-base.com:


The above statement leads me to believe that unfortunately Oracle8i does not have the DBMS_METADATA utility package.

Nonetheless, thanks for your input.

The other issue is that it would truncate any Longs that were greater than the limit for a char... I think most of the issue is as you surmise... you are on a very old version of Oracle (one that is no longer supported).

I believe you have to work with the 80/20 rule... something that gets you to 80% of what you need because trying to solve the last 20% is going to cost you more time and effort than what you will receive in return.