基于MySQL单审计表存储JSON格式变更的简易审计追踪实现问询

Got it, let's put together this audit trail setup step by step. I'll use PostgreSQL for the examples (since it has solid native JSON support), but the core logic can be adapted to other databases like MySQL with minor adjustments to JSON functions and syntax.

Step 1: Define the audit_trail Table #

First, let's make sure your audit table is properly structured. Note that by is a reserved SQL keyword, so I'll wrap it in double quotes to avoid conflicts (you could also rename it to modified_by if you prefer a cleaner name):

CREATE TABLE audit_trail (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    "by" VARCHAR(100) NOT NULL,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    operation VARCHAR(10) NOT NULL CHECK (operation IN ('INSERT', 'UPDATE')),
    contents JSON NOT NULL
);

Step 2: Create the Trigger Function #

This function handles the core logic: checking if the triggered table has a last_modified_by field, capturing change details, and inserting the audit record.

CREATE OR REPLACE FUNCTION log_audit_trail()
RETURNS TRIGGER AS $$
DECLARE
    has_last_modified BOOLEAN;
BEGIN
    -- Check if the target table has a last_modified_by column
    SELECT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = TG_TABLE_NAME
          AND column_name = 'last_modified_by'
          AND table_schema = TG_TABLE_SCHEMA
    ) INTO has_last_modified;

    -- Only log the audit entry if last_modified_by exists on the table
    IF has_last_modified THEN
        INSERT INTO audit_trail (table_name, "by", operation, contents)
        VALUES (
            TG_TABLE_NAME, -- Get the name of the table that triggered the function
            NEW.last_modified_by, -- Use the user value from the modified record
            TG_OP, -- Get the operation type (INSERT/UPDATE)
            row_to_json(NEW) -- Convert the entire new row to JSON
        );
    END IF;

    RETURN NEW; -- Required to pass the row through for INSERT/UPDATE operations
END;
$$ LANGUAGE plpgsql;

Quick Notes on the Function: #

• TG_TABLE_NAME & TG_OP: Built-in PostgreSQL variables that automatically give you the triggered table name and operation type.
• row_to_json(NEW): Converts the full updated/inserted row into a JSON object for the contents field.
• If you want to log the database user instead of the last_modified_by value from the record, replace NEW.last_modified_by with current_user.

Step 3: Attach the Trigger to Your Target Tables #

Now you need to link this function to every table you want to monitor. For example, if you have a customers table:

CREATE TRIGGER customers_audit_trigger
AFTER INSERT OR UPDATE ON customers
FOR EACH ROW
EXECUTE FUNCTION log_audit_trail();

Repeat this snippet for each table you want to track—just update the trigger name and the ON customers clause to match your table's name.

Test the Setup #

To verify everything works, run an insert or update on a table with last_modified_by:

-- Test INSERT
INSERT INTO customers (name, email, last_modified_by)
VALUES ('Alice Smith', 'alice@example.com', 'sales_team');

-- Test UPDATE
UPDATE customers SET email = 'alice.smith@example.com' WHERE id = 1;

Then check the audit trail:

SELECT * FROM audit_trail;

You should see two entries—one for the INSERT, one for the UPDATE—each with the table name, the user from last_modified_by, the operation type, and the full JSON of the modified row.

内容的提问来源于stack exchange，提问作者Merc