Unanswered: return value or exception from trigger
Yes, another post. Anyway, my question is is there any way to raise an exception or otherwise notify failure from a trigger. Specifically, consider the following simple trigger:
CREATE TRIGGER [t3] ON [dbo].[PojoList]
instead of insert
declare @Category int
declare @Min int
declare @Max int
declare @Id int
set @Category = (select category from inserted)
set @Min = (select MinCategory from BackOfficeEjbs where id = @Id)
set @Max = (select MaxCategory from BackOfficeEjbs where id = @Id)
if @Category >= @Min and @Category <= @Max
insert into pojolist (id, category, classname)
select id, category, classname from inserted
The idea here is I want to intercept the insert into the PojoList table and test to see if the Category field is valid for the related BackOfficeEjb table's MinCategory and MaxCategory values.
Now, if it fails, then the ROLLBACK TRANSACTION statement runs, and the rowsaffected returned to the client app is -1. If the category is valid, then it returns 2 as the rows afftected.
The problem is, first of all, if it actually performs the insert, I'd want the rows afftected to return 1, not 2 or some other number. It seems the rows afftect is greater then 1 because of the select statements or somthing else going on in the trigger. Anyway, the client programmer would probably interpret the rows affected value of 2 as a problem.
Second, and most importantly, if the evaluation fails and I am not acutally going to perform the insert, I want an exception raised. In other words, I want an oledb exception raised on the client to let them no the the insert failed (incase they weren't checking the rows affected value). Basically, I want it to act as like any other insert operation that fails because of a failed contraint check or missing column or any other syntax error which might cause the insert to fail.
All I'm trying to do logically is perform a test prior to an insert on the table. If the test fails, I want the insert to fail, and I want it to pass that failure to the client. Again, any help would be greatly appreciated. Thanks.
Thanks for the reply - RAISEERROR was exactly what the doctor ordered! Also, yeah, I'll fix the trigger to deal with multiple inserts. Again, thanks a bunch!
Originally posted by Brett Kaiser
As for the count, it could be more, the trigger fires on the insert and the insert could be more than 1 row...so you're SET statements don't make sense...you'll get the laste