Help Required for UPDATE TRIGGER

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

Help Required for UPDATE TRIGGER

Author

Topic

sital
Yak Posting Veteran

89 Posts

Posted - 2009-06-30 : 07:17:03

Hi all,

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

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-30 : 07:28:47

You can add a check if i.ReturnedOn is not null to both update statements but the consequence in some cases will be that your raiserror is starting up because no updated rows affected.

No, you're never too old to Yak'n'Roll if you're too young to die.

sital
Yak Posting Veteran

89 Posts

Posted - 2009-06-30 : 09:27:41

quote:
Originally posted by webfred

You can add a check if i.ReturnedOn is not null to both update statements but the consequence in some cases will be that your raiserror is starting up because no updated rows affected.

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks for your prompt reply.

As you told I have just included if i.ReturnedOn is not null included in both update statements but I am getting the following error:

ERROR STATEMENT :

Msg 4104, Level 16, State 1, Procedure trg_ForUpdateOnBookIssuedDetails, Line 10
The multi-part identifier "i.ReturnedOn" could not be bound.
Msg 4104, Level 16, State 1, Procedure trg_ForUpdateOnBookIssuedDetails, Line 18
The multi-part identifier "i.ReturnedOn" could not be bound.

For your reference I am including the Coding:

ALTER TRIGGER trg_ForUpdateOnBookIssuedDetails
on BOOKISSUEDDETAILS
For update
as begin
declare @Rows1 int,@Rows2 int
if(update(ReturnedOn))

begin
begin tran
if(i.ReturnedOn is not null)
begin
update nur
set nur.NumberOfBooksIssued = nur.NumberOfBooksIssued -1
from NewUserRegister nur inner join INSERTED i
on i.IssuedTo=nur.UserName
end
set @Rows1 = @@RowCount
if(i.ReturnedOn is not null)
begin
update lbd set Inventory = Inventory +1
from LIBRARYBOOKDETAILS lbd inner join
inserted i on lbd.BookID = i.BookId
end
set @Rows2 = @@rowcount

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

Please help me out in recognising the error and what should I do to overcome the Error and get the desired task done.

Please somebody help me out!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-30 : 10:21:59

Hello sital,
it was a simple misunderstanding.
I mean this:

ALTER TRIGGER trg_ForUpdateOnBookIssuedDetails 
on BOOKISSUEDDETAILS
For update
as begin
declare @Rows1 int,@Rows2 int
if(update(ReturnedOn))

begin
begin tran
--if(i.ReturnedOn is not null)
--begin
update nur
set nur.NumberOfBooksIssued = nur.NumberOfBooksIssued -1
from NewUserRegister nur inner join INSERTED i
on i.IssuedTo=nur.UserName and
   i.ReturnedOn is not null
--end
set @Rows1 = @@RowCount
--if(i.ReturnedOn is not null)
--begin
update lbd set Inventory = Inventory +1
from LIBRARYBOOKDETAILS lbd inner join inserted i 
on lbd.BookID = i.BookId and
   i.ReturnedOn is not null
--end
set @Rows2 = @@rowcount

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

But please remember: the consequence in some cases will be that your raiserror is starting up because no updated rows affected.

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

sital
Yak Posting Veteran

89 Posts

Posted - 2009-06-30 : 10:54:11

quote:

Originally posted by webfred

Hello sital,
it was a simple misunderstanding.
I mean this:

ALTER TRIGGER trg_ForUpdateOnBookIssuedDetails 
on BOOKISSUEDDETAILS
For update
as begin
declare @Rows1 int,@Rows2 int
if(update(ReturnedOn))

begin
begin tran
--if(i.ReturnedOn is not null)
--begin
update nur
set nur.NumberOfBooksIssued = nur.NumberOfBooksIssued -1
from NewUserRegister nur inner join INSERTED i
on i.IssuedTo=nur.UserName and
   i.ReturnedOn is not null
--end
set @Rows1 = @@RowCount
--if(i.ReturnedOn is not null)
--begin
update lbd set Inventory = Inventory +1
from LIBRARYBOOKDETAILS lbd inner join inserted i 
on lbd.BookID = i.BookId and
   i.ReturnedOn is not null
--end
set @Rows2 = @@rowcount

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

