Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jun 2012
    Posts
    8

    Question Unanswered: Help with java stored procedure and trigger

    I'm trying to create a trigger to log the updates from one table to another table,
    the trigger must be dynamic, I can't hard-code the field names..
    I tried to pass the OLDROW and NEWROW to a java stored procedure but I don't know the type of OLDROW and NEWROW to declare the procedure.

    I need to know the type in SQL and in java.

    Thanks for help.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Please post DB2 version and OS. Can you explain why the trigger has to be dynamic?

    Andy

  3. #3
    Join Date
    Jun 2012
    Posts
    8
    DB2 9.7 Linux
    The trigger must be dynamic because I will use this in many tables on many databases, with dynamic structure is easier to maintain and easy to alter the table structure.

    Its possible to create something like that?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think you do not understand how triggers work. Triggers are for specific for a single table (not generic). And as such all columns are known and can used as needed.

    Andy

  5. #5
    Join Date
    Jun 2012
    Posts
    8
    I know it, I'm trying to create a generic java procedure that receives the oldrow and newrow values by parameter this process the data inside java.

    The trigger will be created on every table, but I don't want to hardcode the fields of that table inside the trigger.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by evozniak View Post
    I know it, I'm trying to create a generic java procedure that receives the oldrow and newrow values by parameter this process the data inside java.

    The trigger will be created on every table, but I don't want to hardcode the fields of that table inside the trigger.
    Why?

    Andy

  7. #7
    Join Date
    Jun 2012
    Posts
    8
    Because the tables don't have the same columns.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by evozniak View Post
    Because the tables don't have the same columns.
    I realize that. Why would you want to funnel everything through a single routine?

    Andy

  9. #9
    Join Date
    Jun 2012
    Posts
    8
    Code reuse, easy to maintain.
    Better than hundreds of procedures.

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Just what do you expect to pass to the Java Stored Procedure? What is this Stored Procedure going to do exactly?

    Andy

  11. #11
    Join Date
    Jun 2012
    Posts
    8
    Pass the OLDROW and NEWROW.
    The procedure will compare the rows differences and will insert into a log table something like that.
    Data on EXAMPLETABLE changed EXEMPLEFIELD -> Old value: xxxxx New Value: yyyyy

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    OLDROW and NEWROW are just correlation names. They have no scope outside the Trigger. Nothing external can use them.

    Andy

  13. #13
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    evozniak - can you explain how your (or your business) will use the contents of the log table (containing old and new values)?
    What kind of reports do you expect from it?
    What is your plan when DBAs (or particular tools) need to perform LOAD actions ?
    Is there some overlap with the functionality of db2audit ?

  14. #14
    Join Date
    Jun 2012
    Posts
    8
    can you explain how your (or your business) will use the contents of the log table (containing old and new values)?
    I will log only the fields with changes, not entire old and new.
    What kind of reports do you expect from it?
    Audit reports.
    What is your plan when DBAs (or particular tools) need to perform LOAD actions ?
    This is not a problem atm.
    Is there some overlap with the functionality of db2audit ?
    We need to audit the values not the statements.

    I'm thinking this is really hard to develop, that must be possible in some way.

  15. #15
    Join Date
    Jun 2012
    Posts
    1
    I'm in the same situation. I hope that someone can help us.

Posting Permissions

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