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')
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
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
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
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.
