3

In the documentation of PostgreSQL's triggers Example 43.7. Auditing with Transition Tables

CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        IF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit
                SELECT 'I', now(), user, n.* FROM new_table n;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;
  
CREATE TRIGGER emp_audit_ins
    AFTER INSERT ON emp
    REFERENCING NEW TABLE AS new_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();

Every statement triggers data change. Basically the log table gets 1 new rows for every inserted row.

Is there a way to count all inserts as 1 transaction? Say inserting 1000 rows into the table to result into 1 record in the audit table?

Edit for the comments:

I consider this to be a single statement:

INSERT INTO emp(empname, salary)
SELECT 'test string', i
FROM generate_series(1, 1000) as x(i);

But it creates 1000 entries in the audit table(emp_audit).

1
  • 1
    If the 1000 rows are inserted with a single statement, the trigger will only fire once. You can then either aggregate those 1000 rows inside the trigger or only take the latest one
    – user1822
    Commented Mar 29, 2023 at 12:02

1 Answer 1

1

I changed the trigger function to the following and it inserts just a single record after the load is finished:

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        IF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit VALUES('I', now(), 4, 'postgres', NULL);
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.