Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    16

    Unanswered: Cannot create trigger:"SQL1424N Too many references to transition variables

    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.

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    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.

  3. #3
    Join Date
    Aug 2003
    Posts
    16
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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".

  5. #5
    Join Date
    Aug 2003
    Posts
    16

    Exclamation

    Quote Originally Posted by n_i
    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?

  6. #6
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    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.

  7. #7
    Join Date
    Aug 2003
    Posts
    16
    It worked!!!!

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

  8. #8
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    Good deal. Glad it worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •