Re: How to get the table infomation of a table ...

Dennis Williams:

Thank you for your enthusiastic reply!And I am sorry for my poor english and unclear expresion.

In our project,we must create trigger through dynamical sql to becase we do not know the table name and its schema in advance,so the trigger has been created by dynamical sql,the question post now has been resolved through a global variable,and now the procedure is like:

CREATE OR REPLACE TRIGGER my_trigger
      BEFORE INSERT ON test.courses
      FOR EACH ROW

DECLARE
        ......

        ......
         ddlstr:='
            declare
                myinfo varchar2(80);
             begin
                    select DESCRIPTIONl into variables_pkg.myinfo from '|| fathertable||'where '|| f_columnname||'=:new.'||c_columnname||';
               end;';
       execute immediate ddlstr;
end;

but now a new problem has risen up,suppose in one of my tests varaibal information is like :

c_columnname='courses_score';

:new.courses_score=10;

fathertable=scores;

f_columnname=score;

but when executing dynamically the following statement

select DESCRIPTIONl into variables_pkg.myinfo from '|| fathertable||'where '|| f_columnname||'=:new.'||c_columnname||';

we will get

select DESCRIPTIONl into variables_pkg.myinfo from scores where score=:new.courses_score;

not what i have expected:

select DESCRIPTIONl into variables_pkg.myinfo from scores where score=10;

Then would you please tell me how to construct the dynamical sql to get the statement'select DESCRIPTIONl into variables_pkg.myinfo from scores where score=10;'?

Any idea?

Thank you in advance!

Best Regards

----- Original Message -----

From: Dennis Williams

To: xiaoyezi.xyz@163.com

Cc: oracle-l@freelists.org

Sent: Monday, July 10, 2006 11:09 PM

Subject: 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

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

小燕

2006-07-10