Re: Trigger problem

On Sat, Dec 04, 2004 at 11:53:46PM -0500, Henry Molina wrote:

> drop table t1;
> drop table t2;
> create table t1 (id integer);
> create table t2 (id integer);
> CREATE OR REPLACE FUNCTION myfunc() RETURNS trigger AS '
> BEGIN
>     insert into t2 values(NEW.id);
> END;
> ' LANGUAGE plpgsql;
>
> CREATE TRIGGER
>     mytri
>     AFTER INSERT ON t1 FOR EACH STATEMENT
>     EXECUTE PROCEDURE myfunc();
> insert into t1 values(1);
>
> and I get:
>
> ERROR:  record "new" is not assigned yet
> DETAIL:  The tuple structure of a not-yet-assigned record is
> indeterminate.
> CONTEXT:  PL/pgSQL function "myfunc" line 2 at SQL statement

If you want to access NEW then declare your trigger to be FOR EACH
ROW.  Statement-level triggers set NEW to NULL because the trigger
fires not for a particular row, but for the entire statement, which
could affect multiple rows.

Also, your trigger function doesn't return a value.  Even though
AFTER triggers ignore the return value, the function must still
return something.  The documentation recommends returning NULL
when the value will be ignored.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/