Insert Trigger Not Working Properly

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

Insert Trigger Not Working Properly

Author

Topic

sital
Yak Posting Veteran

89 Posts

Posted - 2009-06-29 : 01:58:15

Hi

I have created the following Trigger:

ALTER trigger trg_InsertInBookIssuedDetails on BOOKISSUEDDETAILS
for insert
as begin
declare
@UserName nvarchar(50),
@BookID int
BEGIN TRY
BEGIN TRAN
select @UserName = IssuedTo,@BookID = BookID from BOOKISSUEDDETAILS
update NewUserRegister set NumberOfBooksIssued = NumberOfBooksIssued+1 where UserName = @UserName AND NumberOfBooksIssued < 3
update LIBRARYBOOKDETAILS set Inventory = Inventory - 1 where BookID= @BookID AND Inventory > 1
COMMIT TRAN
END TRY
BEGIN CATCH
RAISERROR('BOOKS UNAVAILABLE OR CANNOT BORROW MORE THAN 3 BOOKS',16,-1)
ROLLBACK TRAN
END CATCH
end

Now when I am inserting the records The First Update statement is working properly, but the second Update statement does not satisfy the condition hence I dont want to commit even the first statement, but When I insert a record I find that the First Update is working and the Second Update is not working.

But I want either both to be executed or none of them should be executed. Hence I used TRANSACTION, but I find it is not working properly.

Can anybody identify the error?

Please help me out.

Thanks in advance!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-29 : 11:42:06

do you mean this?


ALTER trigger trg_InsertInBookIssuedDetails on BOOKISSUEDDETAILS
for insert
as begin
declare @Rows1 int,@Rows2 int
BEGIN TRAN
update  nur
set nur.NumberOfBooksIssued = nur.NumberOfBooksIssued+1
FROM NewUserRegister nur
INNER JOIN INSERTED i
ON i.IssuedTo =nur.UserName 
WHERE nur.NumberOfBooksIssued < 3

SET @Rows1=@@ROWCOUNT

update lbd
set Inventory = Inventory - 1 
FROM LIBRARYBOOKDETAILS lbd
INNER JOIN INSERTED i
ON lbd.BookID= i.BookID 
AND lbd.Inventory > 1 

SET @Rows2=@@ROWCOUNT

IF @Rows1 * @Rows2 >0
 COMMIT TRAN
ELSE 
BEGIN
RAISERROR('BOOKS UNAVAILABLE OR CANNOT BORROW MORE THAN 3 BOOKS',16,-1)
ROLLBACK TRAN
END 
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources