I want to create an Update trigger which inserts in a table ONLY if one of the column values really changed (old value <> new value).

My table "mytable" has the structure:
id integer
name varchar(2100)

The trigger:

create trigger MYTRIGG after update on mytable
referencing new as new old as old for each row mode db2sql
when (old.NAME <> new.NAME)
begin atomic
insert into audit values('U', new.ID);
end

The trigger cannot be created because of the following error:

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUX] SQL1424N Too many references to transition variables and transition table columns or the row length for these references is too long. Reason code="2". LINE NUMBER=1. SQLSTATE=54040

What can I do? What is wrong here?

Thanks.

Straight from the IBM DB2 support site:

Problem
Error message SQL1424N is received when creating a trigger. The error message SQL1424N with reason code 2 is "sum of the lengths of the references exceeds the maximum length of a row in a table."
Cause
The sum of the lengths of references cannot exceed the maximum length of a row in a table, which is why the error message SQL1424N was received. Two facts on this problem...
1. Transition tables or variables are used as "intermediate" tables during trigger processing. They are subject to the rules governing temporary tables in terms of row lengths.
2. The maximum row length is computed based on the maximum page size of a temporary table. In other words, the length of a row cannot go over the page size of a temporary table space.
If the sum of width (in bytes) of the columns referenced in the trigger is bigger than the maximum record length, then SQL1424N rc=2 is generated.


Solution
There are two ways to solve the problem...
1. Reduce the number of referenced columns.
2. Create a small "large page size" temporary table space. By creating a "large page size" temporary table space, the maximum record length will increase. This will prevent SQL1424N rc=2.

I know where the problem comes from, but don't know how to fix it.

1. How can I reduce the number of columns if I have only 2 columns?
2. I cannot create another tablespace because my trigger will be created from an application installed on a customer machine and I cannot modify anything on their servers.

Any of the 2 solutions above is inacceptable for me.

There must be another way to solve the problem.

You could try using a hash function to compare the two string... Create a UDF that calculates a numeric hash value based on a given string; you then will be able to compare hash values for the two strings, "before" and "after".

Ok, but in order to use this function, I must SUPPLY the new and old values as parameters, and here I get the same error (because the new and old values are referred in the trigger body, no matter how or where they are referred).

So, the problem becomes impossible to solve

Other ideas?

How about creating a SQL-code function which has the id as an input parameter and the body selects the name column, does your check for difference then insert. The function is then called from your after update trigger.

It worked!!!!

THANK you very much for the idea!!!!!

Good deal. Glad it worked.