I have a trigger on a table (source) that data should be copied to the other one (target) in other database. I am trying to implement custom synchronization process for data: I want that target database (table) will be up to date to source database (table). I have 3 hundred tables to synchronize. Some of then have different physical data structure. I cannot use standard approaches of Sql Server (replication, DTS...) because of different data schema and other restrictions (time to implement, environment issues...). My goal is: this trigger should NOT impact on INSERT, DELETE, UPDATE of records in a source table. I tried so solution:
CREATE TRIGGER dbo.MyTrigger ... AFTER INSERT .... BEGIN TRY --RAISERROR('Test error', 16, 2) END TRY BEGIN CATCH -- nothing END CATCH
I have inserted
RAISEERROR to simulate error. I hoped,
try/catch suppressed that error and record was been inserted (deleted, or updated) successfully. Don't. It does not work. I got error:
An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.
Is it possible to realize my solution in this way. How can I catch and hold (suppress) any error in my trigger?
What sense to use
try/catch in trigger if it does not work?