Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009

    Unanswered: Error in Trigger Using TRY CATCH AND IF ELSE

    Hi all,

    I am creating a trigger which should be triggered on insert to BOOKISSUEDETAILS table. I want to alter the table LIBRARYBOOKDETAILS table only if the Inventory column value is greater than 1.

    Initially I have created the trigger in the following way. But now I want to alter the Trigger to include the condition(Inventory > 1) and use the Try catch block to raise error.

    I used the following code snippet to alter the trigger, but it is generating the error:

    alter trigger trg_InsertInBookIssuedDetails on BOOKISSUEDDETAILS
    for insert
    @UserName nvarchar(50),
    @BookID int,
    @ReturnMessage nvarchar(50)
    begin try
    begin tran
    select @UserName = IssuedTo,@BookID = BookID from BOOKISSUEDDETAILS
    update NewUserRegister set NumberOfBooksIssued = NumberOfBooksIssued+1 where UserName = @UserName
    if (select Inventory from LIBRARYBOOKDETAILS WHERE BookID = @BookID)>1
    update LIBRARYBOOKDETAILS set Inventory = Inventory - 1 where BookID= @BookID
    commit tran
    end try
    raiserror('Books UnAvailable Or Cannot Issue More Than 3 Books',16,-1)
    rollback tran

    Error Message:

    Msg 156, Level 15, State 1, Procedure trg_InsertInBookIssuedDetails, Line 19
    Incorrect syntax near the keyword 'else'.
    Msg 156, Level 15, State 1, Procedure trg_InsertInBookIssuedDetails, Line 25
    Incorrect syntax near the keyword 'end'.

    Can anyone let me know the error? and the changes I should make to perform the desired task with condition.

    Please help me out in identifying the error.

    Thanks in advance.

  2. #2
    Join Date
    Dec 2007
    London, UK
    There are a few things wrong here. Firstly you are querying BOOKISSUEDDETAILS directly. That won't tell you which rows are inserted. You need to refer to the Inserted table for that.

    Secondly, triggers need to perform correctly whether zero, one or many rows are updated. Yours doesn't. It just assigns some values from ONE arbitrary row to variables.

    I suggest you look at the examples under CREATE TRIGGER in Books Online and make sure you understand the syntax.

    Finally, why not implement the Inventory > 1 rule as a constraint, rather than create a trigger for it. Use triggers only when it's essential to do so. A constraint is much more appropriate here.

Posting Permissions

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