help needed with update trigger in SQL Server 2005

[jdprime]

Hi,
I am trying to write a trigger that will run when someone updates the delivery charge within the specified table. It will work, but will only record one change to the table. My question is, if multiple records are changes with an update, how do I get the trigger to record all the changes, instead of one?
Thank You,
Jason


Create Table DeliveryType (
DeliveryTypeID int not null
Constraint pk_DeliveryType _DeliveryTypeID primary key,
DeliveryTypeDes cription varchar(10) not null,
DeliveryCharge smallmoney not null
)

Create Table DeliveryTypeCha nges (
ChangeID int identity(1,1) not null
Constraint pk_DeliveryType Changes_ChangeI D primary key,
ChangeDateTime datetime not null,
DeliveryTypeDes cription varchar(10) not null,
OldDeliveryChar ge smallmoney not null,
NewDeliveryChar ge smallmoney not null
)

CREATE Trigger tr_Update_Recor dDeliveryCharge Changes
On DeliveryType
For Update As
declare @rowcount as int
declare @ChangeDateTime as datetime
declare @DeliveryTypeDe scription as varchar(10)
declare @OldDeliveryCha rge as smallmoney
declare @NewDeliveryCha rge as smallmoney
If @@RowCount > 0
Begin
If update (DeliveryCharge )
Begin
begin transaction
select @ChangeDateTime = getdate(),
@DeliveryTypeDe scription = Inserted.Delive ryTypeDescripti on,
@OldDeliveryCha rge = Deleted.Deliver yCharge,
@NewDeliveryCha rge = Inserted.Delive ryCharge
from Inserted inner join Deleted
on Inserted.Delive ryTypeID = Deleted.Deliver yTypeID
insert DeliveryTypeCha nges(ChangeDate Time, DeliveryTypeDes cription, OldDeliveryChar ge, NewDeliveryChar ge)
values (@ChangeDateTim e, @DeliveryTypeDe scription, @OldDeliveryCha rge, @NewDeliveryCha rge)
set @rowcount = @@rowcount
if @@rowcount = 1
Begin
Commit transaction
End
Else
Begin
Rollback transaction
RaisError('Erro r recording to change table, contact admin', 16, 1)
End
End
End
Return

[Derftics]

Hi,
I am trying to write a trigger that will run when someone updates the delivery charge within the specified table. It will work, but will only record one change to the table. My question is, if multiple records are changes with an update, how do I get the trigger to record all the changes, instead of one?
Thank You,
Jason


Create Table DeliveryType (
DeliveryTypeID int not null
Constraint pk_DeliveryType _DeliveryTypeID primary key,
DeliveryTypeDes cription varchar(10) not null,
DeliveryCharge smallmoney not null
)

Create Table DeliveryTypeCha nges (
ChangeID int identity(1,1) not null
Constraint pk_DeliveryType Changes_ChangeI D primary key,
ChangeDateTime datetime not null,
DeliveryTypeDes cription varchar(10) not null,
OldDeliveryChar ge smallmoney not null,
NewDeliveryChar ge smallmoney not null
)

CREATE Trigger tr_Update_Recor dDeliveryCharge Changes
On DeliveryType
For Update As
declare @rowcount as int
declare @ChangeDateTime as datetime
declare @DeliveryTypeDe scription as varchar(10)
declare @OldDeliveryCha rge as smallmoney
declare @NewDeliveryCha rge as smallmoney
If @@RowCount > 0
Begin
If update (DeliveryCharge )
Begin
begin transaction
select @ChangeDateTime = getdate(),
@DeliveryTypeDe scription = Inserted.Delive ryTypeDescripti on,
@OldDeliveryCha rge = Deleted.Deliver yCharge,
@NewDeliveryCha rge = Inserted.Delive ryCharge
from Inserted inner join Deleted
on Inserted.Delive ryTypeID = Deleted.Deliver yTypeID
insert DeliveryTypeCha nges(ChangeDate Time, DeliveryTypeDes cription, OldDeliveryChar ge, NewDeliveryChar ge)
values (@ChangeDateTim e, @DeliveryTypeDe scription, @OldDeliveryCha rge, @NewDeliveryCha rge)
set @rowcount = @@rowcount
if @@rowcount = 1
Begin
Commit transaction
End
Else
Begin
Rollback transaction
RaisError('Erro r recording to change table, contact admin', 16, 1)
End
End
End
Return

Hi,

Theres no problem with your script. I tried the multiple update in my database and found no problem with your trigger.

[iburyak]

Try this trigger instead:

[PHP]CREATE Trigger tr_Update_Recor dDeliveryCharge Changes
On DeliveryType
For Update As


If update (DeliveryCharge )
Begin

Insert DeliveryTypeCha nges(ChangeDate Time, DeliveryTypeDes cription, OldDeliveryChar ge, NewDeliveryChar ge)
Select getdate(), Inserted.Delive ryTypeDescripti on,Deleted.Deli veryCharge,Inse rted.DeliveryCh arge
From Inserted inner join Deleted
on Inserted.Delive ryTypeID = Deleted.Deliver yTypeID

If @@error <> 0
Begin
RaisError('Erro r recording to change table, contact admin', 16, 1)
End
End[/PHP]

There is no point to have a transaction for one statement so I removed it.

This way it does record multiple updates and return informative error message in case of an error.
The way you did it - you stored values in variables before insert which can hold one value at a time.

Good Luck.
Irina.

[Krishna Acharya]

In SQL Server you need to loop all rows (using INSERTED) and process them.