Skip to content

Instantly share code, notes, and snippets.

@inscapist

inscapist/trigger.sql

Created May 2, 2023 08:38
Show Gist options
  • Save inscapist/03241955bb5cff7fca957a1759a80fbd to your computer and use it in GitHub Desktop.
Save inscapist/03241955bb5cff7fca957a1759a80fbd to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. inscapist created this gist May 2, 2023.
    41 changes: 41 additions & 0 deletions trigger.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,41 @@
    CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    schema_name TEXT NOT NULL,
    table_name TEXT NOT NULL,
    operation CHAR(1) NOT NULL,
    new_data JSONB,
    old_data JSONB,
    changed_at TIMESTAMP NOT NULL,
    changed_by TEXT
    );

    -- create function definition
    CREATE OR REPLACE FUNCTION audit_log_function() RETURNS TRIGGER AS $$
    BEGIN
    IF (TG_OP = 'UPDATE') THEN
    INSERT INTO audit_log (schema_name, table_name, operation, new_data, old_data, changed_at, changed_by)
    VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, 'U', to_jsonb(NEW), to_jsonb(OLD), current_timestamp, current_user);
    RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
    INSERT INTO audit_log (schema_name, table_name, operation, new_data, changed_at, changed_by)
    VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, 'I', to_jsonb(NEW), current_timestamp, current_user);
    RETURN NEW;
    ELSE
    RAISE EXCEPTION 'This trigger should only be used for UPDATE and INSERT operations.';
    END IF;
    END;
    $$ LANGUAGE plpgsql;

    -- create trigger for all tables
    DO $$
    DECLARE
    table_name TEXT;
    BEGIN
    FOR table_name IN (SELECT t.table_name FROM information_schema.tables t WHERE t.table_schema = 'public' AND t.table_type = 'BASE TABLE' AND t.table_name != 'audit_log')
    LOOP
    EXECUTE format('CREATE TRIGGER audit_log_trigger
    AFTER INSERT OR UPDATE ON %I
    FOR EACH ROW
    EXECUTE FUNCTION audit_log_function();', table_name);
    END LOOP;
    END $$;