I have a primary table on which I need to create an "after insert" trigger. The trigger will insert selected fields from the primary insert on the primary table into a secondary table. The trigger works, but I must absolutely eliminate any chance that a failed trigger blocks inserts into the primary table.
I constructed a trigger using the following, just to see if was bullet proof. It is not.
create trigger mytrigger on table_w_trigger
insert into secondtable (field1, field2) select field1, field2 from INSERTED
I tested it by renaming secondtable to something that does not exist. This blocked inserts into the primary table. Renaming secondtable to its correct name, unblocked inserts and the trigger started working.
I'll work on getting the catch block to do something useful once I sort this out, but why did the trigger crash, and how can I use try/catch to make it bullet proof.
Thanks in advance.