From: | "Rod Kreisler" <rod(at)23net(dot)net> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | ERROR: NEW used in non-rule query |
Date: | 2003-01-12 04:27:08 |
Message-ID: | JNEGKNDJGBKLBDGPOPFOKELEDGAA.rod@23net.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
ERROR: NEW used in non-rule query
OK, the above is the error I'm getting upon insert into my table
"prospectNotes". I've included the table schema and trigger/function
declarations. I have no idea why this is happening. I haven't had problems
like this with any of the other tables I have created with other trigger
procedures. I'm sure I have a syntactical or logical error in
v_i_prospectNotes but I sure can't see it. I'd be grateful if someone with
fresh eyes could take a look.
Thanks
Rod
CREATE table "prospectNotes"(
"prospectNoteID" serial NOT NULL CONSTRAINT "PK_prospectNotes1" PRIMARY KEY,
"note" text NOT NULL,
"noteAdded" timestamp DEFAULT now() NOT NULL,
"addedByID" int8 NOT NULL,
"prospectID" int8 NOT NULL,
CONSTRAINT "FK_prospectNotes_1" FOREIGN KEY ("prospectID") REFERENCES
"users" ("userID"),
CONSTRAINT "FK_prospectNotes_2" FOREIGN KEY ("addedByID") REFERENCES "users"
("userID"));
COMMENT ON COLUMN "prospectNotes"."addedByID" IS 'userid of author of this
note';
COMMENT ON COLUMN "prospectNotes"."prospectID" IS 'userid of prospect this
note is about';
create or replace function "v_i_prospectNotes"() returns opaque as '
declare
errors text := '''';
checkRec RECORD;
begin
if NEW."prospectNoteID" IS NULL then
errors = errors || ''\\nprospectNoteID\\tmust not be empty.'';
end if;
if NEW."note" IS NULL then
errors = errors || ''\\nnote\\tmust not be empty.'';
end if;
if NEW."addedByID" IS NULL then
errors = errors || ''\\naddedByID\\tmust not be empty.'';
else
select into checkRec count(*) as c from "users" where
"userID"=NEW."addedByID";
if checkRec.c=0 then
errors = errors || ''\\naddedByID\\tentered does not exist.'';
end if;
end if;
if NEW."prospectID" IS NULL then
errors = errors || ''\\nprospectID\\tmust not be empty.'';
else
select into checkRec count(*) as c from "users" where
"userID"=NEW."prospectID";
if checkRec.c=0 then
errors = errors || ''\\nprospectID\\tentered does not exist.'';
end if;
end if;
if errors != '''' then
raise exception ''%'',errors;
end if;
return NEW;
end;
' language 'plpgsql';
create or replace function "v_u_prospectNotes"() returns opaque as '
declare
errors text := '''';
checkRec RECORD;
begin
raise exception ''You may not edit notes!'';
end;
' language 'plpgsql';
create trigger "t_v_i_prospectNotes" before insert on "prospectNotes" for
each row execute procedure "v_i_prospectNotes"();
create trigger "t_v_u_prospectNotes" before update on "prospectNotes" for
each row execute procedure "v_u_prospectNotes"();
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-01-12 05:19:15 | Re: ERROR: NEW used in non-rule query |
Previous Message | Bruno Wolff III | 2003-01-12 00:51:21 | Re: Reference to multiple cols |