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

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;
[/SIZE]

the trigger on tbl:

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.