472,374 Members | 1,567 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,374 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
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 |
usrsid | integer | not null default
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
revealemail | boolean | not null default
outsideusr | boolean | not null default
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
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
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?

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 4012

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

Similar topics

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.
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...
by: shottarum | last post by:
I currently have 2 tables as follows: CREATE TABLE . ( mhan8 int, mhac02 varchar(5), mhmot varchar(5), mhupmj int )
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);
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...
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...
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...
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...
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...
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 required to effectively administer and manage Oracle...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
by: DizelArs | last post by:
Hi all) Faced with a problem, element.click() event doesn't work in Safari browser. Tried various tricks like emulating touch event through a function: let clickEvent = new Event('click', {...

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.