SQLServerPedia

What you’re asking is partially covered in the Books Online topic entitled Rollbacks and Commits in Stored Procedures and Triggers – the article is worth reading if you haven’t looked at it before. Having said that, I’m not a huge fan of the advice provided because it’s rule-of-thumb-esque; it ignores the fact that in some cases business rules are business rules and “imperfect” code needs to be implemented. Theoretically you shouldn’t ROLLBACK within a trigger. So, just like “If it hurts when you do that, don’t do that” if you don’t want the client to see the transaction count message don’t rollback in the trigger. By the way, whatever you do, DO NOT open a nested transaction within the trigger just to make the error go away, i.e. don’t implement a worse practice to buffer the consequences of a not-the-best practice.

Anyway, so far we’re not having any fun…so let’s leverage a cool feature implemented in SQL Server 2005 to solve your problem. Using TRY…CATCH you can custom-tailor your error message like so:

/* Setup my lame-named table and trigger */
IF EXISTS(SELECT * FROM sysobjects where type='U' and name='MyTable')
DROP TABLE MyTable;
GO
CREATE TABLE MyTable([id] int,[desc] nvarchar(500));
GO
IF EXISTS(SELECT * FROM sysobjects where type='T' and name='MyTableTrigger')
DROP TRIGGER MyTableTrigger;
GO
CREATE TRIGGER MyTableTrigger ON MyTable AFTER INSERT
AS
BEGIN
IF (SELECT COUNT(*) FROM MyTable)=2 AND @@TRANCOUNT>0
BEGIN
ROLLBACK TRAN;
-- RAISERROR('This is my triggered error message',16,1);
END
END
GO
/* Open a transaction */
BEGIN TRAN
BEGIN TRY
INSERT INTO MyTable SELECT TOP 2 [id],[name] FROM sysobjects;
END TRY
BEGIN CATCH
IF (SELECT ERROR_NUMBER()) IN(3609,3616)
RAISERROR('This is my coded error message',16,1);
ELSE
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
IF @@TRANCOUNT>0
COMMIT TRAN;

Running that code in SQL Server will return your custom message (remove the comment in the CREATE TRIGGER section to display a different error). So, there are two ways to get what you’re looking for. Oh, and if you’re thinking, “that’s great, but the INSERT is being executed by code” just wrap the INSERT in a stored procedure and have your code call the procedure – you’ll be implementing a certifiable best practice and increasing the likelihood of query plan reuse at the same time.

Hope that helps!