Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    26

    Unanswered: How to write an automatic journal?

    I would like to write an automatic journal for my table 'booking', which logs every insert, update and delete.

    The (simplified) table 'booking' looks like this:

    Code:
    CREATE TABLE "BOOKING"
    (
       ID bigint PRIMARY KEY NOT NULL,
       VERSION bigint NOT NULL,
       CUSTOMER_ID bigint NOT NULL,
       TEXT varchar(255) 
    )
    and the 'bookingjournal' I would like to create looks like this:

    Code:
    CREATE TABLE "BOOKINGJOURNAL"
    (
       ID bigint PRIMARY KEY NOT NULL,
       VERSION bigint NOT NULL,
       CUSTOMER_ID bigint NOT NULL,
       TEXT varchar(255),
       OPERATION varchar(255)
    )
    Let's say my application makes the following operations on the table 'booking':

    Code:
    INSERT INTO BOOKING VALUES(1, 0, 42, 'Booking of customer 42.');
    INSERT INTO BOOKING VALUES(2, 0, 43, 'Booking of customer 43.');
    UPDATE BOOKING SET (TEXT, VERSION) = ('Changed booking of customer 42.', 1) WHERE ID = 1;
    UPDATE BOOKING SET (TEXT, VERSION) = ('Changed booking of customer 43.', 1) WHERE ID = 2;
    DELETE FROM BOOKING WHERE ID = 2;
    I would like the 'bookingjournal' to have the following entries:

    Code:
    ID, VERSION, CUSTOMER_ID, TEXT, OPERATION 
    1, 0, 42, 'Booking of customer 42.', 'INSERT' 
    2, 0, 43, 'Booking of customer 43.', 'INSERT'
    1, 1, 42, 'Changed booking of customer 42.', 'UPDATE'
    2, 1, 43, 'Changed booking of customer 43.', 'UPDATE'
    2, 1, 43, 'Changed booking of customer 43.', 'DELETE'
    What would be the simplest solution for achieving this? Trigger? PL/SQL-Script? And could someone possibly show me the pieces of code I need?

    Any help is greatly appreciated.

    Olel

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    according to my opinion, I would implement some triggers to capture all modifications
    if formatting is not important in first step, I would just save old-new old new
    creating a sp or simple application to format this
    all syntax is documented in infocenter and many samples are available in sample directory where db2 is installed.
    just follow the link and see for more
    IBM Knowledge Center
    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

Posting Permissions

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