Solved

Audit

Forum|Forum|1 year ago
June 24, 2025
11 replies
0 views

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

Best answer by harshas2775

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!

albertoc033829

There isn’t a native way in Appian to perform field-level auditing — this type of functionality has to be implemented manually.

If you're looking to track which fields were changed, when, and by whom, you would need to build that logic yourself.

Would you mind sharing more details about your specific use case?

S

swayangp408335Author

We want to capture and display on an interface the fields changed value , who changed/modified and when .

stefanhelzle0001

There are some solutions outlined in this conversation. Are you looking for something specific?

shubhama926776

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.

yashwantha0914

Hi [mention:19806d7f2a45466eaeeec7ddd420472f:e9ed411860ed4f2ba0265705b8793d05]

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.

stefanhelzle0001

Do you want to record the changes, or keep versions of the old data?

S

swayangp408335Author

I want to record the changes , to keep track of which fields are modified by which user and the updated values.

harshas2775Answer

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!

S

swayangp408335Author

Thank you for the explanations.

Sign up

Login with SSO

Login to the community

Login with SSO