CODEWITHSUNDEEP
sqlpostgresqltriggers
koala
koala

Reputation: 1564

PostgreSQL multiple triggers and functions

I have the following tables.

tbl_groups contains all the current data, and I'm storing the name in a history table when it changes.

CREATE TABLE tbl_groups (
    id integer NOT NULL,
    name varchar NOT NULL,
    active boolean NOT NULL
);

CREATE TABLE tbl_groups_history (
    id integer NOT NULL,
    group_id integer NOT NULL,
    name varchar NOT NULL        
);

When the active field is false an update may not occur. So I created a trigger for this to throw an exception when this happens:

CREATE OR REPLACE FUNCTION fn_group_deny_update_when_inactive() RETURNS TRIGGER AS
$BODY$
    BEGIN
        IF 
            OLD.active = false
        THEN 
            RAISE EXCEPTION 'Record is inactive';
        END IF;
        RETURN NEW;
    END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER 01_group_can_update
BEFORE UPDATE ON tbl_groups
FOR EACH ROW
EXECUTE PROCEDURE fn_group_deny_update_when_inactive();

I also have a trigger that writes a record to the history table

CREATE OR REPLACE FUNCTION fn_group_log_history() RETURNS trigger as 
$BODY$
    BEGIN
        IF
           (NEW.name <> OLD.name)       
        THEN
            INSERT INTO tbl_groups_history (group_id, name)
            VALUES (OLD.id, OLD.name);
        END IF;
        RETURN NEW;
    END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER 02_group_write_history_log
BEFORE UPDATE ON tbl_groups
FOR EACH ROW
EXECUTE PROCEDURE fn_group_log_history();

I have named the triggers with 01 and 02 as a prefix, so I know in what order they will be executed as PostgreSQL fires them in alphabetical order.

I have some questions about this approach as I'm not sure if it's good practice:

  1. Is it possible to have one BEFORE UPDATE on tbl_groups trigger that executes those 2 functions in a specific order?
  2. Is it better to have just one function that does a check on the "active" field first, and if it continues, do the history stuff? So I will have just 1 (bigger) function and only 1 trigger.

Everything in one function:

 CREATE OR REPLACE FUNCTION fn_group_before_update() RETURNS trigger as 
$BODY$
    BEGIN
        IF 
            OLD.active = false
        THEN 
            RAISE EXCEPTION 'Record is inactive';
        END IF;
        IF
           (NEW.name <> OLD.name)       
        THEN
            INSERT INTO tbl_groups_history (group_id, name)
            VALUES (OLD.id, OLD.name);
        END IF;
        RETURN NEW;
    END;
$BODY$

Any thoughts about this considering performance, maintenance of code and stuff like that?

Upvotes: 6

Views: 11536

Answers (1)

klin
klin

Reputation: 121494

Two problems. 01_group_can_update is not a proper name, so change this for e.g.:

CREATE TRIGGER trg_01_group_can_update
BEFORE UPDATE ON tbl_groups
FOR EACH ROW
EXECUTE PROCEDURE fn_group_deny_update_when_inactive();

Next, the id in tbl_groups_history should be serial:

CREATE TABLE tbl_groups_history (
    id serial NOT NULL,
    group_id integer NOT NULL,
    name varchar NOT NULL        
);

There is no reason to have more than one trigger in your case, so the second solution seems better. However, the first variant should work too. Per the documentation:

If more than one trigger is defined for the same event on the same relation, the triggers will be fired in alphabetical order by trigger name. In the case of BEFORE and INSTEAD OF triggers, the possibly-modified row returned by each trigger becomes the input to the next trigger. If any BEFORE or INSTEAD OF trigger returns NULL, the operation is abandoned for that row and subsequent triggers are not fired (for that row).

Upvotes: 7

Related Questions