At 09:41 AM 14/04/2005 +0000, you wrote:

>It seems to me that SUBSTRING does not work in triggers on strings in
>UNICODE_FSS character set.
>
>I have a table TBL with 2 columns:
>STRL varchar(120) character set UNICODE_FSS ;
>STRS varchar( 20) character set UNICODE_FSS ;
>
>STRS serves for indexing. So I set up trigger before insert:
>
>NEW.STRS = SUBSTRING(NEW.STRL FROM 1 FOR 20);
>
>This work perfect for strings STRL that do not contain national
>characters. But STRL containing for example hungarian or czech
>characters causes conversion or overflow error and new record is not
>inserted.
>
>When I deactivate trigger and set up value for STRS manually (
>INSERT INTO TBL (STRL, STRS) VALUES(MyValue, SUBSTRING(MyValue FROM 1
>FOR 20) );
>) new record is inserted as I expect.
>
>----------------------------------------------
>Have you encounter such problem too?
>Am I doing something wrong?
>Any suggestions?

What you are reporting does seem like a bug. The NEW context variables
*should* be aware of the character set attributes of the database columns
they represent, but perhaps they are not.

Try this to test whether it works around the problem:

create trigger...
as
declare newstrl varchar(120) character set unicode_fss;
declare newstrs varchar(20) character set unicode_fss;
begin
if (new.strl is not null) then
begin
newstrl = cast (new.strl as varchar(120) character set unicode_fss);
newstrs = SUBSTRING(newstrl FROM 1 FOR 20);
new.strs = newstrs;
end
end

What database and version are you using?

./heLen