October 21, 2008 at 6:16 am
I have one table xyz.
There are three column ID, Name, Version.
Now, new record is insert with same id then i insert record with New Version (Previous version + 1)
for ex.
ID Name Version
--------------------
1 Pravin 0
2 Ferik 0
1 Pinakin 1
3 Prashant 0
1 PK 2
Now, Insted of Versioon system i want to do one new backup table XYZ_Backup which hold old records. Means if new record is inserted with same id then transfer old record in BackupTable XYZ_Backup and keep only latest record in our XYZ table.
In above sitution there are two way to do that
1. Using trigger (create trigger on insert,update)
2. Using Broker Services.
--> Above is dummy example of my Problem.
--> my question is that which is better one when there are thousound of transaction on table at a time.
--> Which methord save my time of read and write operation.
If any other idea to do same thing you know then please suggess me..
October 21, 2008 at 7:42 am
Service broker would still require you to enter something into the broker queue - so you would need a trigger anyway. The advantage to using service broker would be that the insert into the historical table could be moved out of the active transaction. If this historical table is on another server, this may be a good idea in case the other server is down. If it is in the same database, there is probably no benefit and you should just write to the historical table directly in the trigger. This would also roll back the original transaction if the history insert fails (which is probably what you want).
There are alternatives to this trigger history solution. I suggest you do some research on this site and Google and examine your other options.
October 21, 2008 at 8:54 am
If you're willing to do Broker, I'd do that. It gives you some flexibility to move the archive to another server/system easily later if need be. Harder to do that with a trigger.
Triggers tightly couple you to 2 databases as well. SSBS is slightly less coupled and if you have issues in your other table, this means you can still work in the primary one.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply