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.