DB2 Before Insert Trigger not acting like before?

[73blazer]

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'12345 67890123456'
-----------------------------------------------
So when an insert statment from our crappy application tries to pass a
bad insert such as

Insert into tab1 values ('1234567890123 456')

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 '12345678901234 56' 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'12345 67890123456'

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'12345 67890123456';
end if;
END
-----------------------------------------------

But same story, SQL0433N value '12345678901234 56' 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'12345 67890123456';
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(At tr1 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

[Serge Rielau]

73blazer wrote:

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'12345 67890123456'
-----------------------------------------------
So when an insert statment from our crappy application tries to pass a
bad insert such as

Insert into tab1 values ('1234567890123 456')

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 '12345678901234 56' is too long

<snip>
x'1234567890123 456' is only 8 long....
'12345678901234 56' is 16 long

Now, the transition variable has the same data type as the table column.
So the insert values will overflow the variable just the same as the
column.

To make a long story short.. if you are willing to stick with CHAR(16)
FOR BIT DATA in the table (8 bytes waste)
All you need to do is this:

create trigger check
no cascade before insert on Tab1
referencing new as N
for each row mode DB2SQL
WHEN length(N.Attr1) >8 set N.Attr1=x'12345 67890123456'

But there is no way to intercept a column overflow with a trigger.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[73blazer]

Serge Rielau wrote:

73blazer wrote:

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'12345 67890123456'
-----------------------------------------------
So when an insert statment from our crappy application tries to pass a
bad insert such as

Insert into tab1 values ('1234567890123 456')

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 '12345678901234 56' is too long

<snip>
x'1234567890123 456' is only 8 long....
'12345678901234 56' is 16 long

Now, the transition variable has the same data type as the table column.
So the insert values will overflow the variable just the same as the
column.

To make a long story short.. if you are willing to stick with CHAR(16)
FOR BIT DATA in the table (8 bytes waste)
All you need to do is this:

create trigger check
no cascade before insert on Tab1
referencing new as N
for each row mode DB2SQL
WHEN length(N.Attr1) >8 set N.Attr1=x'12345 67890123456'

But there is no way to intercept a column overflow with a trigger.

Cheers
Serge

Ok, I screwed up, the original declaration is char(8) for bit data, not
16, there isn't any waste. But what you say is interesting, I can't
intercept column overflow, but I could perhaps change that column to be
char(16) for bit data and then check as you mention. That might screw up
our application though, and this database is multisited in 8 other
places around the globe, but that looks like my only solution so far.
Thanks for the help, again!

Ken