Reply-To: <fmiddleton@verizon.net>
From: "Ferindo Middleton, Jr" <fmiddleton@verizon.net>
To: <pgsql-sql@postgresql.org>
Subject: UPDATE/INSERT on multiple co-dependent tables
Date: Tue, 9 Nov 2004 21:35:58 -0500
Message-ID: <000c01c4c6ce$03da7e70$1001a8c0@RENNYSLAPTOP>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Importance: Normal

Is it possible for an UPDATE/INSERT query string to function in such a way
that it requires two like fields in different tables to be equal to/'in sync
with' one another:

Example: I have two tables: registration & schedules....
they both record a class_id, start_date, end_date... I want to make sure
that if the schedule_id field is updated in the registration table; that
class_id, start_date & end_date fields automatically change to match the
schedules.id record in the schedules table.... I've devised a function to
handle this but pgsql recognizes the query to be 'infinitely recursive:

CREATE RULE registration_update AS
ON UPDATE TO registration
DO
UPDATE registration SET class_id = schedules.class_id WHERE new.schedule_id
= (SELECT schedules.id FROM schedules WHERE id = new.schedule_id);

What I'm doing is kind of redundant but necessary for
backwards-compatibility