Error in Trigger Using TRY CATCH AND IF ELSE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error in Trigger Using TRY CATCH AND IF ELSE

Author

Topic

sital
Yak Posting Veteran

89 Posts

Posted - 2009-06-28 : 01:38:55

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-28 : 12:51:50

seems like what you need is this


alter trigger trg_InsertInBookIssuedDetails on BOOKISSUEDDETAILS
for insert
as 
begin
begin tran

update nur 
set nur.NumberOfBooksIssued = nur.NumberOfBooksIssued+1 
FROM NewUserRegister nur
INNER JOIN INSERTED i
ON i.UserName = nur.UserName
if exists(select 1 
          from LIBRARYBOOKDETAILS lbd
          INNER JOIN INSERTED i
          ON i.BookID = lbd.BookID
          WHERE lbd.Inventory<=1)
begin
raiserror('Books UnAvailable Or Cannot Issue More Than 3 Books',16,-1)
rollback tran	
end
else
begin
update lbd 
set lbd.Inventory = lbd.Inventory - 1 
FROM LIBRARYBOOKDETAILS lbd
INNER JOIN INSERTED i
ON lbd.BookID= i.BookID
commit tran
end
end

sital
Yak Posting Veteran

89 Posts

Posted - 2009-06-29 : 00:10:05

quote:

Originally posted by visakh16

seems like what you need is this


alter trigger trg_InsertInBookIssuedDetails on BOOKISSUEDDETAILS
for insert
as 
begin
begin tran

update nur 
set nur.NumberOfBooksIssued = nur.NumberOfBooksIssued+1 
FROM NewUserRegister nur
INNER JOIN INSERTED i
ON i.UserName = nur.UserName
if exists(select 1 
          from LIBRARYBOOKDETAILS lbd
          INNER JOIN INSERTED i
          ON i.BookID = lbd.BookID
          WHERE lbd.Inventory<=1)
begin
raiserror('Books UnAvailable Or Cannot Issue More Than 3 Books',16,-1)
rollback tran	
end
else
begin
update lbd 
set lbd.Inventory = lbd.Inventory - 1 
FROM LIBRARYBOOKDETAILS lbd
INNER JOIN INSERTED i
ON lbd.BookID= i.BookID
commit tran
end
end

Thanks a lot for your reply, but the code given by you is generating the following error:

Msg 207, Level 16, State 1, Procedure trg_InsertInBookIssuedDetails, Line 11
Invalid column name 'UserName'.

For your reference I am including the Columns in various table.

Table Name : NEWUSERREGISTER
COLUMNS: UserID,UserName,Password,DateOfJoining,NumberOfBooksIssued

TABLE NAME: LIBRARYBOOKDETAILS
COLUMNS :BookID,Title,CategoryName,AuthorName,Inventory,IssueStatus

TABLE NAME :BOOKISSUEDDETAILS
COLUMNS:Library_BookIssuedID,BookID,IssuedOn,IssuedTo,ReturnedOn

Can anybody find out the error?

Please help me out!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-29 : 11:30:05

[code]
alter trigger trg_InsertInBookIssuedDetails on BOOKISSUEDDETAILS
for insert
as
begin
begin tran

update nur
set nur.NumberOfBooksIssued = nur.NumberOfBooksIssued+1
FROM NewUserRegister nur
INNER JOIN INSERTED i
ON i.IssuedTo = nur.UserName
if exists(select 1
from LIBRARYBOOKDETAILS lbd
INNER JOIN INSERTED i
ON i.BookID = lbd.BookID
WHERE lbd.Inventory<=1)
begin
raiserror('Books UnAvailable Or Cannot Issue More Than 3 Books',16,-1)
rollback tran
end
else
begin
update lbd
set lbd.Inventory = lbd.Inventory - 1
FROM LIBRARYBOOKDETAILS lbd
INNER JOIN INSERTED i
ON lbd.BookID= i.BookID
commit tran
end
end
[/code]

sital
Yak Posting Veteran

89 Posts

Posted - 2009-06-30 : 01:20:44

quote:

Originally posted by visakh16


alter trigger trg_InsertInBookIssuedDetails on BOOKISSUEDDETAILS
for insert
as 
begin
begin tran

update nur 
set nur.NumberOfBooksIssued = nur.NumberOfBooksIssued+1 
FROM NewUserRegister nur
INNER JOIN INSERTED i
ON i.IssuedTo = nur.UserName
if exists(select 1 
          from LIBRARYBOOKDETAILS lbd
          INNER JOIN INSERTED i
          ON i.BookID = lbd.BookID
          WHERE lbd.Inventory<=1)
begin
raiserror('Books UnAvailable Or Cannot Issue More Than 3 Books',16,-1)
rollback tran	
end
else
begin
update lbd 
set lbd.Inventory = lbd.Inventory - 1 
FROM LIBRARYBOOKDETAILS lbd
INNER JOIN INSERTED i
ON lbd.BookID= i.BookID
commit tran
end
end

Thanks for your reply. Now it is working fine.

Can you please explain me what does this query in the Trigger mean?

select 1 from LIBRARYBOOKDETAILS lbd
INNER JOIN INSERTED i
ON i.BookID = lbd.BookID
WHERE lbd.Inventory<=1

Why are we writing as select 1? What does that 1 indicate?

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 13:06:41

it just checks whether there's atleast 1 record which is updated currently having a Inventory value of <=1

sital
Yak Posting Veteran

89 Posts

Posted - 2009-06-30 : 13:15:21

quote:
Originally posted by visakh16

it just checks whether there's atleast 1 record which is updated currently having a Inventory value of <=1

Ok Thanks for your reply.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 13:20:30

welcome

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-30 : 13:22:40

Just to be clear...
the "1" in "SELECT 1" doesn't have anything to do with making sure there is at least 1 row. The SELECT list is meaningless here. You can select anything like "SELECT 'sital'". The fact that it is in the context of an EXISTS clause is the reason that it is making sure that at least 1 row exists.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources