By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Help with like predicate in update trigger

P: n/a

The initial row is inserted with the colPartNum column containing a valid
LIKE pattern, such as (without the single quotes) 'AB%DE'.
I want to update the column value with the results of a query against a
different table (that uses the LIKE predicate) but cannot get around the
SQL0132 error .
I have tried the hex notation after the LIKE such as (without the
quotes)...
" where colNewPartNum like ( X'27' || nnn.colPartNum || X'27) " , but
still get this same error....

See
http://groups.google.com/groups?q=sq...hoo.com&rnum=4
and
http://forums.devshed.com/archive/t-169709 for similar issues ... but no
answers that I can find on google or the newsgroups...

Anyone know the syntax trick to getting this to work?
Thanks...

--#SET DELIMITER !
drop trigger Test1!

CREATE TRIGGER Test1
AFTER INSERT ON USER1.ORDERS
REFERENCING NEW AS NNN
FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC
UPDATE ORDERS
SET colPartNum = (select colNewPartNum from tblMasterParts
where colNewPartNum like (nnn.colPartNum))
where colPartNum = nnn.colPartNum ;
END!
COMMIT!
Nov 12 '05 #1
Share this Question
Share on Google+
18 Replies


P: n/a
That's a bit of a trap. LIKE only allows for constant patterns.
Thsi excludes local or trigger transition variables in side of inline
SQL PL (as used in triggers).
Teh migration tool kit (MTK) provides general LIKE functions which you
can download for free if that's a major issue for you.

Cheers
Serge
Nov 12 '05 #2

P: n/a
"Bill Smith" <x@x.com> wrote in message news:<Xe*****************@fe2.columbus.rr.com>...
The initial row is inserted with the colPartNum column containing a valid
LIKE pattern, such as (without the single quotes) 'AB%DE'.
I want to update the column value with the results of a query against a
different table (that uses the LIKE predicate) but cannot get around the
SQL0132 error .

DB2 UDB SQL Reference Volume 1 -> Chapter 2. Language elements ->
Predicates -> LIKE predicate:
pattern-expression
An expression that specifies the string that is to be matched.
The expression can be specified by:
v A constant
v A special register
v A host variable
v A scalar function whose operands are any of the above
v An expression concatenating any of the above

So, you can't use a column for pattern-expression.
Nov 12 '05 #3

P: n/a
Yes, This functionality is a major issue for me. I need to find some way to
update the column value from the pattern to a result that matches the
pattern. Are you saying I can get the functionality I want by calling an
external function that resides in the MTK?
Bill
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:2s*************@uni-berlin.de...
That's a bit of a trap. LIKE only allows for constant patterns.
Thsi excludes local or trigger transition variables in side of inline SQL
PL (as used in triggers).
Teh migration tool kit (MTK) provides general LIKE functions which you can
download for free if that's a major issue for you.

Cheers
Serge

Nov 12 '05 #4

P: n/a
Bill Smith wrote:
Yes, This functionality is a major issue for me. I need to find some way to
update the column value from the pattern to a result that matches the
pattern. Are you saying I can get the functionality I want by calling an
external function that resides in the MTK?
Bill

Correct. Just download the MTK and go shopping.

There are a bunch of interesting Sybase/SQL Server and Oracle functions
you can snicker.

Cheers
Serge
Nov 12 '05 #5

P: n/a
Serge Rielau wrote:
Bill Smith wrote:
Yes, This functionality is a major issue for me. I need to find some way
to update the column value from the pattern to a result that matches the
pattern. Are you saying I can get the functionality I want by calling an
external function that resides in the MTK?
Bill

Correct. Just download the MTK and go shopping.

There are a bunch of interesting Sybase/SQL Server and Oracle functions
you can snicker.


I don't know if the MTK provides a regular expression matching function. If
it doesn't and if you need regexp matching, then you can build your own
function:
http://www-106.ibm.com/developerwork...301stolze.html

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #6

P: n/a
Thanks but I didn't really want to have to code an external function to get
the basic LIKE functionality. I don't need the regular expression (but I
bookmarked the URL for a future project I have in mind).
Do you know if a SQL language UDF can perform the functionality? Ie Can I
create an SQL language UDF that accepts the pattern as a varchar, the UDF
uses the varchar on the right side of the LIKE predicate for a table lookup,
and return the single column, single row result of the lookup to the trigger
for use in the update statement?

I didn't think it would be so hard to just have the pattern stored in the
database as opposed to being hardcoded in the statement.
Bill

"Knut Stolze" <st****@de.ibm.com> wrote in message
news:ck**********@fsuj29.rz.uni-jena.de...
Serge Rielau wrote:
Bill Smith wrote:
Yes, This functionality is a major issue for me. I need to find some way
to update the column value from the pattern to a result that matches the
pattern. Are you saying I can get the functionality I want by calling an
external function that resides in the MTK?
Bill

Correct. Just download the MTK and go shopping.

There are a bunch of interesting Sybase/SQL Server and Oracle functions
you can snicker.


I don't know if the MTK provides a regular expression matching function.
If
it doesn't and if you need regexp matching, then you can build your own
function:
http://www-106.ibm.com/developerwork...301stolze.html

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Nov 12 '05 #7

P: n/a
Do you know of any solution that does not require the use of an external
function? (an SQL language UDF?, combo of SQL UDF and stored proc?,etc.)

To summarize the issue again ...
When a row is inserted into a table containing a column value 'Jo_es' , I
want to change it to 'Jones' based on a simple query against a tblNames
table using sql similar to
select name from tblNames where name like 'Jo_es'
It's just that 'Jo_es' is not hard coded but comes from the result of a
query ..
Thanks,
Bill
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:2s*************@uni-berlin.de...
Bill Smith wrote:
Yes, This functionality is a major issue for me. I need to find some way
to update the column value from the pattern to a result that matches the
pattern. Are you saying I can get the functionality I want by calling an
external function that resides in the MTK?
Bill

Correct. Just download the MTK and go shopping.

There are a bunch of interesting Sybase/SQL Server and Oracle functions
you can snicker.

Cheers
Serge

Nov 12 '05 #8

P: n/a
Bill Smith wrote:
Do you know of any solution that does not require the use of an external
function? (an SQL language UDF?, combo of SQL UDF and stored proc?,etc.)


You could of course use the built-in functions like LOCATE and other string
functions to do the pattern matching. And the result of that can be
wrapped into a UDF.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #9

P: n/a
The trick to doing this is to use a suitable UDB facility. Forget about
the trigger because, as Serge initially commented, row variables AND
LOCALLY DEFINED STRING VARIABLES are not usable in a LIKE clause.

UDB does, however, have a mechanism that can do this.

Instead of inserting the row from your application then updating it in a
trigger, use a stored procedure to do everything. Pass all of the column
values to the stored procedure for the insert. You can use the passed
string to locate the "new" data value (using LIKE - UDB 8.1 FP 7),
insert your logging record and, as a performance bonus, do a single
insert of the data row avoiding two logging actions.

If your column data values do not include the wildcard characters used
by LIKE, a "before" trigger can be used to raise an error if the
wildcard characters appear in the column data. This will prevent an
insert without using the stored procedure.

Phil Sherman
Bill Smith wrote:
Do you know of any solution that does not require the use of an external
function? (an SQL language UDF?, combo of SQL UDF and stored proc?,etc.)

To summarize the issue again ...
When a row is inserted into a table containing a column value 'Jo_es' , I
want to change it to 'Jones' based on a simple query against a tblNames
table using sql similar to
select name from tblNames where name like 'Jo_es'
It's just that 'Jo_es' is not hard coded but comes from the result of a
query ..
Thanks,
Bill
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:2s*************@uni-berlin.de...
Bill Smith wrote:

Yes, This functionality is a major issue for me. I need to find some way
to update the column value from the pattern to a result that matches the
pattern. Are you saying I can get the functionality I want by calling an
external function that resides in the MTK?
Bill


Correct. Just download the MTK and go shopping.

There are a bunch of interesting Sybase/SQL Server and Oracle functions
you can snicker.

Cheers
Serge



Nov 12 '05 #10

