Updating another table using a trigger

[Robert Fitzpatrick]

I am running PostgreSQL 7.4.5 and have a trigger on a table called
tblriskassessors 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_contact_id. A manual update on tblinspectors using
the same values works fine. There is a foreign key on tblriskassessors
assessor_contact_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"."tblriskassessors" (
"assessor_contact_id" INTEGER NOT NULL,
"assessor_certification_state" CHAR(2) NOT NULL,
"assessor_license" VARCHAR(50) NOT NULL,
"assessor_certificate" TEXT,
"assessor_expiration_date" DATE,
CONSTRAINT "tblriskassessors_assessor_license_key"
UNIQUE("assessor_license"),
CONSTRAINT "tblriskassessors_pkey" PRIMARY KEY("assessor_contact_id"),
CONSTRAINT "tblinspectors_tblriskassessors_fk" FOREIGN KEY
("assessor_contact_id")
REFERENCES "public"."tblinspectors"("inspector_contact_id ")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE,
CONSTRAINT "tblriskassessorstblstates_fk" FOREIGN KEY
("assessor_certification_state")
REFERENCES "public"."tblstates"("state_abbreviation")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE
) WITH OIDS;

CREATE TRIGGER "tblriskassessors_set_inspecor_trigger" BEFORE INSERT OR
UPDATE OR DELETE
ON "public"."tblriskassessors" FOR EACH ROW
EXECUTE PROCEDURE
"public"."tblriskassessors_set_inspecor_trigger_fu nc"();

CREATE TABLE "public"."tblinspectors" (
"inspector_contact_id" INTEGER NOT NULL,
"inspector_certification_state" CHAR(2) NOT NULL,
"inspector_license" VARCHAR(50) NOT NULL,
"inspector_certificate" TEXT,
"inspector_expiration_date" DATE,
CONSTRAINT "tblinsepectors_pkey" PRIMARY KEY("inspector_contact_id"),
CONSTRAINT "tblcontacts_tblinspectors_fk" FOREIGN KEY
("inspector_contact_id")
REFERENCES "public"."tblcontacts"("contact_id")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE,
CONSTRAINT "tblinsepectorstblstates_fk" FOREIGN KEY
("inspector_certification_state")
REFERENCES "public"."tblstates"("state_abbreviation")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE
) WITH OIDS;

COMMENT ON TABLE "public"."tblinspectors"
IS 'Risk assessors details tied to contact entry.';

CREATE UNIQUE INDEX "tblinspectors_activity_license_key" ON
"public"."tblinspectors"
USING btree ("inspector_license");

CREATE TRIGGER "tblriskassessors_set_inspecor_trigger" BEFORE INSERT OR
UPDATE OR DELETE
ON "public"."tblriskassessors" FOR EACH ROW
EXECUTE PROCEDURE
"public"."tblriskassessors_set_inspecor_trigger_fu nc"();

CREATE OR REPLACE FUNCTION
"public"."tblriskassessors_set_inspecor_trigger_fu nc" () RETURNS trigger
AS'
DECLARE
checkit record;
contactid integer;
license varchar;

BEGIN
IF (TG_OP = ''DELETE'') THEN
contactid := OLD.assessor_contact_id;
license := OLD.assessor_license;
ELSE
contactid := NEW.assessor_contact_id;
license := NEW.assessor_license;
END IF;

SELECT into checkit
public.tblinspectors.inspector_contact_id,
public.tblinspectors.inspector_certification_state ,
public.tblinspectors.inspector_license,
public.tblinspectors.inspector_certificate,
public.tblinspectors.inspector_expiration_date,
public.tblcontacts.displayas
FROM
public.tblinspectors
INNER JOIN public.tblcontacts ON
(public.tblinspectors.inspector_contact_id =
public.tblcontacts.contact_id)
WHERE
(public.tblinspectors.inspector_contact_id = contactid) AND
(public.tblinspectors.inspector_license = license);

IF NOT FOUND THEN
-- insert inspector if id does not exist
INSERT INTO tblinspectors VALUES (NEW.assessor_contact_id,
NEW.assessor_certification_state, NEW.assessor_license, NULL,
NEW.assessor_expiration_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_certification_state =
NEW.assessor_certification_state, inspector_license =
NEW.assessor_license, inspector_expiration_date =
NEW.assessor_expiration_date WHERE inspector_contact_id =
NEW.assessor_contact_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_contact_id =
OLD.assessor_contact_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 tblriskassessors set
assessor_certification_state='FL',assessor_license ='2512',assessor_expiration_date='2004-09-28' where assessor_contact_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*******@postgresql.org

[Stephan Szabo]

On Wed, 15 Sep 2004, Robert Fitzpatrick wrote:

I am running PostgreSQL 7.4.5 and have a trigger on a table called
tblriskassessors 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_contact_id. A manual update on tblinspectors using
the same values works fine. There is a foreign key on tblriskassessors
assessor_contact_id field to the primary key above. The structures of
the two tables can be found below as well.

Are you sure that you're going in the update path and not the insert path
inside the function? Could the select/if not found be taking effect at
which point the insert occurs rather than the else block?
RAISE NOTICE might be useful to determine thise.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org