5

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

Community
  • 1
  • 1
FredFloete
  • 627
  • 2
  • 13
  • 33

2 Answers2

9

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.

0

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.

JotaDeAA
  • 35
  • 6