P: n/a
Thanks, our project team has declared they are not changing the way the are
loading the data <end of story>.. I like your approach, I guess I'll keep
trying to wear them down... So as an alternative ...Can I have a trigger
that passes the pattern to a Stored procedure and the stored procedure do
the update? I can trigger off of a different table if that matters...(ie on
update of status flag in status table, run trigger to pass pattern to stored
proc to do the update of the names table).... (db2,fp7a, on xp)
Thanks for all the suggestions.
"Philip Sherman" <ps******@ameritech.net> wrote in message
news:Yf*****************@newssvr19.news.prodigy.co m...
The trick to doing this is to use a suitable UDB facility. Forget about
the trigger because, as Serge initially commented, row variables AND
LOCALLY DEFINED STRING VARIABLES are not usable in a LIKE clause.

UDB does, however, have a mechanism that can do this.

Instead of inserting the row from your application then updating it in a
trigger, use a stored procedure to do everything. Pass all of the column
values to the stored procedure for the insert. You can use the passed
string to locate the "new" data value (using LIKE - UDB 8.1 FP 7), insert
your logging record and, as a performance bonus, do a single insert of the
data row avoiding two logging actions.

If your column data values do not include the wildcard characters used by
LIKE, a "before" trigger can be used to raise an error if the wildcard
characters appear in the column data. This will prevent an insert without
using the stored procedure.

Phil Sherman
Bill Smith wrote:
Do you know of any solution that does not require the use of an external
function? (an SQL language UDF?, combo of SQL UDF and stored proc?,etc.)

To summarize the issue again ...
When a row is inserted into a table containing a column value 'Jo_es' ,
I want to change it to 'Jones' based on a simple query against a tblNames
table using sql similar to
select name from tblNames where name like 'Jo_es'
It's just that 'Jo_es' is not hard coded but comes from the result of a
query ..
Thanks,
Bill
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:2s*************@uni-berlin.de...
Bill Smith wrote:
Yes, This functionality is a major issue for me. I need to find some way
to update the column value from the pattern to a result that matches the
pattern. Are you saying I can get the functionality I want by calling an
external function that resides in the MTK?
Bill

Correct. Just download the MTK and go shopping.

There are a bunch of interesting Sybase/SQL Server and Oracle functions
you can snicker.

Cheers
Serge


Nov 12 '05 #11

P: n/a
Why don't you set up a couple of little tables (2-3 columns, 1-3 rows)
and try doing this? It should tke less time than waiting for a forum
response. This is one of the great uses of UDB personal edition.

Note that triggers can invoke stored procedures at 8.2 or 8.1 with FP7.

Phil Sherman
Bill Smith wrote:
Thanks, our project team has declared they are not changing the way the are
loading the data <end of story>.. I like your approach, I guess I'll keep
trying to wear them down... So as an alternative ...Can I have a trigger
that passes the pattern to a Stored procedure and the stored procedure do
the update? I can trigger off of a different table if that matters...(ie on
update of status flag in status table, run trigger to pass pattern to stored
proc to do the update of the names table).... (db2,fp7a, on xp)
Thanks for all the suggestions.
"Philip Sherman" <ps******@ameritech.net> wrote in message
news:Yf*****************@newssvr19.news.prodigy.co m...


Nov 12 '05 #12

P: n/a
I got past the LIKE problem using the stored procedure and ran into a -746 SQLSTATE 57053...Listed below is the trigger and stored proc... Is think the error is from the FOR loop selecting rows, and then trying to do the UPDATE in the SP on the same table being processed in the loop. Do you have any suggestions to get around this? I tried setting up a first FOR loop to insert the values to another table (worked fine) , followed by a second FOR loop to loop through the rows of the second table and call the sp...but still got the 746 error...I thought the END FOR of the first loop might release the employee table for updates, but it did not. If I comment out the UPDATE in the sp, my history table does show alternating rows of pattern, matched value like I expect.
Thanks, Bill

--#SET DELIMITER !

drop trigger Test1!
drop procedure sp1!

Create procedure sp1(IN pattern VARCHAR(15))
Begin
insert into history values (pattern);
insert into history values (select colName from masternames
where colName like pattern);

UPDATE EMPLOYEE
SET LASTNAME = (select colName from masternames
where colName like pattern)
where lastname = pattern ;
end!

