Ad:

MS SQL Discussion Thread
Marked Solved
Views: 2004

Jun 28th, 2009

Error in Trigger Using TRY CATCH AND IF ELSE

Expand Post »

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:

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.

Similar Threads

Imaginary Error (PHP bug?) in PHP
NullPointerException: null (in sun.misc.Floating Decimal) Error? in Java
nullpointerexception error in Java
Error In trigger. in MS SQL
Error In trigger. in VB.NET

S2009

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

67 posts
since Jun 2009

Permalink

Jun 28th, 2009

Re: Error in Trigger Using TRY CATCH AND IF ELSE

Try this:

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.

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Jun 30th, 2009

Re: Error in Trigger Using TRY CATCH AND IF ELSE

Click to Expand / Collapse Quote originally posted by sknake ...

Try this:
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

S2009

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

67 posts
since Jun 2009

Permalink

Jun 30th, 2009

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:

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!

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Jun 30th, 2009

Re: Error in Trigger Using TRY CATCH AND IF ELSE

Click to Expand / Collapse Quote 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:
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!

Thanks for your prompt reply!!

S2009

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

67 posts
since Jun 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: SQL Query takes lot of time

Next Thread in MS SQL Forum Timeline: Help required for UPDATE TRIGGER

DaniWeb Message

Cancel Changes

User Name
Password