Error in Trigger Using TRY CATCH AND IF ELSE

[S2009]

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.

[sknake]

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>

[S2009]

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>

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

[sknake]

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!

[S2009]

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!

Thanks for your prompt reply!!