CREATE TRIGGER Test1
AFTER UPDATE OF loaded ON USER1.STATUS
REFERENCING NEW AS NNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
FOR getnames AS
--SELECT TRANSLATE(lastname,'_','*') AS #n
SELECT lastname AS #n
FROM employee
--where midinit = nnn.loaded
DO
call sp1(#n);
END FOR;
END!

COMMIT!

===========
SQL0746N Routine routine-name (specific name specific-name ) violated nested SQL statement rules when attempting to perform operation operation on table table-name .
Explanation:
The routine routine-name (specific name specific-name ) attempted to perform the operation operation on table table-name . This operation conflicts with other uses of the table by either the application, or a routine invoked directly or indirectly from that application.

If the operation is "READ", then the table table-name is currently being written to by either the application or another routine.

===> If the operation is "MODIFY", then the table table-name is already being read from or written to by the application or another routine. <====

If table-name references an explain table and the statement that receives this error message is either a PREPARE statement or an EXECUTE IMMEDIATE statement, then a conflict occurred when inserting explain information into the explain table.

User Response:
The operation may be successful if retried. Redesign either the application or the routine to avoid the conflict.

If the conflict occurred when inserting explain information for a dynamic statement, then disable explain for dynamic statements and try the PREPARE statement or EXECUTE IMMEDIATE statement again.

sqlcode : -746

sqlstate : 57053


"Philip Sherman" <ps******@ameritech.net> wrote in message news:VW*****************@newssvr19.news.prodigy.co m...
Why don't you set up a couple of little tables (2-3 columns, 1-3 rows)
and try doing this? It should tke less time than waiting for a forum
response. This is one of the great uses of UDB personal edition.

Note that triggers can invoke stored procedures at 8.2 or 8.1 with FP7.

Phil Sherman


Bill Smith wrote:
Thanks, our project team has declared they are not changing the way the are
loading the data <end of story>.. I like your approach, I guess I'll keep
trying to wear them down... So as an alternative ...Can I have a trigger
that passes the pattern to a Stored procedure and the stored procedure do
the update? I can trigger off of a different table if that matters...(ie on
update of status flag in status table, run trigger to pass pattern to stored
proc to do the update of the names table).... (db2,fp7a, on xp)
Thanks for all the suggestions.
"Philip Sherman" <ps******@ameritech.net> wrote in message
news:Yf*****************@newssvr19.news.prodigy.co m...

Nov 12 '05 #13

P: n/a
Bill,

This is odd. Are you sure you don't access history outside the stored
procedure? Can you provide a full exampel to repro (including the tabel
DDL)?

Cheers
Serge
Nov 12 '05 #14

P: n/a
Bill Smith wrote:
I got past the LIKE problem using the stored procedure and ran into a
-746 SQLSTATE 57053...Listed below is the trigger and stored proc... Is
think the error is from the FOR loop selecting rows, and then trying to
do the UPDATE in the SP on the same table being processed in the loop.
Do you have any suggestions to get around this? I tried setting up a
first FOR loop to insert the values to another table (worked fine) ,
followed by a second FOR loop to loop through the rows of the second
table and call the sp...but still got the 746 error...I thought the END
FOR of the first loop might release the employee table for updates, but
it did not. If I comment out the UPDATE in the sp, my history table does
show alternating rows of pattern, matched value like I expect.
Thanks, Bill


The following script demonstrates how to do most of what you want. This
runs without problems on UDB (Linux) 8.1 FP7. Unfortunately, it can't
log the changes (not allowed in 'before insert' trigger) so it validates
that the name translation works properly. (Better to validate than to
log changes & track down problems later.) All table definitions, data
loads and tests, including error conditions are included. Run it with
the command:
db2 -td! -vf your_file_name

Note the use of "rtrim" in fixname. It's mandatory because the passed
string is padded to match the column definition of name in t1. Without
the rtrim; the LIKE clause will never match because it contains trailing
blanks.

Phil Sherman

drop table t1!
drop table t2!
create table t1 (id int not null, name char(10) not null)!
create table t2 (name varchar(10) not null)!
insert into t2 values ('Sherman'),('Shermen')!

drop trigger Test1!
drop procedure fixName!

create procedure fixName (inout sstr varchar(10)
,inout matchrows int)
dynamic result sets 0 reads sql data language sql
begin
set sstr = rtrim(sstr);
set matchrows = (select count(*) from t2 where name like sstr);
if (matchrows = 1) then
set sstr = (select name from t2 where name like sstr);
end if;
end!

CREATE trigger Test1
no cascade before insert on t1
referencing new as nnn
for each row mode db2sql
BEGIN atomic
declare count1 int;
declare s1 varchar(10);
call fixName(nnn.name,count1);
if (count1 = 0)
then
signal sqlstate '99001' set message_text =
'No matching name found';
elseif (count1 > 1)
then
signal sqlstate '99001' set message_text =
'Multiple matching names found';
end if;
END!

insert into t1 values(1,'Sh%an')!
insert into t1 values(2,'%en')!
select * from t1!
insert into t1 values(3,'Sh%n')!
insert into t1 values(4,'Pe%h')!

Nov 12 '05 #15

P: n/a
I can't duplicate the previous error. This test script works (the pattern
comes from a table other than the tblEmployee table).. However, If I change
the trigger to get the pattern from the tblEmployee table I get a -746
error, which makes some sense to me. Any suggestions on how to get the
pattern from the tblEmployee ?
Bill

