CODEWITHSUNDEEP
postgresqldatabase-trigger
qadriarslan
qadriarslan

Reputation: 33

Performance wise, is it better to create a separate triggers for INSERT, DELETE and UPDATE events or just one for all the events

In order to maintain audit log for the table test_table, I need to create triggers on the base table for INSERT, UPDATE and DELETE events and then insert these records in an audit table. I can create trigger (and also associated procedure) in the following manner:

Create the procedure as:

CREATE OR REPLACE FUNCTION audit_test_table_function() RETURNS TRIGGER AS $body$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO audit_test_table VALUES (OLD.*, now(), user, pg_backend_pid(), 'D', DEFAULT);
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO audit_test_table VALUES (NEW.*, now(), user, pg_backend_pid(), 'U', DEFAULT);
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO audit_test_table VALUES (NEW.*, now(), user, pg_backend_pid(), 'I', DEFAULT);
        RETURN NEW;
    END IF;
    RETURN NULL;
END; $body$ LANGUAGE plpgsql;

And then create the trigger:

CREATE TRIGGER audit_test_table_trigger AFTER INSERT OR UPDATE OR DELETE ON test_table FOR EACH ROW EXECUTE PROCEDURE audit_test_table_function();

Other option would be to create the trigger/function for individual events ie separate one for DELETE event as following:

CREATE OR REPLACE FUNCTION audit_test_table_delete_function() RETURNS TRIGGER AS $body$
    BEGIN
        INSERT INTO audit_test_table VALUES (OLD.*, now(), user, pg_backend_pid(), 'D', DEFAULT);
        RETURN OLD;
    END;
$body$ LANGUAGE plpgsql;


CREATE TRIGGER audit_test_table_trigger AFTER DELETE ON test_table FOR EACH ROW EXECUTE PROCEDURE audit_test_table_delete_function();

And similarly for INSERT and UPDATE events.

My question is performance wise which one is recommended. And is there anything else that I should keep in mind?

I have already checked this but it doesn't answer my question.

Upvotes: 3

Views: 170

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

You'll save a little execution time if you write three simpler functions, but I doubt that it is worth the effort.

If performance is paramount, you might consider writing the trigger functions in C.

Upvotes: 1

Related Questions