MS SQL

Help required for UPDATE TRIGGER

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

Help required for UPDATE TRIGGER

Jun 30th, 2009

Hi all,

I have created the Trigger for UPDATING to be invoked when Returnedon column is updated using the following code snnipet.

 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
begin tran
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
CREATE TRIGGER trg_ForUpdateOnBookIssuedDetails
on BOOKISSUEDDETAILS
For update
as begin
declare @Rows1 int,@Rows2 int
if(update(ReturnedOn))

begin
begin tran
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

Now I want To perform this Updation only if the RETURNEDON column is null. If the ReturnedOn column consists of some other value then the Updation of records should not take place.

Can anyone help me out in performing this task?

Please help me out.

Thanks in advance!!

•

Join Date: Dec 2008

Posts: 21

Reputation: NextCom is an unknown quantity at this point

Solved Threads: 0

NextCom

Offline

Newbie Poster

Re: Help required for UPDATE TRIGGER

Jun 30th, 2009

Try to change:
if(update(ReturnedOn))
To:
IF (SELECT ReturnedOn FROM INSERTED) IS NULL

Last edited by peter_budo; Jul 2nd, 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.