PostgreSQL multiple triggers and functions

Question

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:

Is it possible to have one BEFORE UPDATE on tbl_groups trigger that executes those 2 functions in a specific order?
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?

klin · Accepted Answer · 2016-09-25 18:29:37Z

7

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).

edited Sep 25, 2016 at 18:29

answered Sep 25, 2016 at 18:07

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

koala Over a year ago

Interesting to know that the operation will be abandoned if that trigger return NULL, I missed that part. So it would be more work to search for a possible bug somewhere if I have multiple triggers. I kinda had a feeling that the multiple triggers was a bit too much for this purpose. And for the name of the trigger, you're right, that's better. I also prefixed the functions with fn_, so good catch that I must do it on the triggers as well with trg_

Collectives™ on Stack Overflow

PostgreSQL multiple triggers and functions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related