- Microsoft SQL Server
- System tables
- Procedure
- Views
- Functions
|
sys.sp_MSscriptupdateconflictfinder
Syntax
|
|
 |
 |
 |
|
create procedure sys.sp_MSscriptupdateconflictfinder
(
@publication sysname -- publication name
,@article sysname -- article name
,@objid int -- object id
)
as
begin
declare
@rc int
,@cmd nvarchar(4000)
,@artid int
,@pubid int
,@qualname nvarchar(517)
,@fhasnonpkuniquekeys int
-- initialize the vars we will use
select @pubid = pubid
from syspublications
where name = @publication
select @artid = artid
from sysarticles
where name = @article
and pubid = @pubid
exec sp_MSget_qualified_name @objid, @qualname OUTPUT
-- check if this article has non PK unique keys
exec @fhasnonpkuniquekeys = sp_repltablehasnonpkuniquekey @tabid = @objid
-- start scripting
select @cmd = N'
' + N'
' + N'--
' + N'-- This is the crux of the proc for conflict resolution
' + N'-- This code block is essentially a state machine
' + N'-- where we ascertain the state of resolution
' + N'-- The actions of this resolution varies for the policy
' + N'-- The comments for each state outline the policy
' + N'-- specific actions
' + N'--
' + N'--'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
if (@execution_mode in (@QPubWins, @QSubWins))
begin
declare @fpkeyupdated int'
insert into #proctext(procedure_text) values( @cmd )
-- non PK unique keys specific scripting
if (@fhasnonpkuniquekeys = 1)
begin
select @cmd = N'
declare @fnpukeyupdated int'
insert into #proctext(procedure_text) values( @cmd )
end
-- continue scripting
select @cmd = N'
' + N'
' + N'-- initialize the conflict case
' + N'
select @cftcase = 0 '
insert into #proctext(procedure_text) values( @cmd )
-- script the PK update check
select @cmd = N'
exec @fpkeyupdated = sp_MSispkupdateinconflict ' +
cast(@pubid as nvarchar(10)) + N', ' + cast(@artid as nvarchar(10)) + N', @bitmap'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
if (@fpkeyupdated = -1)
return -1'
insert into #proctext(procedure_text) values( @cmd )
-- script non PK unique key update check
if (@fhasnonpkuniquekeys = 1)
begin
select @cmd = N'
exec @fnpukeyupdated = sp_MSisnonpkukupdateinconflict ' +
cast(@pubid as nvarchar(10)) + N', ' + cast(@artid as nvarchar(10)) + N', @bitmap'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
if (@fnpukeyupdated = -1)
return -1'
insert into #proctext(procedure_text) values( @cmd )
end
-- continue scripting
select @cmd = N'
if (@rowcount = 0)
begin
' + N'
' + N'-- we had conflict for this command
' + N'--'
insert into #proctext(procedure_text) values( @cmd )
if (@fhasnonpkuniquekeys = 1)
begin
select @cmd = N'
if (@error in (547, 2601, 2627) or (@fpkeyupdated = 1) or (@fnpukeyupdated = 1)) '
end
else
begin
select @cmd = N'
if (@error in (547, 2601, 2627) or (@fpkeyupdated = 1)) '
end
insert into #proctext(procedure_text) values( @cmd )
-- continue scripting
select @cmd = N'
begin
' + N'
' + N'-- Conflict due to unique key/constraint
' + N'--'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
if (@fpkeyupdated = 1)
begin
' + N'
' + N'-- PK is being updated
' + N'--'
insert into #proctext(procedure_text) values( @cmd )
-- script check for rows with all keys with OLD values
--if (row exists with pk = OLD_PK or non PK unique keys = OLD values)
select @cmd = N'
if exists (select * from ' + @qualname
insert into #proctext(procedure_text) values( @cmd )
exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
,@artid = @artid
,@prefix = N'@c'
,@suffix = N'_old'
,@mode = 6
insert into #proctext(procedure_text) values( N' )')
-- continue scripting
select @cmd = N'
begin
' + N'
' + N'-- case 14: row(s) with OLD key values exist(s)
' + N'-- (and rows with NEW key values do not exist)
' + N'-- PubWins
' + N'-- generate delete + insert compensating action with OLD values for all unique keys
' + N'-- generate delete compensating action for row with PK = NEW_PK
' + N'-- SubWins
' + N'-- delete row with PK=OLD_PK
' + N'-- insert row with NEW values (use bitmap)
' + N'
select @cftcase = 14
end'
insert into #proctext(procedure_text) values( @cmd )
-- script check for rows with all keys with NEW values (use bitmap)
--if (row exists with pk = NEW_PK or non PK unique keys = NEW values)
select @cmd = N'
if exists (select * from ' + @qualname
insert into #proctext(procedure_text) values( @cmd )
exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
,@artid = @artid
,@prefix = N'@c'
,@suffix = NULL
,@mode = 6
insert into #proctext(procedure_text) values( N' )')
-- continue scripting
select @cmd = N'
begin
' + N'
' + N'-- row with NEW key values exist(s)
' + N'
if (@cftcase = 14)
begin
' + N'
' + N'-- case 15: rows exist with NEW key values and OLD key values
' + N'-- PubWins
' + N'-- generate delete + insert compensating action with OLD values for all unique keys
' + N'-- generate delete + insert compensating action with NEW values for all unique keys
' + N'-- SubWins
' + N'-- delete row with PK=OLD_PK
' + N'-- delete rows with NEW values of all keys
' + N'-- insert row with NEW values (use bitmap)
' + N'
select @cftcase = 15
end'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
else
begin
' + N'
' + N'-- case 16: rows exist with NEW key values and
' + N'-- row does not exist for OLD values
' + N'-- PubWins
' + N'-- generate delete compensating action for row with PK = OLD_PK
' + N'-- generate delete + insert compensating action with NEW values for all unique keys
' + N'-- SubWins
' + N'-- delete rows with NEW values of all keys
' + N'-- insert row with NEW values (use bitmap)
' + N'
select @cftcase = 16
end
end'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
else
begin
' + N'
' + N'-- row with NEW key values does not exist
' + N'
if (@cftcase = 0)
begin
' + N'
' + N'-- case 12 : no existing rows with OLD key values or NEW or new key values
' + N'-- PubWins
' + N'-- generate delete compensating action with PK = OLD_PK
' + N'-- generate delete compensating action with PK = NEW_PK
' + N'-- SubWins
' + N'-- insert row with NEW values (use bitmap)
' + N'
select @cftcase = 12
end'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
else
begin
' + N'
' + N'-- case 14: row(s) with OLD key values exist(s)
' + N'-- (and rows with NEW key values do not exist)
' + N'-- PubWins
' + N'-- generate delete + insert compensating action with OLD values for all unique keys
' + N'-- generate delete compensating action for row with PK = NEW_PK
' + N'-- SubWins
' + N'-- delete row with PK=OLD_PK
' + N'-- insert row with NEW values (use bitmap)
' + N'
select @cftcase = 14
end
end
end'
insert into #proctext(procedure_text) values( @cmd )
-- script this block if the article has non PK unique keys
if (@fhasnonpkuniquekeys = 1)
begin
-- continue scripting
select @cmd = N'
else if (@fnpukeyupdated = 1)
begin
'+N'
'+N'-- non PK unique keys are being updated but PK is not updated
'+N'-- OLD_PK == NEW_PK in these cases
'+N'--'
insert into #proctext(procedure_text) values( @cmd )
-- script the pkrowexists assignment
select @cmd = N'
declare @pkrowexist bit
if exists (select * from ' + @qualname
insert into #proctext(procedure_text) values( @cmd )
exec sp_MSscript_where_clause @objid, @artid, 'upd version', NULL, 0
select @cmd = N' )
select @pkrowexist = 1 '
insert into #proctext(procedure_text) values( @cmd )
-- script check for rows with non PK keys with OLD values
-- if (rows exist with OLD values of non PK unique keys values)
select @cmd = N'
if exists (select * from ' + @qualname
insert into #proctext(procedure_text) values( @cmd )
exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
,@artid = @artid
,@prefix = N'@c'
,@suffix = N'_old'
,@mode = 7
insert into #proctext(procedure_text) values( N' )')
-- continue scripting
select @cmd = N'
begin
if (@pkrowexist = 1)
begin
'+N'
'+N'-- case 10: rows exist with OLD non PK key values
'+N'-- (and rows with NEW non PK key values do not exist)
'+N'-- and row with OLD_PK exists
'+N'-- PubWins
'+N'-- generate delete + insert compensation action with OLD values for all keys
'+N'-- generate delete compensating action with NEW values for non PK keys
'+N'-- SubWins
'+N'-- delete row with PK=OLD_PK
'+N'-- insert row with NEW values (use bitmap)
'+N'
select @cftcase = 10
end'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
else
begin
'+N'
'+N'-- case 21: rows exist with OLD non PK key values
'+N'-- (and rows with NEW non PK key values do not exist)
'+N'-- and row with OLD_PK does not exist
'+N'-- PubWins
'+N'-- generate delete with PK = OLD_PK
'+N'-- generate delete + insert compensation action with OLD values for non PK keys
'+N'-- generate delete compensating action with NEW values for non PK keys
'+N'-- SubWins
'+N'-- delete row with PK=OLD_PK
'+N'-- insert row with NEW values (use bitmap)
'+N'
select @cftcase = 21
end
end '
insert into #proctext(procedure_text) values( @cmd )
-- script check for rows with non PK keys with NEW values (use bitmap)
-- if (rows exist with NEW values of non PK unique keys values)
select @cmd = N'
if exists (select * from ' + @qualname
insert into #proctext(procedure_text) values( @cmd )
exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
,@artid = @artid
,@prefix = N'@c'
,@suffix = NULL
,@mode = 7
insert into #proctext(procedure_text) values( N' )')
-- continue scripting
select @cmd = N'
begin
'+N'
'+N'-- find the type of conflict
'+N'
if (@cftcase in (10,21))
begin
if (@pkrowexist = 1)
begin
'+N'
'+N'-- case 20: rows exist with OLD and NEW values of non PK keys
'+N'-- and row with OLD_PK exists
'+N'-- PubWins
'+N'-- generate delete + insert compensation action with OLD values for all keys
'+N'-- generate delete + insert compensation action with NEW values for non PK keys
'+N'-- SubWins
'+N'-- delete row with PK=OLD_PK
'+N'-- delete row with NEW values for non PK keys
'+N'-- insert row with NEW values (use bitmap)
'+N'
select @cftcase = 20
end '
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
else
begin
'+N'
'+N'-- case 22: rows exist with OLD and NEW values of non PK keys
'+N'-- and row with OLD_PK does not exist
'+N'-- PubWins
'+N'-- generate delete with PK = OLD_PK
'+N'-- generate delete + insert compensation action with OLD values for non PK keys
'+N'-- generate delete + insert compensation action with NEW values for non PK keys
'+N'-- SubWins
'+N'-- delete row with PK=OLD_PK
'+N'-- delete row with NEW values for non PK keys
'+N'-- insert row with NEW values (use bitmap)
'+N'
select @cftcase = 22
end
end '
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
else
begin
if (@pkrowexist = 1)
begin
'+N'
'+N'-- case 17: rows exist with NEW values of non PK keys
'+N'-- and row does not exist with OLD values of non PK keys
'+N'-- and row with OLD_PK exists
'+N'-- PubWins
'+N'-- generate delete compensating action with OLD values for non PK keys
'+N'-- generate delete + insert compensation action with NEW values for all keys
'+N'-- SubWins
'+N'-- delete row with NEW values for non PK keys
'+N'-- insert row with NEW values (use bitmap)
'+N'
select @cftcase = 17
end '
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
else
begin
'+N'
'+N'-- case 23: rows exist with NEW values of non PK keys
'+N'-- and row does not exist with OLD values of non PK keys
'+N'-- and row with OLD_PK does not exist
'+N'-- PubWins
'+N'-- delete row with PK=OLD_PK
'+N'-- generate delete compensating action with OLD values for non PK keys
'+N'-- generate delete + insert compensation action with NEW values for non PK keys
'+N'-- SubWins
'+N'-- delete row with NEW values for non PK keys
'+N'-- insert row with NEW values (use bitmap)
'+N'
select @cftcase = 23
end
end
end '
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
else
begin
'+N'
'+N'-- row does not exist with NEW values of non PK keys
'+N'
if (@cftcase = 0)
begin
if (@pkrowexist = 1)
begin
'+N'
'+N'-- case 18 : no existing rows with OLD or NEW values of non PK keys
'+N'-- and row with OLD_PK exists
'+N'-- PubWins
'+N'-- delete row with PK=OLD_PK
'+N'-- generate delete compensating action with OLD values for non PK keys
'+N'-- generate delete compensating action with NEW values for non PK keys
'+N'-- generate insert with PK = OLD_PK
'+N'-- SubWins
'+N'-- insert row with NEW values (use bitmap)
'+N'
select @cftcase = 18
end '
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
else
begin
'+N'
'+N'-- case 24 : no existing rows with OLD or NEW values of non PK keys
'+N'-- and row with OLD_PK does not exist
'+N'-- PubWins
'+N'-- delete row with PK=OLD_PK
'+N'-- generate delete compensating action with OLD values for non PK keys
'+N'-- generate delete compensating action with NEW values for non PK keys
'+N'-- SubWins
'+N'-- insert row with NEW values (use bitmap)
'+N'
select @cftcase = 24
end
end
end
end '
insert into #proctext(procedure_text) values( @cmd )
end
-- continue scripting
select @cmd = N'
end
else
begin
' + N'
' + N'-- Conflict due non key column change or row deleted
' + N'--'
insert into #proctext(procedure_text) values( @cmd )
-- script check for rows with pk = OLD_PK
select @cmd = N'
if exists (select * from ' + @qualname
insert into #proctext(procedure_text) values( @cmd )
exec sp_MSscript_where_clause @objid, @artid, 'upd version', NULL, 0
insert into #proctext(procedure_text) values( N' )')
-- continue scripting
select @cmd = N'
begin
' + N'
' + N'-- case 11: row exists
' + N'-- PubWins
' + N'-- generate delete + insert compensating action with PK = OLD_PK
' + N'-- SubWins
' + N'-- delete row with PK=OLD_PK
' + N'-- insert row with NEW values (use bitmap)
' + N'
select @cftcase = 11
end'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
else
begin
' + N'
' + N'-- case 13: row does not exist
' + N'-- PubWins
' + N'-- generate delete compensating action with PK = OLD_PK
' + N'-- SubWins
' + N'-- insert row with NEW values (use bitmap)
' + N'
select @cftcase = 13
end
end
end'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
else if (@execution_mode = @QPubWins)
begin
' + N'
' + N'-- we had no conflict for this command
' + N'-- We need to process this block only in the Publisher Wins cases
' + N'--'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
if (@fpkeyupdated = 1)
begin
' + N'
' + N'-- PK is being updated
' + N'-- PubWins
' + N'-- generate delete + insert compensating action with OLD values for all unique keys
' + N'-- generate delete compensating action with PK=NEW_PK
' + N'
select @cftcase = 1
end'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
else
begin
' + N'
' + N'-- non PK column updated
' + N'-- PubWins
' + N'-- generate delete + insert compensating action with OLD values for all unique keys
' + N'
select @cftcase = 3
end
end
end'
insert into #proctext(procedure_text) values( @cmd )
-- all done
return 0
end
|
|
|
|
|
|
|
|
Last revision 2008RTM |
|
|
|
|
|
See also
News
|
