Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    70

    Unanswered: before update trigger? But need to do an insert

    For auditing purposes a table needs to be mirrored/shadowed.

    Whenever a row is inserted a copy should be inserted to the auditing table.

    Whenever a row is updated the values of the row prior to the update should be inserted into the auditing table as a new row.


    I read in the ibm power builder page a "before update" trigger can't do inserts. Im pretty stumped on this.

    Is there anyway to do this other than forgetting about a trigger and just doing the insert into the auditing table manually before doing an update?

  2. #2
    Join Date
    Jul 2004
    Posts
    6
    IN DB2 ZOS:

    1)
    CREATE TRIGGER TGINS1
    AFTER INSERT ON tbsource
    REFERENCING OLD AS OLDROW
    NEW AS NEWROW
    FOR EACH ROW
    MODE DB2SQL
    INSERT INTO tbaudit
    SELECT ..... OLDROW

    2)
    CREATE TRIGGER TGINS2
    AFTER UPDATE OF col1 ON tbsource
    REFERENCING OLD AS OLDROW
    NEW AS NEWROW
    FOR EACH ROW
    MODE DB2SQL
    INSERT INTO tbaudit
    SELECT ..... OLDROW

  3. #3
    Join Date
    Jan 2004
    Posts
    70
    Thanks, that works.

    Is there a way to check to see if a row with the say primary key is already in the aud table before doing the insert? So if the key already exsists then update that document instead of trying to perform the insert?

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by JamesAvery22
    Is there a way to check to see if a row with the say primary key is already in the aud table before doing the insert? So if the key already exsists then update that document instead of trying to perform the insert?
    Perhaps you should not use the same PK in the mirror table, but eg the PK in combination with the timestamp of the INSERT/UPDATE operation. That way you can really monitor all the events on your base table.
    I would create a mirror table with all the columns of the base table and an identity column (and a timestamp field if needed).
    The identity column will provide a PK and a sequence of the operations on the base table (the timestamp field will make it easy to determine when there were bursts of activity).
    Last edited by Wim; 04-20-06 at 19:10.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2004
    Posts
    70
    Quote Originally Posted by Wim
    Perhaps you should not use the same PK in the mirror table, but eg the PK in combination with the timestamp of the INSERT/UPDATE operation. That way you can really monitor all the events on your base table.
    I would create a mirror table with all the columns of the base table and an identity column (and a timestamp field if needed).
    The identity column will provide a PK and a sequence of the operations on the base table (the timestamp field will make it easy to determine when there were bursts of activity).
    I dont have control over the design =\

    And I dont want to do what I originally though.

    Now a row is inserted into the main table. If it is updated the original data is inserted into the audit table. It should never be updated again. Business rules dictate this. But if by some way it does get updated again the trigger should check to see if there is already that row in the audit table and if there is then do nothing.

    So is there a "if row with this key doesnt exsist then {}" call?

  6. #6
    Join Date
    Jan 2004
    Posts
    70
    my final code:

    Code:
    CREATE TRIGGER copy_mytable
    AFTER UPDATE OF key1,
    		col1,
    		col2,
    		col3
    ON mytable
    REFERENCING OLD AS OLDROW
    FOR EACH ROW
    MODE DB2SQL
    	if (select count(*) from mytable_aud where key1 = OLDROW.key1) = 0 then
         insert into mytable_aud(
    		key1,
    		col1,
    		col2,
    		col3
    	)             
    	values (
    		OLDROW.key1,
    		OLDROW.col1,
    		OLDROW.col2,
    		OLDROW.col3
    	end if

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    When you said , "if there is then do nothing" is it 'do nothing' to the audit table or the base table ?

    BTW, never use
    (select count(*) from mytable_aud where key1 = OLDROW.key1) = 0

    Always use EXISTS or NOT EXISTS ... the former is preferred ...
    ie

    if not exists(select count(*) from mytable_aud where key1 = OLDROW.key1) = 0 then
    insert into mytable_aud(
    key1,
    col1,
    col2,
    col3
    )
    values (
    OLDROW.key1,
    OLDROW.col1,
    OLDROW.col2,
    OLDROW.col3
    end if
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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