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?

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

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?

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?

my final code:

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