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

as

begin

declare

@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

begin

update LIBRARYBOOKDETAILS set Inventory = Inventory - 1 where BookID= @BookID

commit tran

end

end try

else

begin

raiserror('Books UnAvailable Or Cannot Issue More Than 3 Books',16,-1)

rollback tran

end

end

end

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.

Re: Error in Trigger Using TRY CATCH AND IF ELSE
 

Try this:

Create trigger trg_InsertInBookIssuedDetails on BOOKISSUEDDETAILS

for insert

as

begin

  declare

  @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

    begin

      update LIBRARYBOOKDETAILS set Inventory = Inventory - 1 where BookID= @BookID

      commit tran

    end

  end try

  begin catch

    raiserror('Books UnAvailable Or Cannot Issue More Than 3 Books',16,-1)

    rollback tran

  end catch

end

Also -- Please use [code] tags in the future, not <code>

Re: Error in Trigger Using TRY CATCH AND IF ELSE
 

Thanks for your reply.

But since I am a beginner I have a doubt with RaisError statement.

Can you tell me why are we writing 16,-1?

What does this indicate?

What can be replaced instead of this?

Sorry I am asking so many doubts but I am learning SQL server on my own and I have found this forum very useful specifically for MS SQL SERVER.

Thanks for your reply

Re: Error in Trigger Using TRY CATCH AND IF ELSE
 

The two parameters are severity and state. The severity of the error dictates how MSSQL will handle the error. See http://doc.ddart.net/mssql/sql70/ra-rz_1.htm

Here is a quote:

severity 

Is the user-defined severity level associated with this message. Severity levels from 0 through 18 can be used by any user. Severity levels 19 through 25 are used only by members of the sysadmin fixed server role. For severity levels 19 through 25, the WITH LOG option is required. 

--------------------------------------------------------------------------------



Caution Severity levels 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error log and the application log.

I hope this answers your question and I'm glad you find daniweb useful. Please be sure to start a new thread when you have new questions and mark old threads as solved. It gives the solvers here incentive to help!

Re: Error in Trigger Using TRY CATCH AND IF ELSE
 

Thanks for your prompt reply!!