RAISERROR, Transaction outcome and execution flow

Remember that when using RAISERROR inside procedures or triggers it does:
- not issue a rollback
- not interrupt the execution

For that results, when desired, use a ROLLBACK command explicitly, and a RETURN to quit from the routine. Do not misunderstand this concepts because it is so easy to think the RAISERROR will interrupt both the transaction and the execution flow.

For more detailed situations, which can apply to your case, browse on Microsoft Technet or MSDN, and google for more informations from the experts on the Internet.

Example:
Using a trigger to show the concept, we can issue any DML statements, receive an error message, and, anyway, the statements will work. No interruption. No rollback after raiserror.

CREATE TRIGGER dbo.trg_dml_testtrans
   ON  dbo.testtrans
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

print '@@trancount: ' + cast( @@trancount as varchar);

    -- Insert statements for trigger here
    raiserror('raising an error...', 16, 10);
    
print '@@trancount: ' + cast( @@trancount as varchar); 
END

insert testtrans (cola, colb) values (1, 'aaa');
insert testtrans (cola, colb) values (2, 'bbb');
update testtrans set colb='ccc' where cola=2
delete testtrans where cola=2

Each of above statements will get the errors message, even though working as expected. See one message example below, for delete statement:

@@trancount: 1
Msg 50000, Level 16, State 10, Procedure trg_dml_testtrans, Line 18
raising an error...
@@trancount: 1

Going further, the following commands show us that the delete statement (and so the insert and update) starts an implicit transaction (or automatic transaction) when executing, then commits on  after successfuly perform (auto commit). That´s because you did not see any transaction statements in the example.

print '@@trancount: ' + cast( @@trancount as varchar);
delete testtrans where cola=3
print '@@trancount: ' + cast( @@trancount as varchar);

@@trancount: 0
@@trancount: 1
Msg 50000, Level 16, State 10, Procedure trg_dml_testtrans, Line 18
raising an error...
@@trancount: 1

(1 row(s) affected)
@@trancount: 0

Try to change the trigger to issue a rollback in some condition and see for yourself how funny is to work with databases. I will give you another example:

ALTER TRIGGER [dbo].[trg_dml_testtrans]
   ON  [dbo].[TestTrans]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
   SET NOCOUNT ON;

   print '@@trancount: ' + cast( @@trancount as varchar);

   if (select colb from inserted) = 'zzz'
   begin
       raiserror('raising an error...', 16, 10);
       rollback;
       --use RETURN here to get out immediately
   end
    
   print '@@trancount: ' + cast( @@trancount as varchar); 
END

insert testtrans (cola, colb) values (7, 'zzz');

@@trancount: 1
Msg 50000, Level 16, State 10, Procedure trg_dml_testtrans, Line 19
raising an error...
@@trancount: 0
Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.

This means that the 'zzz' won't be inserted.

That's all for now, folks!