Postgres SQL trigger to update TableA.column with NEW value AFTER INSERT OR UPDATE on TableB

Question

I have a very simple trigger, that updates a column in table_a whenever table_b is updated:

create or replace function trigger_update_status()
    returns trigger as 
    $body$
    begin

-- UPDATE TABLE A   
update table_a
    set a_status = new.b_status,
        date_updated = now()
    from table_b
        where table_a.serial_number = table_b.serial_number; 

    return new;

end;
$body$
language plpgsql;

create trigger "currentStatus" after update or insert on table_b
for each row 
execute procedure trigger_update_status();

However, I am getting the error that there is no RETURN value:

ERROR: control reached end of trigger procedure without RETURN

I'm confused on whether or not NEW is appropriate here as I've been reading conflicting information. On the one hand, the answer here (Postgres trigger after insert accessing NEW) makes it clear that: "The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null. However, any of these types of triggers might still abort the entire operation by raising an error."

On the other hand, my trigger here essentially matches the one here (https://dba.stackexchange.com/questions/182678/postgresql-trigger-to-update-a-column-in-a-table-when-another-table-gets-inserte), which calls NEW & AFTER together. So I am not sure why mine is not working. Any help is very greatly appreciated!

Works for me: https://dbfiddle.uk/?rdbms=postgres_9.4&fiddle=39d331fc9a8254366b84a143e1ee3f77 — sticky bit, Sep 24 '18 at 23:04

score 0 · Answer 1 · answered Sep 25 '18 at 22:27

Future answer for anybody with same issue: it was a second (similarly named, which is why I didn't catch it until much later) trigger that was called AFTER this one that was causing the issue. The second trigger could not RETURN NEW because in this case, there was no new value. I fixed this by adding an IF/ELSE statement to my second trigger:

IF NEW.current_location <> OLD.current_location 
    THEN
    INSERT INTO table_x(serial_number, foo, bar)
        VALUES(OLD.serial_number, OLD.foo, old.bar);
    return new;

-- ADDED THIS LINE: 
    else return null;
END IF;

Lesson learned thanks to @sticky bit - if the trigger works in an isolated dbfiddle, it's something else causing the issue.

Postgres SQL trigger to update TableA.column with NEW value AFTER INSERT OR UPDATE on TableB

1 Answers1