Обсуждение: UPDATE/INSERT on multiple co-dependent tables

Is it possible for an UPDATE/INSERT query string to function in such a way that it requires two like fields in
differenttables to be equal to/'in sync with' one another: <br /><br /> Example: I have two tables: registration &
schedules....<br /> they both record a class_id, start_date,  end_date... I want to make sure that if the schedule_id
fieldis updated in the registration table; that class_id, start_date & end_date fields automatically change to
matchthe schedules.id record in the schedules table.... I've devised a function to handle this but pgsql recognizes the
queryto be 'infinitely recursive:<br /><br /> CREATE RULE registration_update AS<br /> ON UPDATE TO registration<br />
DO<br/> UPDATE registration SET class_id = schedules.class_id WHERE new.schedule_id = (SELECT schedules.id FROM
schedulesWHERE id = new.schedule_id);<br /><br /> What I'm doing is kind of redundant but necessary for
backwards-compatibility<br/><table cellpadding="0" cellspacing="0" width="100%"><tr><td> -- <br />
www.sleekcollar.com<br/> Ferindo Middleton, Jr.<br /> Chief Architect<br /> Sleekcollar Internet Application &
ArtisticVisualizations<br /><a
href="mailto:ferindo.middleton@sleekcollar.com"><u>ferindo.middleton@sleekcollar.com</u></a></td></tr></table>

> 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....
Sounds like you want a foreign key with ON UPDATE CASCADE. If
that doesn't work for some reason or other you might be able
to achieve what you need with an explicit trigger on update of
registration.schedule_id.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346