MS SQL

Error in If statement

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

•

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 If statement

Jul 3rd, 2009

I am trying to create Update trigger which should be invoked only if the ReturnedOn column is clicked.

I have used the following code-snippet but it generates the error:

CODING:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
CREATE TRIGGER trg_ForUpdateOnBookIssuedDetails
ON BOOKISSUEDDETAILS
FOR UPDATE
AS
begin
declare @Rows1 int,@Rows2 int
IF(UPDATE(ReturnedOn)
begin
 
IF EXISTS(SELECT 1 FROM INSERTED)
begin
UPDATE nur
SET nur.NumberOfBooksIssued = nur.NumberOfBooksIssued -1
FROM NewUserRegister nur INNER JOIN INSERTED i
ON i.IssuedTo=nur.UserName
 
SET @Rows1 = @@RowCount
 
UPDATE lbd SET Inventory = Inventory +1
FROM LIBRARYBOOKDETAILS lbd INNER JOIN inserted i
ON lbd.BookID = i.BookId
 
 
SET @Rows2 = @@rowcount
 
IF @Rows1 * @rows2 >0
commit tran
else
begin
raiserror('Error Updating the Database',16,-1)
rollback tran
end
end
end
end
create TRIGGER trg_ForUpdateOnBookIssuedDetails
on BOOKISSUEDDETAILS
For update
as
begin
declare @Rows1 int,@Rows2 int
if(update(ReturnedOn)
begin

IF EXISTS(SELECT 1 FROM INSERTED)
begin
update nur
set nur.NumberOfBooksIssued = nur.NumberOfBooksIssued -1
from NewUserRegister nur inner join INSERTED i
on i.IssuedTo=nur.UserName

set @Rows1 = @@RowCount

update lbd set Inventory = Inventory +1
from LIBRARYBOOKDETAILS lbd inner join inserted i
on lbd.BookID = i.BookId


set @Rows2 = @@rowcount

if @Rows1 * @rows2 >0
commit tran
else
begin
raiserror('Error Updating the Database',16,-1)
rollback tran
end
end
end
end

ERROR STATEMENT:

Msg 156, Level 15, State 1, Procedure trg_ForUpdateOnBookIssuedDetails, Line 9
Incorrect syntax near the keyword 'begin'.

I want the trigger to be fired only if the ReturnedOn column is updated, however if anyother column is updated the trigger should not be fired.

Can anyone help me identify the error and changes I should make to rectify this error?

Thanks in advance!!

•

Join Date: Oct 2008

Posts: 2,434

Reputation: adatapost has much to be proud of

Solved Threads: 439

adatapost

Offline

Nearly a Posting Maven

Re: Error in If statement

Jul 5th, 2009

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
CREATE TRIGGER trg_ForUpdateOnBookIssuedDetails
ON BOOKISSUEDDETAILS
FOR UPDATE
AS
begin
declare @Rows1 int,@Rows2 int
IF(UPDATE(ReturnedOn)
begin
 
IF EXISTS(SELECT 1 FROM INSERTED)
begin
UPDATE nur
SET nur.NumberOfBooksIssued = nur.NumberOfBooksIssued -1
FROM NewUserRegister nur INNER JOIN INSERTED i
ON i.IssuedTo=nur.UserName
 
SET @Rows1 = @@RowCount
 
UPDATE lbd SET Inventory = Inventory +1
FROM LIBRARYBOOKDETAILS lbd INNER JOIN inserted i
ON lbd.BookID = i.BookId
 
 
SET @Rows2 = @@rowcount
 
IF @Rows1 * @rows2 >0
commit tran
else
begin
raiserror('Error Updating the Database',16,-1)
rollback tran
end
end
end
end
create TRIGGER trg_ForUpdateOnBookIssuedDetails
on BOOKISSUEDDETAILS
For update
as
begin
declare @Rows1 int,@Rows2 int
if(update(ReturnedOn)
begin

IF EXISTS(SELECT 1 FROM INSERTED)
begin
update nur
set nur.NumberOfBooksIssued = nur.NumberOfBooksIssued -1
from NewUserRegister nur inner join INSERTED i
on i.IssuedTo=nur.UserName

set @Rows1 = @@RowCount

update lbd set Inventory = Inventory +1
from LIBRARYBOOKDETAILS lbd inner join inserted i
on lbd.BookID = i.BookId


set @Rows2 = @@rowcount

if @Rows1 * @rows2 >0
commit tran
else
begin
raiserror('Error Updating the Database',16,-1)
rollback tran
end
end
end
end