redaware

We recently completed a development project to create a .NET application to populate an iSeries file. The client required an audit trail to be setup, this is how to do it:

Firstly create an audit file, ensure that the file has all the fields you wish to audit.
The best way to achieve this is to copy the original source member and use an “A” suffix.
Then add three addition fields to the end of the file:

A            TIMESMP         Z         TEXT('AUDIT TIMESTAMP')
A            AUDTYP         1          TEXT('AUDIT TYPE')
A            AUDIMG         1          TEXT('AUDIT IMAGE')

To add SQL Triggers, use the STRSQL command. Copy the sample code below substituting library, file and fields for your own details.
For new records:

CREATE TRIGGER library.trigger_name
  AFTER INSERT ON library.file_name
  REFERENCING NEW AS new_row
  FOR EACH ROW MODE DB2ROW
BEGIN
  INSERT INTO library.audit_file(field1,field2,field3,field4, audtyp, audimg)  VALUES(new_row.field1, new_row.field2, new_row.field3, new_row.field4,’I’,’ A‘ );
END

For deletions:

CREATE TRIGGER library.trigger_name
  AFTER DELETE ON library.file_name
  REFERENCING OLD AS old_row
  FOR EACH ROW MODE DB2ROW
BEGIN
  INSERT INTO library.audit_file(field1,field2,field3,field4, audtyp, audimg)  VALUES(old_row.field1, old_row.field2, old_row.field3, old_row.field4,’D’,’B’ );
END

For record changes:

CREATE TRIGGER library.trigger_name
  AFTER UPDATE ON library.file_name
  REFERENCING OLD AS old_row
                            NEW AS new_row
  FOR EACH ROW MODE DB2ROW
BEGIN
  /* Before update*/
  INSERT INTO library.audit_file(field1,field2,field3,field4,audtyp,audimg)  VALUES(old_row.field1, old_row.field2, old_row.field3, old_row.field4 ,‘ U‘,’B’);
  /* After update*/
  INSERT INTO library.audit_file(field1,field2,field3,field4,audtyp,audimg)  VALUES(new_row.field1, new_row.field2, new_row.field3, new_row.field4,’U’,’A’ );
END

Removing triggers
You can remove triggers using the Remove Physical File Trigger (RMVPFTRG) command, the SQL DROP TRIGGER statement, and iSeries Navigator.

Related posts: