How to get the table infomation of a table is referencing in a trigger?
- From: "xiaoyan" <xiaoyezi.xyz@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 9 Jul 2006 12:07:09 +0800
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;
end;
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!
Other related posts:
