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!
