2

For years I have been creating functions that are executed by triggers, and in the function I always returned OLD (for DELETE triggers) or NEW (for INSERT or UPDATE triggers).

Is it mandatory to specify RETURNS trigger and put return NEW/OLD at the end of the function?

CREATE OR REPLACE FUNCTION public.fun_clear_trask_draft()
    RETURNS trigger AS
$BODY$

BEGIN
    
    DELETE FROM drafts WHERE task_id = NEW.task_id;

    RETURN NEW;

END;
$BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;

CREATE TRIGGER trg_clear_task_draft
AFTER UPDATE ON task
    FOR EACH ROW
    WHEN (NEW.has_drafts = 'Y')
    EXECUTE PROCEDURE fun_clear_trask_draft();

Some tables have more than one associated trigger for insert and update.

0

1 Answer 1

3

You were doing the right thing. See the documentation:

The trigger function must be declared as a function taking no arguments and returning type trigger.

So yes, you have to declare the function as RETURNS trigger.

Trigger functions invoked by per-row triggers can return a table row [...] to the calling executor, if they choose. A row-level trigger fired before an operation has the following choices:

  • It can return NULL to skip the operation for the current row. This instructs the executor to not perform the row-level operation that invoked the trigger (the insertion, modification, or deletion of a particular table row).

  • For row-level INSERT and UPDATE triggers only, the returned row becomes the row that will be inserted or will replace the row being updated. This allows the trigger function to modify the row being inserted or updated.

A row-level BEFORE trigger that does not intend to cause either of these behaviors must be careful to return as its result the same row that was passed in (that is, the NEW row for INSERT and UPDATE triggers, the OLD row for DELETE triggers).

So yes, if you don't want to modify the new row, you have to RETURN NEW for INSERT and UPDATE triggers, and you should RETURN OLD for DELETE triggers if you don't want to cancel the DELETE.

If you have several triggers on a table, the returned value of each trigger becomes the input for the next trigger:

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

See my article on the topic for some detail. I have come to RETURN coalesce(NEW, OLD) in trigger functions that are fired on INSERT OR UPDATE OR DELETE.

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.