My goal is to keep 3 separate SQL server instances which have the some table on one of their DBs up to date, meaning every insert I do on one table should be done exactly the some on the other 2. At the moment I am using the below SP (resided on all DBs):
---SP for Inserting a row in myTable
--IF ANY Errors Occur we rollback and return FALSE
--ELSE WE Return the ID of the row inserted (integer)
CREATE PROCEDURE [dbo].[sp_InsertWithRollback] @PartnerId [int]
--the table fields as parameters
AS
DECLARE @TransactionName VARCHAR(20) = 'InsertTrans';
BEGIN TRY
BEGIN TRAN @TransactionName
INSERT INTOmy table (
---
...
...
)
VALUES (
---
...
---
)
COMMIT;
SELECT SCOPE_IDENTITY();
END TRY
BEGIN CATCH
ROLLBACK TRAN @TransactionName;
SELECT 'FALSE';
END CATCH
on a C# program I use a loop (for ever one of 3 instances) where I am calling the SP for each one of the tables. If in any of those calls I get a 'FALSE' return instead of an int, I go over the other instances and delete the rows with given ID.
The problem with the above logic is that I am doing a delete if I get an error - exception. Ideally I would like to rollback and avoid deletes. Is there any good idea how will I replace the commit - delete with something smarter like ---, rollback?
