I want to make an audit log table. where i can store the previous and current data along with the date and time of the update in a seperate table

I need help with this.

To do this you would to write some functions and triggers based on UPDATE.
@Van has done this and was posted in Slack and was shared by @tristdrum :slight_smile:

create function log_table_changes() returns trigger
    language plpgsql
old_val TEXT;
new_val TEXT;
    FOR ri IN
        SELECT column_name
        FROM information_schema.columns
        WHERE table_name = 'sales'
        EXCEPT SELECT 'updatedBy'
    EXECUTE 'SELECT ($1).' || quote_ident(ri.column_name) || '::text' INTO new_val USING NEW;
    EXECUTE 'SELECT ($1).' || quote_ident(ri.column_name) || '::text' INTO old_val USING OLD;

    IF old_val is distinct from new_val THEN
      INSERT INTO change_logs (policy_id, column_name, old_value, new_value, created_by)
      VALUES (NEW.id, ri.column_name, old_val, new_val, NEW."updatedBy");
    END IF;

alter function log_table_changes() owner to "retool-admin";

grant execute on function log_table_changes() to "gds-read";

grant execute on function log_table_changes() to "vercel-admin";
1 Like

I understand how to do it using a changeset array but since I'm using the forms data I'm lost a little I need help . I'm not particularly strong in mongoDB too so please help me out.

Here's an example using Mongo:

When I run this query & re-trigger the table query, I see the value from the form in my table

I realized my screenshot cut off the top, but it's an updateOne query type. You can use moment() for the current date.