473,320 Members | 1,600 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

trigger problem where two tables reference each other's primary keys

Table "grps"
Column | Type | Modifiers
-------------+-----------------------------+--------------------------------
---------------------
grpsid | integer | not null default
nextval('"grps_grpsid_seq"'::text)
grpsname | text | not null
memberofgrp | integer | not null default -1
dateadded | timestamp without time zone | not null default 'now'
addedby | integer | not null default 0
dateupdated | timestamp without time zone | not null default 'now'
updatedby | integer | not null default 0
outsidegrp | boolean | not null default 'f'::bool
active | boolean | not null default 't'::bool
notes | text |

Table "usrs"
Column | Type |
Modifiers
--------------------+-----------------------------+-------------------------
----------------------------
usrsid | integer | not null default
nextval('"usrs_usrsid_seq"'::text)
reportsto | integer | not null default -1
grpsid | integer | not null default -1
titlesid | integer | not null default 0
departmentsid | integer | not null default 0
firstname | character varying(100) |
lastname | character varying(100) |
phone | character varying(25) |
fax | character varying(25) |
celphone | character varying(25) |
address1 | character varying(100) |
address2 | character varying(100) |
citiesid | integer | not null default 0
states_provincesid | integer | not null default 0
postalcodeid | integer | not null default 0
countriesid | integer | not null default 0
email | character varying(255) |
password | character varying(255) | not null default '!'
revealcel | boolean | not null default
'f'::bool
revealemail | boolean | not null default
'f'::bool
outsideusr | boolean | not null default
't'::bool
dateadded | timestamp without time zone | not null default 'now'
addedby | integer | not null default 0
dateupdated | timestamp without time zone | not null default 'now'
updatedby | integer | not null default 0
active | boolean | not null default
't'::bool
notes | text |
in the usrs table, addedby and updatedby are self-referential to usrsid
grpsid refers to grpsid in grps

in the grps table, addedby and updatedby refer to the usrs table.

deletes trigger set default and updates cascade

when I update the usrs table ( any other table that has reference to the
usrsid column of usrs) no problem. when I update the grps table in any way,
I get

update grps set memberofgrp = 7 where grpsid = 6;
ERROR: constraint updgrpsupdatedbyusrsusrsid: table usrs does not have an
attribute usrsid

here is that constraint, generated by postgres at table creation and named
later:
CREATE CONSTRAINT TRIGGER "updgrpsupdatedbyusrsusrsid" AFTER UPDATE ON
"grps" FROM "usrs" DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_cascade_upd" ('updgrpsupdatedbyusrsusrsid', 'grps',
'usrs', 'UNSPECIFIED', 'updatedby', 'usrsid');

The web application that this serves as backend for can't be updating grps
unless a valide usrsid logged in, so I can be confident that the usrsid of
the updating/adding person will be in the usrs table, but I was hoping to
keep the referential integrity for consistency with the rest of the database
(and because I see it as a Good Thing) any ideas on the source of the bogus
error? Is it the cross-linking between the tables what is setting it off? Is
there a way to compensate for that?
Thanks,

Sean
Web Solutions That Work Developing custom web solutions designed
specifically to accomplish the unique objectives of our clients. Phone
503-639-2727 Fax 503-639-0807
Jul 19 '05 #1
0 4069

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

Similar topics

6
by: Kenneth Osenbroch | last post by:
Hi. I want to create a trigger that only allows delete from table A if corresponding record in table B does not exist. Any idea on how this can be done? Thanks, Kenneth.
8
by: Jason | last post by:
I have a table that matches up Securities and Exchanges. Individual securities can belong on multiple exchanges. One of the columns, named PrimaryExchangeFlag, indicates if a particular exchange is...
1
by: shottarum | last post by:
I currently have 2 tables as follows: CREATE TABLE . ( mhan8 int, mhac02 varchar(5), mhmot varchar(5), mhupmj int )
5
by: Dave Sisk | last post by:
Hey folks: I'm trying to do this: CREATE TRIGGER datawhse.emp_ti AFTER INSERT ON emp REFERENCING NEW AS n FOR EACH ROW MODE DB2ROW BEGIN DECLARE v_rrn DECIMAL(15,0);
7
by: Ilan Sebba | last post by:
I am trying to add a record using SQL. My problem is that the primary keys are foreign keys, and these foreign keys are autonumbers. I therefore do not know the primary keys of the record I am...
5
by: Prabu Subroto | last post by:
Dear my friends... I am using SuSE Linux 9.1 and postgres. I am a beginner in postgres, usually I use MySQL. I have 3 tables : appointment, appointment0 and appointment1. the fields of...
3
by: teddysnips | last post by:
I need a trigger (well, I don't *need* one, but it would be optimal!) but I can't get it to work because it references ntext fields. Is there any alternative? I could write it in laborious code...
6
by: Jchick | last post by:
Im a newbie with a sql table in a MSDE database that contains fields of CustName, BrokerName, Type, Status. I am trying to write a trigger that does this: 1. When a new record in a table is...
13
by: dennis | last post by:
Hello, I'm having trouble solving the following problem with DB2 UDB 8.2. I need to create a trigger that performs certain extra constraint validations (temporal uniqueness). One of the tables...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.