SQL Statement Termination using RAISERROR

sql sql-server-2005 triggers raiserror
Bob picture Bob · Aug 6, 2009 · Viewed 10.3k times · Source

(SQL 2005) Is it possible for a raiserror to terminate a stored proc.

For example, in a large system we've got a value that wasn't expected being entered into a specific column. In an update trigger if you write:

if exists (select * from inserted where testcol = 7) begin raiseerror('My Custom Error', 16, 1) end

the update information is still applied. however if you run

if exists (select * from inserted where testcol = 7) begin select 1/0 end

a divide by 0 error is thrown that actually terminates the update. is there any way i can do this with a raiseerror so i can get custom error messages back?

Answer

KM. picture KM. · Aug 6, 2009

In a trigger, issue a ROLLBACK, RAISERROR and then RETURN.

see Error Handling in SQL Server - Trigger Context by Erland Sommarskog

Related questions

IF UPDATE() in SQL server trigger

If there's: IF UPDATE (col1) ...in the SQL server trigger on a table, does it return true only if col1 has been changed or been updated? I have a regular update query like UPDATE table-name SET col1 = 'x', col2 = 'y' …

Read More
Difference between FOR and AFTER triggers?

What's the difference between FOR and AFTER triggers?

Read More
Asynchronous Triggers in SQL Server 2005/2008

I have triggers that manipulate and insert a lot of data into a Change tracking table for audit purposes on every insert, update and delete. This trigger does its job very well, by using it we are able to log …

Read More