The delete procedures are identical at the primary and replicate sites. The upper-level stored procedure that controls the delete procedures and the delete procedures observe the following logic:
When a record is deleted, all dependent child records are also deleted.
A publisher record is not deleted when a title record exists.
These scripts create the del_publishers and del_titles stored procedures and the upper-level stored procedure del_pub_title.
-- Execute this script at NY and SF data servers -- Creates stored procedure create procedure del_publishers (@pub_id char(4)) as begin if exists (select * from titles where pub_id=@pub_id) raiserror 20005 “**FATAL ERROR: Existing titles**” else delete from publishers where pub_id=@pub_id end /* end of script */
-- Execute this script at NY and SF data servers -- Creates stored procedure / create procedure del_titles (@title_id tid, @pub_id char(4)=null) as if @pub_id=null delete from titles where title_id=@title_id else delete from titles where pub_id=@pub_id end /* end of script */
-- Execute this script at NY and SF data servers -- Creates stored procedure create procedure del_pub_title (@pub_id char(4), @state char(2), @title_id tid=null) as begin if @title_id != null begin exec del_titles @title_id return end if @pub_id != null begin exec del_titles @title_id, @pub_id exec del_publishers @pub_id end end /* end of script */
