Question 137175 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: How to create AFTER OR BEFORE INSERT in one trigger?

I know that PostgreSQL can create a trigger that catches more than one event (such as UPDATE and INSERT) as shown below:

CREATE TRIGGER my_trigger1
AFTER UPDATE OR INSERT
ON my_table1
FOR EACH ROW
EXECUTE FUNCTION my_function1();

and determine event by TG_OP:

BEGIN
    IF TG_OP = 'UPDATE' THEN
        -- do something when event is update
    ELSIF TG_OP = 'INSERT' THEN
        -- do something when event is insert
    END IF;

    RETURN NULL; -- result is ignored since this is an AFTER trigger
END$$;

My question is, is it possible to create a trigger that can catch multiple events (BEFORE, AFTER), as shown below?

CREATE TRIGGER my_trigger2()
BEFORE INSERT OR AFTER INSERT
ON my_table2
FOR EACH ROW
EXECUTE FUNCTION my_function2();

and catch event by TG_WHEN:

BEGIN
    IF TG_WHEN = 'AFTER' THEN
        -- do something when event is AFTER
        RETURN NULL; -- because AFTER
    ELSIF TG_WHEN = 'BEFORE' THEN
        NEW.some_column = 'some_value';
        RETURN NEW;
    END IF;
END$$;

If it’s not possible, then what is the use of the TG_WHEN keyword?.
PostgreSQL version: 15.1;

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    As commented by @RichardHuxton and @Bergi, it is not possible to create triggers for both UPDATE and INSERT events in a single statement or query, as illustrated in the example provided in the question. Instead, we can create two separate triggers for UPDATE and INSERT events that execute the same function. Then, we can use TG_WHEN to perform the desired actions.

    1. Create function
    CREATE FUNCTOIN my_function()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_WHEN = 'AFTER' THEN
        -- do something when event is AFTER
        RETURN NULL; -- because AFTER
    ELSIF TG_WHEN = 'BEFORE' THEN
        NEW.some_column = 'some_value';
        RETURN NEW;
    END IF;
END$$;
    1. Create two triggers, one for AFTER and one for BEFORE, that execute the same function
    CREATE TRIGGER trigger_for_before
BEFORE INSERT
ON my_table
FOR EACH ROW
EXECUTE FUNCTION my_function();

CREATE TRIGGER trigger_for_after
AFTER INSERT
ON my_table
FOR EACH ROW
EXECUTE FUNCTION my_function();

  2. 0

    As some have stated in comments answering your question if you want a trigger to execute BEFORE and AFTER you have to declare two triggers that execute the same function.

    Use of OR

    The keyword OR is used to specify multiple events as per the docs:

    One of INSERT, UPDATE, DELETE, or TRUNCATE; this specifies the event that will fire the trigger. Multiple events can be specified using OR, except when transition relations are requested.

    So it is not correct to declare BEFORE INSERT OR AFTER UPDATE which also wouldn’t make any sense to the machine because it is a completely arbitrary command. What you are looking for would be of the form BEFORE INSERT AND AFTER UPDATE but this doesn’t exist.

    Use of TG_WHEN

    The TG_WHEN is used to group various operations under the same function, but nonetheless you still have to create as many triggers as operations you want to catch (INSERT, DELETE or UPDATE). This example, taken from the docs, illustrates the use of TG_WHEN:

    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
        --
        -- Create rows in emp_audit to reflect the operations performed on emp,
        -- making use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit
                SELECT 'D', now(), user, o.* FROM old_table o;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit
                SELECT 'U', now(), user, n.* FROM new_table n;
        ELSIF (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();
CREATE TRIGGER emp_audit_upd
    AFTER UPDATE ON emp
    REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_del
    AFTER DELETE ON emp
    REFERENCING OLD TABLE AS old_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();

    From a clean code and good practice standpoint I would suggest the following:

    • If you want to execute the same set of commands before and after any number of operations you should create one function and then as many triggers as operations you want execution linked to.
    • If, on the other hand, what you want is grouping different commands for different operations I would suggest you do not try to make one big function that on the long run will be hard to maintain, but instead create a few functions after_insert_trigger, before_update_trigger and so on…
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search