But please remember: the consequence in some cases will be that your raiserror is starting up because no updated rows affected.

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks for your prompt reply.

I just altered the trigger with the statements given by you. But still the desired task is not achieved. What should I do now?

I am not getting the output as I desired.

Evenif the ReturnedOn column contains the value the trigger is performing the updation.

I want the update trigger to be invoked only if the RETURNEDON column is null(does not contain any value).

Please help me out.

I am in urgent need to complete this task.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 12:57:36

wont this be enough?


ALTER TRIGGER trg_ForUpdateOnBookIssuedDetails 
on BOOKISSUEDDETAILS
For update
as 
begin
declare @Rows1 int,@Rows2 int
IF EXISTS(SELECT 1 FROM INSERTED WHERE ReturnedOn is not null)
begin
update nur
set nur.NumberOfBooksIssued = nur.NumberOfBooksIssued -1
from NewUserRegister nur inner join INSERTED i
on i.IssuedTo=nur.UserName 
and   i.ReturnedOn is not null

set @Rows1 = @@RowCount

update lbd set Inventory = Inventory +1
from LIBRARYBOOKDETAILS lbd inner join inserted i 
on lbd.BookID = i.BookId and
   i.ReturnedOn is not null

set @Rows2 = @@rowcount

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

sital
Yak Posting Veteran

89 Posts

Posted - 2009-06-30 : 13:46:07

quote:

Originally posted by visakh16

wont this be enough?


ALTER TRIGGER trg_ForUpdateOnBookIssuedDetails 
on BOOKISSUEDDETAILS
For update
as 
begin
declare @Rows1 int,@Rows2 int
IF EXISTS(SELECT 1 FROM INSERTED WHERE ReturnedOn is not null)
begin
update nur
set nur.NumberOfBooksIssued = nur.NumberOfBooksIssued -1
from NewUserRegister nur inner join INSERTED i
on i.IssuedTo=nur.UserName 
and   i.ReturnedOn is not null

set @Rows1 = @@RowCount

update lbd set Inventory = Inventory +1
from LIBRARYBOOKDETAILS lbd inner join inserted i 
on lbd.BookID = i.BookId and
   i.ReturnedOn is not null

set @Rows2 = @@rowcount

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

No still I am not able to do it.

All the Commands are executed successfully but still I am not able to update it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 13:48:14

ah sorry i think i had it other way around


ALTER TRIGGER trg_ForUpdateOnBookIssuedDetails 
on BOOKISSUEDDETAILS
For update
as 
begin
declare @Rows1 int,@Rows2 int
IF EXISTS(SELECT 1 FROM INSERTED WHERE ReturnedOn is null)
begin
update nur
set nur.NumberOfBooksIssued = nur.NumberOfBooksIssued -1
from NewUserRegister nur inner join INSERTED i
on i.IssuedTo=nur.UserName 
and   i.ReturnedOn is null

set @Rows1 = @@RowCount

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

set @Rows2 = @@rowcount

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

sital
Yak Posting Veteran

89 Posts

Posted - 2009-07-01 : 00:17:37

quote:

Originally posted by visakh16

ah sorry i think i had it other way around


ALTER TRIGGER trg_ForUpdateOnBookIssuedDetails 
on BOOKISSUEDDETAILS
For update
as 
begin
declare @Rows1 int,@Rows2 int
IF EXISTS(SELECT 1 FROM INSERTED WHERE ReturnedOn is null)
begin
update nur
set nur.NumberOfBooksIssued = nur.NumberOfBooksIssued -1
from NewUserRegister nur inner join INSERTED i
on i.IssuedTo=nur.UserName 
and   i.ReturnedOn is null

set @Rows1 = @@RowCount

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

set @Rows2 = @@rowcount

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

Thanks for your reply.

Even now it is not working well!!

I have a null value for ReturnedOn column but now while updating the table I am getting the following error :

(1)row affected

(1) row affected

Transaction has ended in Trigger. The batch has been aborted.

Though I am receiving the above message I find the update trigger is working exactly the way I want it.

But I don't know why this error message is displayed.

What should I do to overcome this error?

Why am I getting the error?

Please help me out!

Thanks in advance!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-01 : 04:27:05

See history of this thread: the BEGIN TRANS has been lost.

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources