resume on error in trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

resume on error in trigger

Author

Topic

TallOne
Starting Member

49 Posts

Posted - 2007-11-08 : 09:05:59

Is there any way to commit a transaction to the table when the trigger code fails? Example....

Create TRIGGER dbo.TestTrigger
ON dbo.TestTable
AFTER INSERT,DELETE,UPDATE
AS
BEGIN

BEGIN TRY
--This Will Raise An Error
SELECT convert(smallint, '2003121')
END TRY
BEGIN CATCH
--Is there something I can do here to ignore the error raised by the trigger code and ensure my inserts,deletes or updates are committed?

END CATCH
END
GO

I haven't found anything...
TIA
Jerry

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-08 : 18:18:18

I would think you wouldn't want to ignore errors. The problem is the trigger won't actually fire if an error is raise (like inserting a NULL where it is not allowed) and you error will not get picked up by the trigger at all. I would look into INSTEAD OF triggers for when an error is caused by Insert/Delete/or Update.

TallOne
Starting Member

49 Posts

Posted - 2007-11-08 : 22:10:55

Thanks for the reply. I wouldn't want to ignore the errors. Boss at worked asked me to find a way to force a commit on a transaction regardless of whether a trigger doomed or not. I couldn't find a way. In the end, I offered to defensively program against a trigger error and not enter the trigger code if it would cause an error. I also advised that if there is an error, it's likely that there is a problem elsewhere that needs attention. They don't use triggers and I suspect they don't trust them or want to introduce a possibility of transaction failure as the data is very sensitive. I explained that in my experience, the only problems I've encountered is the deadlock and now with SQL 2005 the try catch can trap the error reporting back to the client or even try it again. Not being an expert in this area, I just wanted to confirm my opinions :) Any additional feedback or comments on this subject are welcome.

Thanks,
Jerry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources