Trigger error handling-- try catch does not work for after insert triggers?

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
after insert
begin try
   insert into secondtable (field1, field2) select field1, field2 from INSERTED

begin catch
end catch

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.
Who is Participating?
Scott PletcherConnect With a Mentor Senior DBACommented:
TRY/CATCH does not catch all types of errors.  For example, a "table does not exist" error ends the current batch immediately.  You can see that using in-line code:

begin try
insert into dbo.no_such_table values('test')
print 'didn't catch the error!' --note that this doesn't print
end try
begin catch
print 'caught the error!' --note that this doesn't print either
end catch

However, that should only force the batch to rollback, not block future inserts.

But not specifying the schema name on a table does force SQL to take some exclusive locks before processing the query.  Maybe that is causing some type of blocking.  Try specifying schema names on all tables used in triggers -- that is the proper way to code any trigger anyway.
Kyle AbrahamsSenior .Net DeveloperCommented:
not sure why that wouldn't work,

but you could add an

if exists(secondtable)

condition to ensure the table is there before inserting.

A stab:

but are you using at try catch in your main inserta as well?

if so:
"A TRY…CATCH construct cannot span multiple batches. A TRY…CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY…CATCH construct cannot span two BEGIN…END blocks of Transact-SQL statements and cannot span an IF…ELSE construct."
dakota5Author Commented:
I was using a bad table name as an example of something sure to make the trigger fail.  I don't want to add in specific if clauses for different types of errors.  I want something that will keep any and all errors in the trigger from affecting the main table inserts.

As for spanning blocks, no, as you can see from the procedure, the Try/CATCH is not spanning multiple blocks.

I wonder if Try/Catch does not work for triggers.
The same table non-existence error also is not caught by select statements.  The main technet article on try-catch points this out.

Wonder what people do for triggers to make them bullet proof.
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
try catches do work for sql triggers.

Are you calling this from a stored proc or something.

if you just try

insert into test2GuaranteedwontBeThere select 1

it throws an error in the query editor.  It will compile as a stored proc though and throw the error on execution.

with regards to the 2 blocks:

begin try
  insert into table 1  -- invoke trigger here
end try
begin catch
end catch

and then in the trigger also doing a begin try I'm wondering if that is what's causing your issue.

Having an if statement to ensure a table exists before an insert is a sure fire way to make it bullet proof as you say.  You don't need an if for every kind of error.

Just as a sample


declare @id int
set @id = 1 / 0

and see if the catch fires.
dakota5Author Commented:
Good answers.  The Technet entry is dense, but has these details.  Somewhat hard to follow.

Compile errors (syntax) and statement-level recompile errors (like object does not exist) are not trapped by Try/Catch.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.