I am running PostgreSQL 7.4.5 and have a trigger on a table called
tblriskassessor s which inserts, updates or delete a corresponding record
in tblinspectors by lookup of a contact id and license number match. The
INSERT and DELETE work fine. The UPDATE works good unless I update the
license number. The error, at the bottom of this message, suggests the
primary key violation. But my UPDATE in no way alters the primary key,
which is inspector_conta ct_id. A manual update on tblinspectors using
the same values works fine. There is a foreign key on tblriskassessor s
assessor_contac t_id field to the primary key above. The structures of
the two tables can be found below as well.
Can anyone see here what may be causing my problem?
CREATE TABLE "public"."tblri skassessors" (
"assessor_conta ct_id" INTEGER NOT NULL,
"assessor_certi fication_state" CHAR(2) NOT NULL,
"assessor_licen se" VARCHAR(50) NOT NULL,
"assessor_certi ficate" TEXT,
"assessor_expir ation_date" DATE,
CONSTRAINT "tblriskassesso rs_assessor_lic ense_key"
UNIQUE("assesso r_license"),
CONSTRAINT "tblriskassesso rs_pkey" PRIMARY KEY("assessor_c ontact_id"),
CONSTRAINT "tblinspectors_ tblriskassessor s_fk" FOREIGN KEY
("assessor_cont act_id")
REFERENCES "public"."tblin spectors"("insp ector_contact_i d")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE,
CONSTRAINT "tblriskassesso rstblstates_fk" FOREIGN KEY
("assessor_cert ification_state ")
REFERENCES "public"."tblst ates"("state_ab breviation")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE
) WITH OIDS;
CREATE TRIGGER "tblriskassesso rs_set_inspecor _trigger" BEFORE INSERT OR
UPDATE OR DELETE
ON "public"."tblri skassessors" FOR EACH ROW
EXECUTE PROCEDURE
"public"."tblri skassessors_set _inspecor_trigg er_func"();
CREATE TABLE "public"."tblin spectors" (
"inspector_cont act_id" INTEGER NOT NULL,
"inspector_cert ification_state " CHAR(2) NOT NULL,
"inspector_lice nse" VARCHAR(50) NOT NULL,
"inspector_cert ificate" TEXT,
"inspector_expi ration_date" DATE,
CONSTRAINT "tblinsepectors _pkey" PRIMARY KEY("inspector_ contact_id"),
CONSTRAINT "tblcontacts_tb linspectors_fk" FOREIGN KEY
("inspector_con tact_id")
REFERENCES "public"."tblco ntacts"("contac t_id")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE,
CONSTRAINT "tblinsepectors tblstates_fk" FOREIGN KEY
("inspector_cer tification_stat e")
REFERENCES "public"."tblst ates"("state_ab breviation")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE
) WITH OIDS;
COMMENT ON TABLE "public"."tblin spectors"
IS 'Risk assessors details tied to contact entry.';
CREATE UNIQUE INDEX "tblinspectors_ activity_licens e_key" ON
"public"."tblin spectors"
USING btree ("inspector_lic ense");
CREATE TRIGGER "tblriskassesso rs_set_inspecor _trigger" BEFORE INSERT OR
UPDATE OR DELETE
ON "public"."tblri skassessors" FOR EACH ROW
EXECUTE PROCEDURE
"public"."tblri skassessors_set _inspecor_trigg er_func"();
CREATE OR REPLACE FUNCTION
"public"."tblri skassessors_set _inspecor_trigg er_func" () RETURNS trigger
AS'
DECLARE
checkit record;
contactid integer;
license varchar;
BEGIN
IF (TG_OP = ''DELETE'') THEN
contactid := OLD.assessor_co ntact_id;
license := OLD.assessor_li cense;
ELSE
contactid := NEW.assessor_co ntact_id;
license := NEW.assessor_li cense;
END IF;
SELECT into checkit
public.tblinspe ctors.inspector _contact_id,
public.tblinspe ctors.inspector _certification_ state,
public.tblinspe ctors.inspector _license,
public.tblinspe ctors.inspector _certificate,
public.tblinspe ctors.inspector _expiration_dat e,
public.tblconta cts.displayas
FROM
public.tblinspe ctors
INNER JOIN public.tblconta cts ON
(public.tblinsp ectors.inspecto r_contact_id =
public.tblconta cts.contact_id)
WHERE
(public.tblinsp ectors.inspecto r_contact_id = contactid) AND
(public.tblinsp ectors.inspecto r_license = license);
IF NOT FOUND THEN
-- insert inspector if id does not exist
INSERT INTO tblinspectors VALUES (NEW.assessor_c ontact_id,
NEW.assessor_ce rtification_sta te, NEW.assessor_li cense, NULL,
NEW.assessor_ex piration_date);
IF NOT FOUND THEN
RAISE EXCEPTION ''Could not insert inspector'';
END IF;
ELSE
-- update inspector if id does not exist
IF (TG_OP = ''UPDATE'') THEN
UPDATE tblinspectors set inspector_certi fication_state =
NEW.assessor_ce rtification_sta te, inspector_licen se =
NEW.assessor_li cense, inspector_expir ation_date =
NEW.assessor_ex piration_date WHERE inspector_conta ct_id =
NEW.assessor_co ntact_id;
IF NOT FOUND THEN
RAISE EXCEPTION ''Could not update inspector'';
END IF;
END IF;
IF (TG_OP = ''DELETE'') THEN
DELETE FROM tblinspectors WHERE inspector_conta ct_id =
OLD.assessor_co ntact_id;
IF NOT FOUND THEN
RAISE EXCEPTION ''Could not update inspector'';
END IF;
END IF;
END IF;
IF (TG_OP = ''DELETE'') THEN
RETURN OLD;
ELSE
RETURN NEW;
END IF;
END;
'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
Transaction failed!
Your SQL:
update tblriskassessor s set
assessor_certif ication_state=' FL',assessor_li cense='2512',as sessor_expirati on_date='2004-09-28' where assessor_contac t_id = 11804
Error Msg:
ERROR: duplicate key violates unique constraint "tblinsepectors _pkey"
--
Robert
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
