Binaryworld - How to perform update on multiple tables of linked server. ... [ SQL Server -> Linked Server ]

This code demonstrates
1) How to write update trigger which checks modification for a specified field (e.g. here DistrictId)
2) How to handle Errors during update
3) How to update data on linked server.
4) How to do update which requires joining tables
5) How to use XACT_ABORT option to handle transaction so it rolls back all changes if any statement generates run-time error.

Remarks:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When OFF, only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

Click here to copy the following block

CREATE TRIGGER tr_PropInfo on its_propertyxref FOR UPDATE
AS

SET XACT_ABORT ON

IF exists(select * from inserted) and exists(select * from deleted) begin
--print 'Update...'

IF UPDATE(districtid) BEGIN
--//select * from LOKI.PropInfo.dbo.PropInfoXref

--///////////////////////////////////////////////
--//Update PropInfo..propinfoxref table on loki
--///////////////////////////////////////////////
UPDATE LOKI.PropInfo.dbo.PropInfoXref
SET DistrictId=i.DistrictId
   FROM inserted i
     JOIN its_propertyxref p
ON i.Propid = p.Propid
JOIN LOKI.PROPINFO.dbo.PropInfoXref lp
ON lp.PropId=i.Propid

If @@error<>0 Begin
--print 'failed to update propinfo'
RAISERROR ('Failed to update LOKI.PropInfo.dbo.PropInfoXref from trigger on PropInfoXref table', 1, 1)
ROLLBACK TRAN
GOTO lblFail
END

--///////////////////////////////////////////////
--//Update TelecomCorp..propinfo table on loki
--///////////////////////////////////////////////

UPDATE LOKI.TelecomCorp.dbo.PropInfo
SET District_CId=(select District_CID from LOKI.TelecomCorp.dbo.Districtinfo where DistrictId=i.DistrictId)
FROM inserted i
JOIN its_propertyxref p
ON i.Propid = p.Propid
JOIN LOKI.TelecomCorp.dbo.PropInfo lp
ON lp.PropId=i.Propid

IF @@ERROR<>0 BEGIN
RAISERROR ('Failed to update LOKI.TelecomCorp.dbo.PropInfo from trigger on PropInfoXref table', 1, 1)
ROLLBACK TRAN
GOTO lblFail
END

GOTO lblSuccess
  END
GOTO lblSuccess
END

lblFail:
Print 'Failed to update...'
lblSuccess:
Print 'Update successful...'
--select * into _tmpPropinfo_Loki_02_11_04 from LOKI.PropInfo.dbo.PropInfoXref

set XACT_ABORT OFF

GO