----- "sub_woofer" <tqzelijah@xxxxxxxxxxx> 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@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
