Postgresql – not-yet-assigned record is indeterminate, but triggers on update (postgresql 9.3)

postgresqltrigger

I create a very simple function, ensuring it only happens with an update.

CREATE OR REPLACE FUNCTION simple_trigger() RETURNS trigger AS $$
DECLARE
        _unused_variable INTEGER; -- unrelated placeholder.
BEGIN
        IF (TG_OP = 'UPDATE') THEN
                INSERT INTO trigger_log(id, value) values (OLD.id, OLD.value);
        END IF;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

I register the trigger

DROP TRIGGER simple_trigger_trigger on trigger_test; -- just in case. 
CREATE TRIGGER simple_trigger_trigger AFTER UPDATE ON trigger_test FOR EACH statement execute procedure simple_trigger();

I have simple table being inserted into on update.

mydatabase=# \d trigger_log
  Table "public.trigger_log"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 value  | integer | 

Nothing in that table at first.

mydatabase=# select * from trigger_log;
 id | value 
----+-------
(0 rows)

Three rows in trigger_test (the table being updated).

mydatabase=# select * from trigger_test;
 id | value 
----+-------
  1 |     2   
  2 |     2   
  3 |     2   
(3 rows)

I can't update and have the insert happen.

mydatabase=# update trigger_test set value = 100 where id = 2;
ERROR:  record "old" is not assigned yet 
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  SQL statement "INSERT INTO trigger_log(id, value) values (OLD.id, OLD.value)"
PL/pgSQL function simple_trigger() line 6 at SQL statement

My understanding per postgres docs was that NEW is unassigned on DELETE operations and OLD is unassigned on INSERT operations. That makes sense. This is neither.

How can it possible be unassigned?

This is very frustrating. I can't think of a more simple example.

Best Answer

My mistake. Trigger was for each statement; should have been for each row.

CREATE TRIGGER simple_trigger_trigger AFTER UPDATE ON trigger_test 
    FOR EACH ROW EXECUTE PROCEDURE simple_trigger();