How to log record changes?

Question

I'm building an app where users are able to complete jobs that required to be approved by other user. Thus, if the approver changes (update) any data related with this record, I need to save this changes on an audit table like https://github.com/spatie/laravel-activitylog.

What is the best approach? Implement using trigger or compare the objects? Does Android has a lib that can lead with it?

Below is the operation model:

public class Operation {
    private int id;
    private String started_at;
    private int company_id;
    private int collaborator_id;
    private int approver_id;
    private int building_id;
    private String cost_center;
    private String ended_at;
    private int shift_id;
    private int service_id;
    private String invoice_number;
    private String transport_number;
    private String cod;
    private String lot;
    private int vehicle_id;
    private int carrier_id;
    private String vehicle_plate;
    private String cart_plate;
    private int amount;
    private int loose_cargo;
    private int palletized_load;
    private int gross_weight;
    private String comments;
    private int number_assistants;
    private int number_operators;
    private int number_tractors;
    private int number_samples;
    private int number_others;
}

I was trying to create it as below:

"CREATE TRIGGER " + OperationEntry.TRIGGER_NAME +
    " AFTER UPDATE " +
    " ON["+ OperationEntry.TABLE_NAME +"] " +
    " FOR EACH ROW " +
    " BEGIN " +
    " DECLARE changes VARCHAR(8000); " +
    " SET changes = '{'; " +
    " IF OLD. " + OperationEntry.COLUMN_ID + " <> " + " NEW. " + OperationEntry.COLUMN_ID + " THEN " +
    "SET changes = CONCAT(changes, );" +
    " END IF; " +
    " IF OLD. " + OperationEntry.COLUMN_COMPANY + " <> " + " NEW. " + OperationEntry.COLUMN_COMPANY + " THEN " +
    "SET changes = CONCAT(changes, );" +
    " END IF; " +
    " IF OLD. " + OperationEntry.COLUMN_BUILDING + " <> " + " NEW. " + OperationEntry.COLUMN_BUILDING + " THEN " +
    "SET changes = CONCAT(changes, );" +
    " END IF; " +
    " SET changes = CONCAT(changes, '}'); " +
    " END; ");

Answer 1

Using an AFTER UPDATE TRIGGER would be appear to be suitable and would need no additional code other than the code needed to generate the TRIGGER.

The issue is that the existence of the TRIGGER is not obvious, but that drawback could be overcome with suitable commenting within the code.

re :-

I was trying to create it as below:

"CREATE TRIGGER " + OperationEntry.TRIGGER_NAME +
    " AFTER UPDATE " +
    " ON["+ OperationEntry.TABLE_NAME +"] " +
    " FOR EACH ROW " +
    " BEGIN " +
    " DECLARE changes VARCHAR(8000); " +
    " SET changes = '{'; " +
    " IF OLD. " + OperationEntry.COLUMN_ID + " <> " + " NEW. " + OperationEntry.COLUMN_ID + " THEN " +
    "SET changes = CONCAT(changes, );" +
    " END IF; " +
    " IF OLD. " + OperationEntry.COLUMN_COMPANY + " <> " + " NEW. " + OperationEntry.COLUMN_COMPANY + " THEN " +
    "SET changes = CONCAT(changes, );" +
    " END IF; " +
    " IF OLD. " + OperationEntry.COLUMN_BUILDING + " <> " + " NEW. " + OperationEntry.COLUMN_BUILDING + " THEN " +
    "SET changes = CONCAT(changes, );" +
    " END IF; " +
    " SET changes = CONCAT(changes, '}'); " +
    " END; ");

I don't believe much of the above would work. There is no DECLARE keyword in SQLITE's implementation of SQL and the IF keyword is very limited.

Concatenating values is also pretty awkard.

I'd suggest considering the following based upon what you appear to be attempting :-

-- Drop tables and triggers (allows rerunability for testing)
DROP TABLE IF EXISTS OperationEntry;
DROP TABLE IF EXISTS OperationEntryChanges;
DROP TRIGGER IF EXISTS OperationEntryTrigger;

-- Create the original/core table
CREATE TABLE IF NOT EXISTS OperationEntry (
    id INTEGER PRIMARY KEY, 
    company TEXT,   building TEXT
);
-- Create the logging table
CREATE TABLE IF NOT EXISTS OperationEntryChanges (
    oldid INTEGER, 
    newid INTEGER, 
    oldcompany TEXT, 
    newcompany TEXT, 
    oldbuilding TEXT, 
    newbuilding TEXT, 
    timestamp DEFAULT CURRENT_TIMESTAMP, -- assumes that you want some indications of when the update was made
    updatecounter DEFAULT 0 -- maybe over the top/not required may replace or compliment timestamp
    );
-- Create the logging trigger
CREATE TRIGGER OperationEntryTrigger
    AFTER UPDATE  ON OperationEntry
        BEGIN 
            INSERT INTO OperationEntryChanges 
                (oldid, newid, oldcompany, newcompany, oldbuilding, newbuilding, updatecounter) 
            VALUES
                (old.id, new.id, old.company, new.company, old.building, new.building, (SELECT count(*) + 1 FROM OperationEntryChanges)
                );
        END
;

-- Add some testing data
INSERT INTO OperationEntry (company, building) VALUES
        ('company1','Building1'),('company1','Building2'),('company1','Building3'),('company1','Building4'),
        ('company2','Building1'),('company2','Building2'),('company2','Building3'),
        ('company3','Building1'),('company3','Building2'),('company3','Building3'),('company3','Building4'),('company3','Building5')
;

-- Show the data prior to any updates
SELECT * FROM OperationEntry;
SELECT * FROM OperationEntryChanges;

-- Apply some updates
UPDATE OperationEntry SET company = company || 'A' WHERE Building = 'Building2';
UPDATE OperationEntry SET building = replace(building,'Building','Bldg') WHERE building = 'Building5';
UPDATE OperationEntry SET building = building||'X', company = company||'X' WHERE company = 'company1' AND building = 'Building3';

-- Show the data post updates
SELECT * FROM OperationEntry;
SELECT * FROM OperationEntryChanges;
-- Show the changes made
SELECT 
  datetime(timestamp),
    updatecounter,
    CASE 
      WHEN oldcompany <> newcompany AND oldbuilding <> newbuilding THEN 'Company changed from '|| oldcompany || ' to ' || newcompany || ', also Building changed from ' || oldbuilding || ' to ' || newbuilding
        WHEN oldcompany <> newcompany THEN 'Company changed from '|| oldcompany || ' to ' || newcompany 
      WHEN oldbuilding <> newbuilding THEN 'Building changed from ' || oldbuilding || ' to ' || newbuilding END AS changemade
FROM OperationEntryChanges
;

The following would be the results :-

Result 1

The core table after loading data :-

Result 2

The changes/logging table after loading the data (i.e. empty as no updates)

Result 3

The changed core table (changes highlighted)

Result 4

The change log table (after all 5 updates (6 values changed))

Result 5

The changes made in a more human usable format