Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2012
    Posts
    24

    Unanswered: Looping through changed values

    Hi,

    I have an update trigger in DB2 9.7. I am referencing to OLD as DELETED, and this on FOR EACH ROW. I need to loop through the list of DELETED columns for the specific row. How do I do this?

    Thanks!

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    is it not possible to have the delete trigger insert the old-val into a table where you can do any operation on with regular sql ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Aug 2012
    Posts
    24
    Quote Originally Posted by przytula_guy View Post
    is it not possible to have the delete trigger insert the old-val into a table where you can do any operation on with regular sql ?
    I am not quite sure what you mean, but the pseudo algorithm I am looking for is stated below:

    CREATE TRIGGER TRG_EMPLOYEE_ON_UPDATE
    AFTER UPDATE ON EMPLOYEE
    REFERENCING OLD AS DELETED NEW AS INSERTED
    FOR EACH ROW

    BEGIN ATOMIC
    // int i = 0;
    // while(i < DELETED.length()) {
    // col = DELETED.get(i);
    // INSERT INTO TEMP_TABLE (col1, col2) VALUES (col.name, col.value)
    // }
    END

    so this would loop through the changed columns for the affected row, take a column, get its name and value, and insert into another column.

    I am sure there is a way to do this...

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I need to loop through the list of DELETED columns for the specific row.
    I couldn't understand your requirements.
    Would you please give more concrete example?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    To know updated columns...
    Quote Originally Posted by dbzTHEdinosaur View Post
    compare old.col to new.col
    (when old.col <> new.col)
    db2 does not have (yet) a comparable function like COLUMNS_UPDATED() which returns a bitmask indicating which columns have been updated.
    Looking into your pseudo algorithm, how about something like this?
    Code:
    INSERT INTO TEMP_TABLE
    (col1, col2)
    SELECT 'COL_1' , old.col_1
     FROM  sysibm.sysdummy1
     WHERE old.col_1 <> new.col_1
    UNION ALL
    SELECT 'COL_2' , old.col_2
     FROM  sysibm.sysdummy1
     WHERE old.col_2 <> new.col_2
    UNION ALL
    ...
    UNION ALL
    SELECT 'COL_n' , old.col_n
     FROM  sysibm.sysdummy1
     WHERE old.col_n <> new.col_n

  6. #6
    Join Date
    Aug 2012
    Posts
    24
    Quote Originally Posted by tonkuma View Post
    To know updated columns...


    Looking into your pseudo algorithm, how about something like this?
    Code:
    INSERT INTO TEMP_TABLE
    (col1, col2)
    SELECT 'COL_1' , old.col_1
     FROM  sysibm.sysdummy1
     WHERE old.col_1 <> new.col_1
    UNION ALL
    SELECT 'COL_2' , old.col_2
     FROM  sysibm.sysdummy1
     WHERE old.col_2 <> new.col_2
    UNION ALL
    ...
    UNION ALL
    SELECT 'COL_n' , old.col_n
     FROM  sysibm.sysdummy1
     WHERE old.col_n <> new.col_n
    So if I understand correctly: the algorithm takes a column that has changed, and inserts it into TEMP_TABLE COL_N. In my case for every changed column, and new row of TEMP_TABLE is created and values of column name and column value are inserted into it.

    My challenges:
    1) To my understanding the list of OLD/NEW columns contains a list of updated columns (whose value has changed). Therefore, no comparison between NEW and OLD columns would be needed?
    2) I don't necessarily know the name of the columns, so I don't know what list to iterate through (so I can't directly reference to a specific column). I need to know the column name and its value
    3) I need to iterate the whole list of columns and their values in OLD/NEW lists.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    First of all,
    I want confirm that you should replace col_1, col_2, ... , col_n to the real column names on your table.

    1) To my understanding the list of OLD/NEW columns contains a list of updated columns (whose value has changed). Therefore, no comparison between NEW and OLD columns would be needed?
    Comparisons done in WHERE clauses.
    If removed WHERE clauses, all columns including not updated would be inserted.

    2) I don't necessarily know the name of the columns, so I don't know what list to iterate through (so I can't directly reference to a specific column). I need to know the column name and its value
    You are working on a specific table(because each trigger declared on a specific table).
    So, you know all column names of the table.
    What is the difficulty on it?

    3) I need to iterate the whole list of columns and their values in OLD/NEW lists.
    Yes, of caurse.

  8. #8
    Join Date
    Aug 2012
    Posts
    24
    Quote Originally Posted by tonkuma View Post
    You are working on a specific table(because each trigger declared on a specific table).
    So, you know all column names of the table.
    What is the difficulty on it?
    I forgot to mention that I want this trigger to be more generic. So I will have a stored procedure that applies this trigger to a whole load of tables (some things will be dynamically inserted by the stored procedure, e.g. table name). Therefore the trigger needs to be generic in such a way, that it does not need to know the table names. Sorry about that

    I am still confused about the basics of the OLD/NEW expression:
    If I have something like "REFERENCING OLD AS DELETED NEW AS INSERTED", what does the structure of "DELETED" look like? is it a list containing names of the columns? Does it contain only column values? Or is it a key/value list, containing [col_name, col_value] -pairs?

    So can I get both the NAME and the VALUE of the columns in the DELETED/INSERTED list?

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    At first you wrote
    I have an update trigger in DB2 9.7. ...
    You have changed your issue.
    Some of considerations/dialogues were in vain.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I am still confused about the basics of the OLD/NEW expression:
    If I have something like "REFERENCING OLD AS DELETED NEW AS INSERTED", what does the structure of "DELETED" look like? ...
    Please read manuals, like...
    CREATE TRIGGER - IBM DB2 9.7 for Linux, UNIX, and Windows

    Description

    ...
    ...

    REFERENCING
    Specifies the correlation names for the transition variables and the table names for the transition tables. Correlation names identify a specific row in the set of rows affected by the triggering SQL operation. Table names identify the complete set of affected rows. Each row affected by the triggering SQL operation is available to the triggered action by qualifying columns with correlation-names specified as follows.

    OLD AS correlation-name
    Specifies a correlation name which identifies the row state prior to the triggering SQL operation.
    NEW AS correlation-name
    Specifies a correlation name which identifies the row state as modified by the triggering SQL operation and by any SET statement in a BEFORE trigger that has already executed.
    ...
    and Examples in the page.
    Last edited by tonkuma; 08-07-12 at 09:27.

Tags for this Thread

Posting Permissions

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