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

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.
dakota5Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:

http://technet.microsoft.com/en-us/library/ms175976.aspx
"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."
0
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.
http://technet.microsoft.com/en-us/library/ms179296%28v=sql.100%29.aspx


Wonder what people do for triggers to make them bullet proof.
0
Kyle AbrahamsSenior .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

try

declare @id int
set @id = 1 / 0

and see if the catch fires.
0
Scott PletcherSenior 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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

http://technet.microsoft.com/en-us/library/ms179296%28v=sql.105%29.aspx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.