MS SQL

Error in Trigger Using TRY CATCH AND IF ELSE

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Jun 2009

Posts: 67

Reputation: S2009 is an unknown quantity at this point

Solved Threads: 0

S2009

Offline

Junior Poster in Training

Error in Trigger Using TRY CATCH AND IF ELSE

Jun 28th, 2009

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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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.

Last edited by peter_budo; Jun 28th, 2009 at 4:55 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Feb 2009

Posts: 3,172

Reputation: sknake has much to be proud of

Solved Threads: 565

sknake

Offline

.NET Enthusiast

Re: Error in Trigger Using TRY CATCH AND IF ELSE

Jun 28th, 2009

Try this:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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>

Last edited by sknake; Jun 28th, 2009 at 3:12 am.

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Jun 2009

Posts: 67

Reputation: S2009 is an unknown quantity at this point

Solved Threads: 0

S2009

Offline

Junior Poster in Training

Re: Error in Trigger Using TRY CATCH AND IF ELSE

Jun 30th, 2009

•

Originally Posted by sknake

Try this:
Help with Code Tags

sql Syntax (Toggle Plain Text)
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
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

•

Join Date: Feb 2009

Posts: 3,172

Reputation: sknake has much to be proud of

Solved Threads: 565

sknake

Offline

.NET Enthusiast

Re: Error in Trigger Using TRY CATCH AND IF ELSE

Jun 30th, 2009

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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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.
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!

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Jun 2009

Posts: 67

Reputation: S2009 is an unknown quantity at this point

Solved Threads: 0

S2009

Offline

Junior Poster in Training

Re: Error in Trigger Using TRY CATCH AND IF ELSE

Jun 30th, 2009

•

Originally Posted by 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:
Help with Code Tags

MS SQL Syntax (Toggle Plain Text)
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.
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!