Re: How to get the table infomation of a table is referencing in a trigger?
- From: "xiaoyan" <xiaoyezi.xyz@xxxxxxx>
- To: "Dennis Williams" <oracledba.williams@xxxxxxxxx>
- Date: Mon, 10 Jul 2006 23:31:53 +0800
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:
