Thread: Trigger problem

Hi all

I'm having a problem with PostgreSQL 7.4.6-2

I do:

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

Thanks for you help!


--
Henry Molina
R&D
CMN Consulting

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/

On Sat, 4 Dec 2004, 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);

Currently statement triggers don't have any way to get at the affected
rowset. A FOR EACH ROW trigger should work for a case like the above,
although I think you'll need to add a return statement to the function as
well.