473,726 Members | 2,098 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Updating another table using a trigger

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

Nov 23 '05 #1
1 4583

On Wed, 15 Sep 2004, Robert Fitzpatrick wrote:
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.


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
10632
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, and I'd like to know how I go about performing an update on the row that was changed when the trigger was fired. To explain, I have 2 columns, one which contains a member number, the
13
4281
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 happening is when I call the proc from within the program, only one row gets updated. So When I call the proc from Query Analyser, all rows get updated. When I call the proc from within the program, only one row gets updated
3
9500
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 table something ( id int4, sometext text, update_ts timestamp(0), primary key (id)
3
6659
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 trigger. How would I resolve this? create table dept ( dept# int not null primary key, deptname varchar(30)) @
3
3578
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 identity field in a stored procedure that is triggered at insert. I can't see that it can be made in pure SQL, but Java is not a problem. Any of you that can tell me the way of doing it ?
1
2579
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 (township field) For instance, 1060 brings up and fills in name "Spencer Tp". I have 30 townships,2 cities, 6 villages and identifiers consisting 38 separate code numbers assigned by our county government to identify each township,city and village. I ran a "Make Table Query", have created a...
7
6990
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 use ALTOBJ with this thread. I'm not going to get into the GUI because it is hard to describe in text. First of all what is the purpose of ALTOBJ()? This procedure was created mostly for ISVs who need to do produce change scripts to upgrade application from release to release, but it can also
5
2099
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 have is that when the user installs the application, I need to update the table links so that the paths are correct for the install directory. Is there an easy way to accomplish this? I'd really like to avoid duplicating the data in the linked tables, but if updating the links is too much work,...
0
1497
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 written the following trigger, however, getting the following errors. CREATE TRIGGER T_DASHBOARD AFTER UPDATE ON REQ_GRP_REC REFERENCING OLD_TABLE AS O NEW_TABLE AS N
0
9402
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9259
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9120
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8101
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4521
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4785
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3228
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2639
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2157
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.