Search code examples
sqlsql-servertriggerslinked-server

TOP 100 INSERT INTO linked server from local table then delete the inserted rows in local table?

Anybody know how to do this? Right now I created a Trigger on the local table:

CREATE TRIGGER TRG_AUD_DEL
ON [LocalTable]
FOR DELETE
AS
    INSERT INTO [LinkedServerTable] (columns, columns2)
        SELECT
            (Columns, Columns2)
        FROM DELETED

Then I will execute a query like:

DELETE TOP 100 from [LocalTable]

However my issue is, what will happen if the insert into doesn't work? Because of an time out for instance or the linked server being down. Will the record in the local table still be deleted? (Which I hope it will not)

Solution

  • If a trigger fails, the entire transaction is rolled back. This means that if the INSERT to the linked server within the trigger fails, the DELETE will be rolled back as well and the rows with not be deleted.