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

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@xxxxxxx 
  Cc: oracle-l@xxxxxxxxxxxxx 
  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

Other related posts: