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

From: xiaoyan <xiaoyezi.xyz_at_163.com>
Date: Mon, 10 Jul 2006 23:31:53 +0800
Message-ID: <00c301c6a435$face3dc0$d88870ca@buaad538c81ca1>

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_at_163.com Cc: oracle-l_at_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

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

Received on Mon Jul 10 2006 - 10:31:53 CDT