--#SET DELIMITER !

drop trigger Test1!
drop procedure sp1!
drop table tblTrigger!
drop table tblEmployee!
drop table masternames!
drop table tblMasterNames!
drop table tblHistory!
create table tblHistory
( c1 VARCHAR(15),
c2 VARCHAR(15))
!

create table tblTrigger
( Pattern VARCHAR(15))
!

CREATE TABLE tblEmployee
(LastName VARCHAR(15))
!

CREATE TABLE tblMasterNames
( LastName VARCHAR(15))
!

INSERT INTO tblTrigger values ('zzz')
!

INSERT INTO tblMasterNames values ('Jones')!
INSERT INTO tblMasterNames values ('Johnson')!
INSERT INTO tblMasterNames values ('Smith')!

INSERT INTO tblEmployee values ('Jozes')!
INSERT INTO tblEmployee values ('Smitz')!

Create procedure sp1(IN inpattern VARCHAR(15))
Language SQL
Begin
insert into tblHistory values (inpattern,'XXX');
insert into tblHistory values (inpattern,
(select LastName from tblMasterNames
where LastName like inpattern));

UPDATE tblEmployee
SET LASTNAME = (select LastName from tblmasternames
where LastName like inpattern)
where lastname like inpattern;
end!

CREATE TRIGGER Test1
AFTER UPDATE OF Pattern ON USER1.tblTrigger
REFERENCING NEW AS NNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
FOR getnames AS
SELECT Pattern AS p
FROM tblTrigger
-- select lastname as p from tblEmployee

DO
call sp1(p);
END FOR;
END!
COMMIT!
--verify only one row returned for pattern and test it
--select LastName from tblEmployee where lastname like 'Jo_es'!
--select LastName from tblMasterNames where lastname like 'Jo_es'!
update tblTrigger set Pattern = 'Jo_es' !
update tblTrigger set Pattern = '_mit_' !
select * from tblMasterNames !
select * from tblHistory !
select * from tblEmployee !

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:2s*************@uni-berlin.de...
Bill,

This is odd. Are you sure you don't access history outside the stored
procedure? Can you provide a full exampel to repro (including the tabel
DDL)?

Cheers
Serge

Nov 12 '05 #16

P: n/a
Can you pass the pattern in as an argument to the procedure?

Cheers
Serge
Nov 12 '05 #17

P: n/a
THANKS Phil,
It's looks so easy when its working...I wanted to make the fixName procedure
be more generic so that multiple triggers could call it by passing in a
table name as shown below, but got an error... Is there a way to have the
table name passed in...That way I could have multiple triggers for each
column I want to accept patterns in while having only the one generic
fixName (or fixFieldValue) proc.
Bill
create procedure fixName (in tableName varchar (100),inout sstr
varchar(10)
,inout matchrows int)
set matchrows = (select count(*) from tableName where name like sstr);
"Philip Sherman" <ps******@ameritech.net> wrote in message
news:LG*****************@newssvr19.news.prodigy.co m... Bill Smith wrote:
I got past the LIKE problem using the stored procedure and ran into
a -746 SQLSTATE 57053...Listed below is the trigger and stored proc... Is
think the error is from the FOR loop selecting rows, and then trying to
do the UPDATE in the SP on the same table being processed in the loop.
Do you have any suggestions to get around this? I tried setting up a
first FOR loop to insert the values to another table (worked fine) ,
followed by a second FOR loop to loop through the rows of the second
table and call the sp...but still got the 746 error...I thought the END
FOR of the first loop might release the employee table for updates, but
it did not. If I comment out the UPDATE in the sp, my history table does
show alternating rows of pattern, matched value like I expect.
Thanks, Bill


