Updating another table using a trigger - Mailing list pgsql-general

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_func"();

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_func"();

CREATE OR REPLACE FUNCTION
"public"."tblriskassessors_set_inspecor_trigger_func" () 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