I think you have the 'delete some records' and 'set record' lines backwards... The foreign key constraint will prevent the deletion of b.re if any a.call_numbers's point to them...
so, something like BEGIN; DROP RULE protect_bib_rec_delete ON biblio.record_entry; UPDATE asset.call_number SET record='-1' WHERE record IN ([bib IDs]); DELETE FROM biblio.record_entry WHERE id IN ([bib IDs]); CREATE RULE protect_bib_rec_delete AS ON DELETE TO biblio.record_entry DO INSTEAD UPDATE biblio.record_entry SET deleted = TRUE WHERE OLD.id = biblio.record_entry.id; COMMIT; --Don Grant Johnson wrote: > Okay - > > '-1 instead of the records' should be '-1 instead on the records' right? > > So in your scenario: > > BEGIN; > DROP RULE protect_bib_rec_delete ON biblio.record_entry; > -- > -- ... delete some records ... > -- ... set record in asset.call_number to -1 for each call number. > -- > CREATE RULE protect_bib_rec_delete AS ON DELETE TO biblio.record_entry > DO INSTEAD UPDATE biblio.record_entry SET deleted = TRUE WHERE OLD.id > = biblio. > record_entry.id; > COMMIT; > > > >>>> On 2008/10/17 at 12:11 PM, in message > <[EMAIL PROTECTED]>, "Mike Rylander" > <[EMAIL PROTECTED]> wrote: >> On Fri, Oct 17, 2008 at 8:30 AM, Grant Johnson <[EMAIL PROTECTED]> wrote: >>> Thanks Mike. >>> >>> That's easy then. >>> Bear with me... >>> >>> What happens to rows in other tables? >>> - The "label" field will match in asset.call_number is the call number >>> - asset.copy contains the barcode >> Actually, asset.call_number and asset.copy will stop the delete >> (foreign keys). They are both similarly protected from deletes, so it >> would be best to update the record field on asset.call_number, setting >> it to -1 instead of the records you're attempting to delete. >> >>> - metabib.full has marc field info. (Is this index that will get rebuilt?) >> This table, and others like it, will get a cascading delete. >> >> --miker >> >>> -- >>> >>> F. Grant Johnson >>> Systems Coordinator >>> Robertson Library >>> University of Prince Edward Island >>> >>>>>> On 2008/10/16 at 9:04 PM, in message >>> <[EMAIL PROTECTED]>, "Mike Rylander" >>> <[EMAIL PROTECTED]> wrote: >>>> On Thu, Oct 16, 2008 at 2:27 PM, Grant Johnson <[EMAIL PROTECTED]> wrote: >>>>> I take it that it is not as easy as deleting rows from the >>>> biblio.record_entry table!?! >>>> >>>> It is that easy. However, to /really/ delete them you'll need to drop >>>> (and later reapply) the rule protecting against true deletes on that >>>> table: >>>> >>>> BEGIN; >>>> DROP RULE protect_bib_rec_delete ON biblio.record_entry; >>>> -- >>>> -- ... delete some records ... >>>> -- >>>> CREATE RULE protect_bib_rec_delete AS ON DELETE TO biblio.record_entry >>>> DO INSTEAD UPDATE biblio.record_entry SET deleted = TRUE WHERE OLD.id >>>> = biblio. >>>> record_entry.id; >>>> COMMIT; >>> >> > -- Don McMorris Jr. | Operations Manager | Equinox Software Inc. "The Evergreen Experts" | Direct: 1.678.269.6118 | Toll-free: 1.877.Open.ILS (1.877.673.6457) x709 | E-Mail/AIM: [EMAIL PROTECTED] | Web: http://www.esilibrary.com | Member: ALA (ERT, IFRT, IRRT, SRRT), PLA, LITA
