Trigger To Handle Multirow Updates

I am trying to implement trigger which can handle multirow updates and which is running on replicated table. So I want it never fails as trigger failure brakes replication.

So:

CREATE TRIGGER on_person_update

BEGIN

-- create temp table

-- populate temp table with Inserted values (I do not need Deleted as PK never change)

COMMIT TRAN

-- Am I right that this insures updates on replicated table will never be rollback after this commit?

BEGIN TRAN A

-- Make a checkpoint here to be able to rollback at any time to this point if something wrong inside loop.

SAVE TRAN MyTran

-- Start looping in temp table

-- RUN DML statement to make neccesary changes for each record in temp table

-- Does it make any sense to do this (IF @ERR below)? When I am trying in DML insert string value into integer column it never gets to IF statement - terminates straight away.

-- Reason why I think I need it as this trigger might be called by another trigger and top level trigger will get an error and can make a decision based on this.

IF @ERR <> 0
BEGIN
ROLLBACK TRAN MyTran
RAISERROR('Insert or Update failed in on_person_sls_update trigger with error: %s', 16, 1, @ERR)
RETURN
END

-- End looping temp

-- Do I need here COMMIT TRAN A or trigger will make commit anyway?

END

Why all of this?

Data changed on distributor and arrive to subscriber as a transaction.

We have a trigger on replicated table which will update replicated table in any way but after that it will update another database on subscriber.

This trigger should be able to handle multirow updates.

When this trigger updates another database it runs DML which fires other triggers so they become nested, if I am right. Our trigger should always accept changes from distributor as if it fails replication brakes but after data saved in temp table none or all changes have to be made.

May be I am copmpletely wrong with this template - hope somebody will help.

Thank you,



Igor