Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Denver
    Posts
    6

    Unanswered: return value or exception from trigger

    Hello,

    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
    AS
    begin
    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
    begin
    insert into pojolist (id, category, classname)
    select id, category, classname from inserted
    end
    else
    rollback transaction
    end

    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.

    Tony

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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

    check out:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30842
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Location
    Denver
    Posts
    6
    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!

    Tony


    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

    check out:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30842

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •