The insert procedures are identical at the primary and replicate sites. The upper-level stored procedure that controls the insert procedures and the insert procedures observe the following logic:
A publisher record is inserted only when there is no title ID.
A title record is inserted only when the publisher exists.
These scripts create the ins_publishers and ins_titles insert stored procedures and the upper-level stored procedure ins_pub_title.
-- Execute this script at NY and SF data servers -- Creates stored procedure create procedure ins_publishers (@pub_id char(4), @pub_name varchar(40)=null, city varchar(20)=null, @state char(2)=null) as insert publishers values (@pub_id, @pub_name, @city, @state) /* end of script */
-- Execute this script at NY and SF data servers -- Creates stored procedure create procedure ins_titles (@title_id tid, @title varchar(80), @type char(12), @pub_id char(4)=null, @price money=null, @advance money=null, @total_sales int=null, @notes varchar(200)=null, @pubdate datetime, @contract bit) as if not exists (select pub_id from publishers where pub_id=@pub_id) raiserror 20001 “** FATAL ERROR: Invalid publishers id **” else insert titles values (@title_id, @title, @type, @pub_id, @price, @advance, @total_sales, @notes, @pubdate, @contract) /* end of script */
-- Execute this script at NY and SF data servers -- Creates stored procedure create procedure ins_pub_title (@pub_id char(4), @pub_name varchar(40)=null, @city varchar(20)=null, @state char(2), @title_id tid=null, @title varchar(80)=null, @type char(12)=null, @price money=null, @advance money=null, @total_sales int=null, @notes varchar(200)=null, @pubdate datetime=null, @contract bit) as begin if @pub_name != null exec ins_publishers @pub_id, @pub_name, @city, @state if @title_id != null exec ins_titles @title_id, @title, @type, @pub_id, @price, @advance, @total_sales, @notes, @pubdate, @contract end/* end of script */
