Audit

Question

How can we achieve a field level audit . Can someone suggest step by step .

Harsha Sharma · Answer

For these use cases I prefer to setup an Update triggers on my table which inserts data in to an audit table with details of the changes I want to track e.g. column name, old value, new value, timestamp, user etc.

So users can update as they like and a simple sql trigger can maintain the audit of columns/fields level changes. Below is a sample code for reference :

CREATE TRIGGER persons_update
AFTER UPDATE
ON persons FOR EACH ROW
BEGIN
    IF OLD.FirstName <> new.FirstName THEN
        insert into persons_audit_trail(Personid, column_name, old_value, new_value, done_by) values(NEW.Personid,'FirstName',OLD.FirstName,NEW.FirstName,NEW.updated_by);
    END IF;
	IF OLD.LastName <> new.LastName THEN
        insert into persons_audit_trail(Personid, column_name, old_value, new_value, done_by) values(NEW.Personid,'LastName',OLD.LastName,NEW.LastName,NEW.updated_by);
    END IF;
	IF OLD.Age <> new.Age THEN
        insert into persons_audit_trail(Personid, column_name, old_value, new_value, done_by) values(NEW.Personid,'Age',OLD.Age,NEW.Age,NEW.updated_by);
    END IF;
	IF OLD.is_deleted <> new.is_deleted THEN
        insert into persons_audit_trail(Personid, column_name, old_value, new_value, done_by) values(NEW.Personid,'is_deleted',OLD.is_deleted,NEW.is_deleted,NEW.updated_by);
    END IF;
END

As others mentioned you can setup record and drive this whole process from Appian as well, I prefer sql as it helps in performance in my opinion!

Shubham Aware · Answer

Create a generic audit_log table with columns for table_name, field_name, record_id, old_value, new_value, changed_by, changed_date and operation. Build a corresponding Record and create an expression rule that compares old vs new record values field-by-field, logging any differences to the audit table. In process model, capture the current record before any update operation, then after the update call your compare expression passing the old and new values along with metadata like table name, Record ID, and user. Then finally, Display field level log as per your need.

Yashwanth Akula · Answer

Hi swayang Create a record type "Field Audit" with the following columns: {PK, FK, fieldName, oldValue, newValue, modifiedOn, modifiedBy}. In the interface, implement logic in the Update button to compare the previous and updated values of each field. For any field that has changed, construct a corresponding "Field Audit" record capturing the field name, old value, new value, timestamp, and the user who made the change. Pass this data to a process model, which will write the audit records to the database.

Audit

Top Replies