472,330 Members | 1,488 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,330 software developers and data experts.

Updating another table using a trigger

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

Nov 23 '05 #1
1 4414

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

Nov 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: M Wells | last post by:
Hi All, I'm a relatively newbie to SQL Server 2000, having come from a MySQL background. I'm creating my first Trigger statement on a table,...
13
by: EmbersFire | last post by:
I'm using a stored proceedure which should update a number of rows in a table depending on a key value supplied (in this case 'JobID'). But what's...
3
by: Andreas | last post by:
Hello list, I suspect, this is a common issue for newbies. Is there a simple way to have an auto-updating timestamp like mysql has ? create...
3
by: Prince Kumar | last post by:
Hi All, I am trying to a get a trigger retrieved from Oracle to work on DB2 UDB 8.1. I am getting the following error when trying to create the...
3
by: Poul Møller Hansen | last post by:
Hi, I need an auto incrementing field that will contain values like N000001, N000002, N000003 etc. I think the way is to use the value from an...
1
by: Old Timer | last post by:
I wish to type in a number in my "Code" field, for instance 1060, I then wish the number 1060 to trigger an event that will fill in the next field...
7
by: Serge Rielau | last post by:
Hi all, Following Ian's passionate postings on problems with ALTOBJ and the alter table wizard in the control center I'll try to explain how to...
5
by: aaron.m.johnson | last post by:
I have an application which contains an Access database with linked tables that point to another database within the application. The problem I...
0
by: KiranKGone | last post by:
Hello All, I need to define a trigger for updating the multiple columns of a target table when an insert happens on a subject table. I have...
0
by: tammygombez | last post by:
Hey fellow JavaFX developers, I'm currently working on a project that involves using a ComboBox in JavaFX, and I've run into a bit of an issue....
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.