Re: Trigger error

----- "sub_woofer" <tqzelijah(at)yahoo(dot)co(dot)uk> wrote:

> Hi All
>
> Its been some time since I did any work using triggers/pgsql and when
> I did,
> it was pretty much basic stuff. Ive now returned to developing apps
> using
> postgres and have run into an error when using a trigger that I wrote
> a few
> years back (which worked fine then) but doesnt seem to work anymore! I
> must
> have changed something (?) but can't remb what!
>
> When I try to insert a record into a table called "stage" which should
> then
> fire my trigger i get the following error message:
>
> org.postgresql.util.PSQLException: ERROR: record "old" is not assigned
> yet
> Detail: The tuple structure of a not-yet-assigned record is
> indeterminate.
>
>
> Here is the code for the trigger:-
>
> CREATE OR REPLACE FUNCTION createstagesubjectlisting()
> RETURNS "trigger" AS
> $BODY$
>
>
> Declare
>
> Begin
> IF (((TG_OP = 'INSERT') AND (new.subjects=TRUE)) OR ((TG_OP='UPDATE')
> AND
> (new.subjects=TRUE) AND (old.subjects=FALSE))) THEN

An INSERT tuple does not have OLD record. You might be running into problems with your parentheses and operator precedence. I usually find it easier to follow by doing the TG_OP in IF,ELSEIF:

IF TG_OP = 'INSERT'
ELSIF TG_OP = 'UPDATE'
ELSIF TG_OP ='DELETE'

with the appropriate statements nested in each .

>
> Insert into subsperstage(stageid, subno) VALUES (new.stageid, 10);
> Insert into subsperstage(stageid, subno) VALUES (new.stageid, 20);
> Insert into subsperstage(stageid, subno) VALUES (new.stageid, 30);
> Insert into subsperstage(stageid, subno) VALUES (new.stageid, 40);
> Insert into subsperstage(stageid, subno) VALUES (new.stageid, 50);
> Insert into subsperstage(stageid, subno) VALUES (new.stageid, 100);
> Insert into subsperstage(stageid, subno) VALUES (new.stageid, 200);
> Insert into subsperstage(stageid, subno) VALUES (new.stageid, 300);
> Insert into subsperstage(stageid, subno) VALUES (new.stageid, 400);
> Insert into subsperstage(stageid, subno) VALUES (new.stageid, 500);
> Insert into subsperstage(stageid, subno) VALUES (new.stageid, 1000);
> END IF;
>
> IF ((TG_OP='UPDATE') AND (new.subjects=FALSE) AND (old.subjects=true))
> THEN
> DELETE FROM subsperstage where stageid=old.stageid;
> end if;
> Return NULL;
>
> END;
>
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
> ALTER FUNCTION createstagesubjectlisting() OWNER TO postgres;
>
>
> the trigger on table:
>
> CREATE TRIGGER createstagesubjectlisting
> AFTER INSERT OR UPDATE
> ON stage
> FOR EACH ROW
> EXECUTE PROCEDURE createstagesubjectlisting();
>
>
> Removing the trigger everything works fine - records get inserted into
> my
> stage table, but having the trigger results in no data being inserted
> in my
> stage table or the trigger being fired.
>
> Any ideas???
>
>
> Thanks in advance
>
> t.

Adrian Klaver
aklaver(at)comcast(dot)net