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

How to get the table infomation of a table is referencing in a trigger through a programme? for example:

conn test/test

SQL> desc courses;

Name                 Type           Nullable Default Comments 


-------------------- -------------- -------- ------- -------- 


COURSES_ID           NUMBER         Y                         
COURSES_INFO         VARCHAR2(1024) Y       
COURSES_SCORE        NUMBER         Y     

SQL> desc scores;
Name                 Type         Nullable Default Comments 


-------------------- ------------ -------- ------- -------- 


SCORE                NUMBER                                 
DESCRIPTION          VARCHAR2(20) Y   

and the foreign key column of the tabel 'courses' is COURSES_SCORE ,the referencing table is 'scores',and the primary key column of table'scores' is SCORE

now in trigger:

CREATE OR REPLACE TRIGGER my_trigger

      BEFORE INSERT  ON  test.courses
      FOR EACH  ROW
      
DECLARE      
         rcname VARCHAR2(80);
         fathertable VARCHAR2(80);
         ownername VARCHAR2(80);
         f_columnname VARCHAR2(80);
         c_columnname VARCHAR2(80);
         myinfo VARCHAR2(80);
         ddlstr varchar2(500);
      BEGIN         
          SELECT R_CONSTRAINT_NAME INTO rcname FROM All_Constraints 
            WHERE owner='TEST'AND table_name='COURSES';
         
         SELECT table_name INTO fathertable FROM All_Constraints 
            WHERE CONSTRAINT_NAME=upper(rcname);
         
         SELECT owner INTO ownername FROM All_Constraints 
            WHERE CONSTRAINT_NAME=upper(rcname);
         
         SELECT COLUMN_NAME   INTO c_columnname FROM All_Cons_Columns 
            WHERE  table_name=upper('courses') AND owner='TEST';
         SELECT COLUMN_NAME   INTO f_columnname FROM All_Cons_Columns 
            WHERE  table_name=upper(fathertable) AND owner=upper(ownername);         
          
         ddlstr:=' 
            declare
                myinfo varchar2(80);
             begin
                    select DESCRIPTIONl into myinfo  from '|| fathertable||'where '|| f_columnname||'=new.'||c_columnname||';
               end;';  
       execute immediate ddlstr;       

but the 'myinfo' is not useful in trigger my_trigger because it is a local variable,but now i want to get the value of 'myinfo', how can I? Thank you in advance!

--

http://www.freelists.org/webpage/oracle-l Received on Sat Jul 08 2006 - 23:07:09 CDT