Обсуждение: Trigger error
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; 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. -- View this message in context: http://www.nabble.com/Trigger-error-tp23060050p23060050.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
sub_woofer <tqzelijah@yahoo.co.uk> writes:
> IF (((TG_OP = 'INSERT') AND (new.subjects=TRUE)) OR ((TG_OP='UPDATE') AND
> (new.subjects=TRUE) AND (old.subjects=FALSE))) THEN
You can't do that. Split the IF apart so that you don't touch NEW or
OLD in the same if-test that tries to determine if they're safe to
touch.
regards, tom lane
----- "sub_woofer" <tqzelijah@yahoo.co.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@comcast.net
Hi Thank you both for your responses and apologies for the late reply! Tom you were correct! :) I split the If statements into 2 for the old and new tests and it all worked! :) Thanks once again for all your help! :) t. Tom Lane-2 wrote: > > sub_woofer <tqzelijah@yahoo.co.uk> writes: >> IF (((TG_OP = 'INSERT') AND (new.subjects=TRUE)) OR ((TG_OP='UPDATE') AND >> (new.subjects=TRUE) AND (old.subjects=FALSE))) THEN > > You can't do that. Split the IF apart so that you don't touch NEW or > OLD in the same if-test that tries to determine if they're safe to > touch. > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- View this message in context: http://www.nabble.com/Trigger-error-tp23060050p23095916.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.