Re: How to get the table infomation of a table is referencing in a trigger?

Xiaoyan,

Since I don't see where anyone has responded to your question, I'm going to assume that since I didn't clearly understand your question, maybe nobody else did. Here are the clarifying questions that come to mind.

   Here is what I think you are asking: "How can I get a list of the tables referenced in my triggers?". Obviously one way is to inspect the TRIGGER_BODY for table names. You should start by doing this manually, but if your schema has many triggers it would be nice to do that automatically. From your email, I assume you are pointing out that there are many ways that table names could be disguised in the TRIGGER_BODY.

   I don't have any magic solution for you, but if this is indeed your situation, perhaps someone else on this list has a script to handle most situations. The only method I would trust to find every table reference would be to manually analyze every TRIGGER_BODY statement, but if you have many triggers, a script could help find situations you might manually overlook. Typically my simple-minded approach would be to inspect a few TRIGGER_BODY statements, create a simple script to find the obvious tables, then review other triggers to find what my script is overlooking and add more conditions to my script. Or you could teach Perl to parse every SQL statement and then it would be simple. :-)

   Ultimately it comes down to the number of triggers your schema has and how important it is to find every reference.

   If anyone else has a better approach, let Xiaoyezi know.

Dennis Williams

--
http://www.freelists.org/webpage/oracle-l