postgresqlplpgsqldatabase-triggerpostgresql-9.5

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


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


Solution

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