Perhaps my thinking is wrong but this is what I have:
1 table (Tab1) with 1 attribute (Attr1)
Attr1 char(16) for bit data
-----------------------------------------------
create trigger check
no cascade before insert on Tab1
referencing new as N
for each row mode DB2SQL
WHEN length(N.Attr1)>16 set N.Attr1=x'1234567890123456'
-----------------------------------------------
So when an insert statment from our crappy application tries to pass a
bad insert such as
Insert into tab1 values ('1234567890123456')
that would normally be too long because it's not prefixed with the x
which is why I want the trigger to intercept, but it doesn't
with the trigger created it just gives the same error as without the trigger
SQL0433N value '1234567890123456' is too long
If I say
create trigger check
no cascade before insert on Tab1
referencing new as N
for each row mode DB2SQL
set N.Attr1=x'1234567890123456'
It intercepts and works fine.
I've tried several other methods to rectify the situation, like
-----------------------------------------------
create trigger check
no cascade before insert on Tab1
referencing new as N
for each row mode DB2SQL
BEGIN ATOMIC
DECLARE TMPATTR integer;
set TMPATTR=length(N.Attr1);
if TMPATTR>16 then
set N.Attr1=x'1234567890123456';
end if;
END
-----------------------------------------------
But same story, SQL0433N value '1234567890123456' too long.
As an interesting test I ran this one:
-----------------------------------------------
create trigger check
no cascade before insert on Tab1
referencing new as N
for each row mode DB2SQL
BEGIN ATOMIC
DECLARE TMPATTR integer;
set TMPATTR=length(N.Attr1);
set N.Attr1=x'1234567890123456';
END
-----------------------------------------------
And I don't get the error. So the length part works, it's when I try to
evaluate the thing with and if or when that it craps out.
The deal is with certain functions in our application it produces the
correct insert statment, but in other functions, it does not, and that's
where I had an idea to have a trigger to correct the situation until the
application gets fixes (many months away).
Where am I going wrong? My set statments above aren't really the ones I
want to use either, meaning I don't want to set to a fixed value, but
rather have this: set N.Attr1=cast(Attr1 as char16 for bit data) (I
think that will work?), so I retain the original string.
I thought the whole idea of a before insert trigger was to get to the
values before there were inserted into the table. I thought that meant
before they were checked against the table constraints, but that doesn't
seem to be the case, the second I try to use the original value in
anything it just tells me it's too long
Thanks for any help!
Kenneth J. Snyder