using triggers to keep all versions of rows after update, delete + replication


i maintain application uses sql 2005 / 2008 data persistence.
clients use merge replication (pull or push) replicate data.

of tables contain information should never erased.
versions of row such table should stored, somewhere in database.

means updates , deletes rows should result in in 2 affected rows:
• copy of row before change
• row containing change (or deletion of row in question)

plan achieve using after update,delete triggers on tables "versioning" required.
old versions of row may stored in same database, other database (or database on linked server).

hoping creating not replication triggers on published database.
theory was:
update , delete queries performed against subscribed database should replicated (for concurrency) publisher.
when updates , deletes replicated, triggers on published database fired, updating table containing version rows.

there seem 2 problems approach:
1/
update query against table of subscriber wil result in mupltiple update queries performed against published database during replication.
merge triggers seem generate multiple update statements.
2/
multiple updates of same row in subsciber database not result in correct versioning. end-result appears replicated. (this unexpected because of 'first publisher wins' rule).

right now, seems have create versioning triggers on subscribers , on publisher. , keep diffirent versions of rows in same database source database, maintability (database structure changes 2-4 times year).

anyway... since requirement (keep versions of rows of table) seems guys may have implemented... curious how guys this.

thoughts, tips, .... appreciated.

thx

first if use not for replication trigger on publisher wouldn't fire @ replicated data data directly inputed on publisher. 

if you use not replication on both subscriber , publisher achive goal table in store version of rows shold replicated if want version persist on publishers (and subscribers). in case versions applied ones @ source (you can store source database in row table , should implement row version numbering because row can change on each location). problem in situation of conflict because conflict resolution wouldn't loged directly.you should in conflict resolver.   

second approach use change data capture options but availabe in enterprise, developer , evaluation versions. change data capture save data required.

 



SQL Server  >  SQL Server Replication



Comments

Post a Comment

Popular posts from this blog

Reindexing

hi     using sql server 2005 ee sp4. our database size 150 gb. weekly once schedule reindex job. ran 4 5 hours maximum. but recent days getting more 13 hours. the possibilities there? ragu thangavel how perform reindex? maint plan? or check fragmentation first , reorganize fragmented? if later, quite more data nowadays fragmented before. if not, consider doing "smart" defrag (ola.hallengren.com). tibor karaszi, sql server mvp | web | blog SQL Server  >  SQL Server Database Engine
Read more

Users List

hello everyone, i"m builing tool tfs , want user list form tfs "assigned to" field. if 1 can post code review i"ll gled.   thanks hello, to imports users tfs, can try this: using microsoft.teamfoundation.server; using microsoft.teamfoundation.client; using microsoft.teamfoundation.workitemtracking.client; icredentialsprovider provider = new uicredentialsprovider (); teamfoundationserver tfs = teamfoundationserverfactory .getserver(“your_server_name”, provider); tfs.authenticate(); igroupsecurityservice gss = ( igroupsecurityservice )tfs.getservice( typeof ( igroupsecurityservice )); identity idsid = gss.readidentity( searchfactor .accountname, "team foundation valid users" , querymembership .expanded); identity [] idusername = gss.readidentities( searchfactor .sid, idsid.members, querymembership .none); foreach ( identity id in idusername) {       if (id != null && id.type == identitytype .windowsuser) ...
Read more

List of charts

Image
i'm trying build report show activity for reservations placed and satisfied month branch. the report has parameter the item category and dataset has fields month category branch reservations placed reservations satisfied on front sheet i've created system wide figures 2 columns per month and a matrix broken down category (e.g. child fiction, child non fiction, adult fiction, adult non fiction) has row , column totals. i want have page each branch in system. what's best way of approaching this?  add parent group based on branch field in row groups  and add page breaks repeat matrix report every branch. please mark answer if post solved problem. milan das SQL Server  >  SQL Server Reporting Services, Power View ...
Read more