Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: TRIGGER problem: can't retrieve NEW value from SQL statement.

    Hi,

    I can't seem to get the NEW value of the fields that aren't listed in the update statement. I would suspect to get NULL for these fields, but I get the current value from the row.

    This is the problem I'm trying to solve: The (server-side) application uses 1 dedicated user account and connection pooling. Whenever a user changes a record, I get the dedicated account value in the MODIFICATION_USER field because I use "USER" in the trigger. I've changed the application: Some INSERT/UPDATE statements use the client application login for MODIFICATION_USER, some don't.

    The BeforRowUpdate trigger now looks like this:

    create trigger LEMANS.ACC_BRU no cascade before update on LEMANS.ACCESSORIES
    referencing new as new old as old
    for each row mode db2sql
    BEGIN ATOMIC
    SET NEW.modification_dt = current timestamp;
    IF new.modification_usr IS NULL THEN
    set new.modification_usr = user;
    END IF;
    END

    !!Basically, if the application does not provide a "modification_usr" then use the account name from USER.

    The problem: NEW.modification_usr reflect the current value of the modification_usr column rather than the value provided/absent in SQL statement.
    Can I retrieve the real provided/absent value from the SQL statement?

    Help is appriciated.

    Thanks!

    Note: Using DB2 7.2(.8) fixpack 10 on Windows2000 Server.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: TRIGGER problem: can't retrieve NEW value from SQL statement.

    Originally posted by LoggheT
    Hi,

    I can't seem to get the NEW value of the fields that aren't listed in the update statement. I would suspect to get NULL for these fields, but I get the current value from the row.

    This is the problem I'm trying to solve: The (server-side) application uses 1 dedicated user account and connection pooling. Whenever a user changes a record, I get the dedicated account value in the MODIFICATION_USER field because I use "USER" in the trigger. I've changed the application: Some INSERT/UPDATE statements use the client application login for MODIFICATION_USER, some don't.

    The BeforRowUpdate trigger now looks like this:

    create trigger LEMANS.ACC_BRU no cascade before update on LEMANS.ACCESSORIES
    referencing new as new old as old
    for each row mode db2sql
    BEGIN ATOMIC
    SET NEW.modification_dt = current timestamp;
    IF new.modification_usr IS NULL THEN
    set new.modification_usr = user;
    END IF;
    END

    !!Basically, if the application does not provide a "modification_usr" then use the account name from USER.

    The problem: NEW.modification_usr reflect the current value of the modification_usr column rather than the value provided/absent in SQL statement.
    Can I retrieve the real provided/absent value from the SQL statement?

    Help is appriciated.

    Thanks!

    Note: Using DB2 7.2(.8) fixpack 10 on Windows2000 Server.
    So, if you're not specifying MODIFICATION_USR in your update statement the new row contains an old value of that field? Why is that surprising? The trigger does not access the SQL statement; it only sees the table row contents, whatever they are, and since there was no explicit update of the column in question its new value will be the same as the old one.

    I think you could accomplish what you need by re-creating the table while specifying a default value for the column.

  3. #3
    Join Date
    Mar 2004
    Posts
    4

    TRIGGER NEW. DB2 <> TRIGGER NEW. Oracle

    Kindly appriciate you for the response N_I.

    The DB2 approach for TRIGGERS is completely different from Oracle.

    NEW in Oracle provides you with exactly the same record structure as defined in the table and it
    - populates specified fields with the values from the SQL
    - populates not specified fields with NULL and NOT with values in the current row.
    If I wanted the value from the current row (before update) I must use OLD to retrieve it.
    That's the way Oracle does it.

    But indeed, setting a default value will probably work (will know very soon) apart from the fact that I get a modification_usr for every newly inserted record although it has never been modified, only created. But that's no problem, I'll just check if creation_dt and modification_dt are equal.

    What I was looking for is an Oracle-like solution to get NULL for fields that aren't specified in the SQL statement.
    Perhaps it cannot be achieved in DB2...

    Thanks for the solution. Really appreciate it.

    Tom.

  4. #4
    Join Date
    Mar 2004
    Posts
    4

    Default value not bullet proof

    The solution is never bullet proof as long as you can't determine if the SQL statement provided a value for the MODIFICATION_USR field or not. OLD and NEW won't help because NEW = OLD when you don't supply a value.

    The Oracle expert over here is a lot better at explaining...

    So, if anyone is still willing to provide me with a solution to access the SQL statement values... Especially the NULLs for values not provided.

    Thanks!

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Default value not bullet proof

    What about

    IF new.modification_usr = old.modification_usr then
    set new.modification_usr = user;

    Cheers
    Sathyaram

    Originally posted by LoggheT
    The solution is never bullet proof as long as you can't determine if the SQL statement provided a value for the MODIFICATION_USR field or not. OLD and NEW won't help because NEW = OLD when you don't supply a value.

    The Oracle expert over here is a lot better at explaining...

    So, if anyone is still willing to provide me with a solution to access the SQL statement values... Especially the NULLs for values not provided.

    Thanks!
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Mar 2004
    Posts
    4
    Thanks sathyaram_s, but new.modification_usr returns the value from the field in the database if I don't supply a modification_usr in my SQL. So your trigger would update the modification_usr with the same value as it was before although I executed the statement with a different USER.

    This is the first time I'm able to describe the problem in a descent way...

    Thanks!

Posting Permissions

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