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,
DeliveryTypeDescription varchar(10) not null,
DeliveryCharge smallmoney not null
)

Create Table DeliveryTypeChanges (
ChangeID int identity(1,1) not null
Constraint pk_DeliveryTypeChanges_ChangeID primary key,
ChangeDateTime datetime not null,
DeliveryTypeDescription varchar(10) not null,
OldDeliveryCharge smallmoney not null,
NewDeliveryCharge smallmoney not null
)

CREATE Trigger tr_Update_RecordDeliveryChargeChanges
On DeliveryType
For Update As
declare @rowcount as int
declare @ChangeDateTime as datetime
declare @DeliveryTypeDescription as varchar(10)
declare @OldDeliveryCharge as smallmoney
declare @NewDeliveryCharge as smallmoney
If @@RowCount > 0
Begin
If update (DeliveryCharge)
Begin
begin transaction
select @ChangeDateTime = getdate(),
@DeliveryTypeDescription = Inserted.DeliveryTypeDescription,
@OldDeliveryCharge = Deleted.DeliveryCharge,
@NewDeliveryCharge = Inserted.DeliveryCharge
from Inserted inner join Deleted
on Inserted.DeliveryTypeID = Deleted.DeliveryTypeID
insert DeliveryTypeChanges(ChangeDateTime, DeliveryTypeDescription, OldDeliveryCharge, NewDeliveryCharge)
values (@ChangeDateTime, @DeliveryTypeDescription, @OldDeliveryCharge, @NewDeliveryCharge)
set @rowcount = @@rowcount
if @@rowcount = 1
Begin
Commit transaction
End
Else
Begin
Rollback transaction
RaisError('Error 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,
DeliveryTypeDescription varchar(10) not null,
DeliveryCharge smallmoney not null
)

Create Table DeliveryTypeChanges (
ChangeID int identity(1,1) not null
Constraint pk_DeliveryTypeChanges_ChangeID primary key,
ChangeDateTime datetime not null,
DeliveryTypeDescription varchar(10) not null,
OldDeliveryCharge smallmoney not null,
NewDeliveryCharge smallmoney not null
)

CREATE Trigger tr_Update_RecordDeliveryChargeChanges
On DeliveryType
For Update As
declare @rowcount as int
declare @ChangeDateTime as datetime
declare @DeliveryTypeDescription as varchar(10)
declare @OldDeliveryCharge as smallmoney
declare @NewDeliveryCharge as smallmoney
If @@RowCount > 0
Begin
If update (DeliveryCharge)
Begin
begin transaction
select @ChangeDateTime = getdate(),
@DeliveryTypeDescription = Inserted.DeliveryTypeDescription,
@OldDeliveryCharge = Deleted.DeliveryCharge,
@NewDeliveryCharge = Inserted.DeliveryCharge
from Inserted inner join Deleted
on Inserted.DeliveryTypeID = Deleted.DeliveryTypeID
insert DeliveryTypeChanges(ChangeDateTime, DeliveryTypeDescription, OldDeliveryCharge, NewDeliveryCharge)
values (@ChangeDateTime, @DeliveryTypeDescription, @OldDeliveryCharge, @NewDeliveryCharge)
set @rowcount = @@rowcount
if @@rowcount = 1
Begin
Commit transaction
End
Else
Begin
Rollback transaction
RaisError('Error 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_RecordDeliveryChargeChanges
On DeliveryType
For Update As


If update (DeliveryCharge)
Begin

Insert DeliveryTypeChanges(ChangeDateTime, DeliveryTypeDescription, OldDeliveryCharge, NewDeliveryCharge)
Select getdate(), Inserted.DeliveryTypeDescription,Deleted.DeliveryC harge,Inserted.DeliveryCharge
From Inserted inner join Deleted
on Inserted.DeliveryTypeID = Deleted.DeliveryTypeID

If @@error <> 0
Begin
RaisError('Error 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.