Error in If statement

Expand Post »

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:

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!!

Similar Threads

Problem in SQL statement in VB.NET.. Assistance Required. in VB.NET
VB Error's I don't know in VB.NET
if statement > from1 read from fields.. in C#
if statement with dates in PHP
Syntax error in INSERT INTO statement in Java

S2009

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

67 posts
since Jun 2009

Permalink

Jul 5th, 2009

Re: Error in If statement

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

Line # 27 - begin is missing

Last edited by adatapost; Jul 5th, 2009 at 2:50 am.

adatapost

Moderator

Reputation Points: 2136

Solved Threads: 1228

Posting Genius

Offline

6,527 posts
since Oct 2008

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: Generate Alpha-Numeric Values in SQL SERVER

Next Thread in MS SQL Forum Timeline: How do i sendt Text and HTML with sp_send_dbmail?

DaniWeb Message

Cancel Changes

User Name
Password