The update procedures differ at the primary and replicate sites.
Update procedures observe the following logic:
Raise an error on an unknown pub_id.
If a title does not exist, insert one.
These scripts create the upd_publishers and upd_titles stored procedures and the upper-level stored procedure upd_pub_title that controls the execution of upd_publishers and upd_titles.
Note that the upd_pub_title stored procedure has an additional column, old_state, that enables replicates to subscribe to rows that migrate.
-- Execute this script at NY data servers -- Creates stored procedure create procedure upd_publishers (@pub_id char(4), @pub_name varchar(40), @city varchar(20), @state char(2)) as if not exists (select * from publishers where pub_id=@pub_id) raiserror 20005 “**FATAL ERROR: Unknown publishers id**” else update publishers set pub_name=@pub_name, city=@city, state=@state where pub_id = @pub_id end /* end of script */
-- Execute this script at NY data servers -- Creates stored procedure create procedure upd_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 * from titles where title_id=@title_id) raiserror 20005 “**FATAL ERROR: Unknown title id**” else update titles set title=@title, type=@type, pub_id=@pub_id, price=@price, advance=@advance, total_sales=@total_sales, notes=@notes, pubdate=@pubdate, contract=@contract where title_id = @title_id end /* end of script */
-- Execute this script at NY data server -- Creates stored procedure create procedure upd_pub_title (@pub_id char(4), @pub_name varchar(40)=null, @city varchar(20)=null, @state char(2)=null, @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, @old_state char(2)) as begin if not exists (select * from publishers where pub_id=@pub_id) raiserror 20005 “**FATAL ERROR: Unknown publishers id**” else exec upd_publishers @pub_id, @pub_name, @city, @state if @title_id != null begin if not exists (select * from titles where title_id=@title_id) exec ins_titles @title_id, @title, @type, @pub_id, @price, @advance, @total_sales, @notes, @pubdate, @contract else exec upd_titles @title_id, @title, @type, @pub_id, @price, @advance, @total_sales, @notes, @pubdate, @contract end end /* end of script */
Update procedures observe the following logic:
Raise an error on an unknown pub_id.
If title does not exist, insert one.
Implement correct update migration as shown in Table 3-1.
Old state |
New state |
Update procedure needs to |
|---|---|---|
CA |
CA |
Update publishers and titles tables normally. |
CA |
NY |
Delete publisher and cascade delete of all titles associated with publisher. |
NY |
CA |
Insert new publisher and title (if any). |
These scripts create the upd_publishers and upd_titles stored procedures and the managing stored procedure upd_pub_title that controls the execution of upd_publishers and upd_titles.
-- Execute this script at SF data servers -- Creates stored procedure create procedure upd_publishers (@pub_id char(4), @pub_name varchar(40), @city varchar(20), @state char(2)) as if not exists (select * from publishers where pub_id=@pub_id) raiserror 20005 “**FATAL ERROR: Unknown publishers id**” else update publishers set pub_name=@pub_name, city=@city, state=@state where pub_id = @pub_id end /* end of script */
-- Execute this script at SF data servers -- Creates stored procedure create procedure upd_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 * from titles where title_id=@title_id) exec ins_titles @title_id, @title, @type, @pub_id, @price, @advance, @total_sales, @notes, @pubdate, @contract else update titles set title=@title, type=@type, pub_id=@pub_id, price=@price, advance=@advance, total_sales=@total_sales, notes=@notes, pubdate=@pubdate, contract=@contract where title_id = @title_id end /* end of script */
-- Execute this script at SF data servers -- Creates stored procedure create procedure upd_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, @old_state char(2)) as declare @rep_state char (2) begin select @rep_state=state from publishers where pub_id=@pub_id if @old_state = @state begin exec upd_publishers @pub_id, @pub_name,@city, @state if @title_id != null exec upd_titles @title_id, @title, @type, @pub_id, @price,@advance, @total_sales, @notes, @pubdate, @contract end else if @rep_state = @old_state begin exec del_titles @title_id, @pub_id exec del_publishers @pub_id end else if @rep_state = null begin 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 /* end of script */