The following script demonstrates how to do most of what you want. This
runs without problems on UDB (Linux) 8.1 FP7. Unfortunately, it can't log
the changes (not allowed in 'before insert' trigger) so it validates that
the name translation works properly. (Better to validate than to log
changes & track down problems later.) All table definitions, data loads
and tests, including error conditions are included. Run it with the
command:
db2 -td! -vf your_file_name

Note the use of "rtrim" in fixname. It's mandatory because the passed
string is padded to match the column definition of name in t1. Without the
rtrim; the LIKE clause will never match because it contains trailing
blanks.

Phil Sherman

drop table t1!
drop table t2!
create table t1 (id int not null, name char(10) not null)!
create table t2 (name varchar(10) not null)!
insert into t2 values ('Sherman'),('Shermen')!

drop trigger Test1!
drop procedure fixName!

create procedure fixName (inout sstr varchar(10)
,inout matchrows int)
dynamic result sets 0 reads sql data language sql
begin
set sstr = rtrim(sstr);
set matchrows = (select count(*) from t2 where name like sstr);
if (matchrows = 1) then
set sstr = (select name from t2 where name like sstr);
end if;
end!

CREATE trigger Test1
no cascade before insert on t1
referencing new as nnn
for each row mode db2sql
BEGIN atomic
declare count1 int;
declare s1 varchar(10);
call fixName(nnn.name,count1);
if (count1 = 0)
then
signal sqlstate '99001' set message_text =
'No matching name found';
elseif (count1 > 1)
then
signal sqlstate '99001' set message_text =
'Multiple matching names found';
end if;
END!

insert into t1 values(1,'Sh%an')!
insert into t1 values(2,'%en')!
select * from t1!
insert into t1 values(3,'Sh%n')!
insert into t1 values(4,'Pe%h')!

Nov 12 '05 #18

P: n/a
You can use dynamic SQL in stored procedures. This would let you
construct the SQL statement to access the desired table. There are,
however, two important consequences:

1. Dynamic SQL will not perform nearly as well as the static in my
example. Your "multiple triggers can call it" statement is an indication
that it may be getting a lot of use which may be a performance issue.
Dynamic SQL will require a minimum of four interactions with the
database to retrieve the data. You won't be able to use a parameterized
SQL statement because you want to change the table name. This will force
optimization every time the statement is executed - another hinderance
to good performance. (If you compress all of your translation tables
into a single table; you can use a parameterized statement - but that
has its own issues.)

2. Passing in the table name raises philosophical design questions.
Passing the table name certainly cuts down on coding but raises
questions concerning comingling of procedure across tables. I think
that's especially true in this environment.

It'll be interesting to see what happens if this code is used in
production. It doesn't take a very large domain of values to have a
large frequency of multiple matching rows when using a LIKE predicate.
Additionally, a new value can't be added in the target table without
first adding it to the lookup table or designing the application to
allow an insert when there are no '_%' characters in the column. If you
are going to use this search technique for lookups, then a successful
new row insert will also require an insert to the lookup table. All of
this is probably "application changes" which you have indicated won't be
allowed. Don't forget that LIKE predicates can start with a '%' which
will guarantee some form of scan, probably the table.

Phil Sherman
Bill Smith wrote:
THANKS Phil,
It's looks so easy when its working...I wanted to make the fixName procedure
be more generic so that multiple triggers could call it by passing in a
table name as shown below, but got an error... Is there a way to have the
table name passed in...That way I could have multiple triggers for each
column I want to accept patterns in while having only the one generic
fixName (or fixFieldValue) proc.
Bill
create procedure fixName (in tableName varchar (100),inout sstr
varchar(10)
,inout matchrows int)
set matchrows = (select count(*) from tableName where name like sstr);



Nov 12 '05 #19

This discussion thread is closed

Replies have been disabled for this discussion.