PostgreSQL: Checking for NEW and OLD in a function for a trigger

Question

I want to create a trigger which counts rows and updates a field in an other table. My current solution works for INSERT statements but failes when I DELETE a row.

My current function:

 CREATE OR REPLACE FUNCTION update_table_count()
RETURNS trigger AS
$$
DECLARE updatecount INT;
  BEGIN
      Select count(*) into updatecount 
        From source_table 
       Where id = new.id;
      Update dest_table set count=updatecount 
       Where id = new.id;
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

The trigger is a pretty basic one, looking like.

CREATE TRIGGER count_trigger
AFTER INSERT OR DELETE
ON source_table
FOR EACH ROW
EXECUTE PROCEDURE update_table_count();

When I excute a DELETE statement the following error occurs:

ERROR: record "new" is not assigned yet

DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.

I know one solution could be to create just one set of trigger and function for the DELETE and one for the INSERT statement. But I want to do it a bit more elegant and want to know, if there is a solution to check if NEW or OLD is present in the current context and just implement an IF ELSE block. But I dont know how to check for this context sensitive items.

Thanks for your help

Larisalarissa

Answer 1

The usual approach to make a trigger function do different things depending on how the trigger was fired is to check the trigger operation through TG_OP

CREATE OR REPLACE FUNCTION update_table_count()
RETURNS trigger AS
$$
DECLARE 
  updatecount INT;
BEGIN
  if tg_op = 'UPDATE' then 
    select count(*) into updatecount from source_table where id = new.id;
    update dest_table set count=updatecount where id = new.id;
  elsif tg_op = 'DELETE' then 
    ... do something else
  end if;
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

---

Unrelated, but: the language name is an identifier. Do not quote it using single quotes.

Coprolalia

Answer 2

From PostgreSQL's documentation:

NEW

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations.

OLD

Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations.

So, for example, if NEW is NULL, then the trigger was invoked on DELETE.

EDIT
On reply to blissweb comment, and assuming it's a BEFORE, NOT statement-level trigger, here's a plpgsql code example:

...

IF NEW IS NULL THEN
    -- Record is to be DELETEd.
ELSEIF OLD IS NULL THEN
    -- Record is to be INSERTed.
ELSE
    -- Record is to be UPDATEd.
END IF;

...

Erubescence

all good, but what's the IF statement to check inside the function ?? – Arboreous