Stored Procedures with Delete Clauses

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.

-- 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 */