October 22, 2008 at 11:40 pm
ALTER TRIGGER [trg_Transport] ON [dbo].[Transport]
FOR UPDATE
AS
SET NOCOUNT ON
DECLARE @UpdatedRows INT
DECLARE @DeletedRows INT
SELECT @UpdatedRows = count(*) FROM inserted
SELECT @DeletedRows = count(*) FROM Deleted
--Always update into updated table
update Transport
set
Date_Activate = getdate()
FROM inserted I
LEFT JOIN Deleted D ON I.UserID = D.UserID
WHERE CHECKSUM( I.Start_date) <> CHECKSUM(D.Start_date)
October 23, 2008 at 1:02 am
You need to add a join back to Transport based on the primary key to make sure that it only updates the records that match the INSERTED and DELETED tables.
October 23, 2008 at 12:19 pm
Hello,
I think your where clause is not filtering the records you intended.
WHERE CHECKSUM( I.Start_date) <> CHECKSUM(D.Start_date)
The checksum of I.Start_date may match several records in Inserted.
The checksum of D.Start_date can match all the other records in Deleted that
do not have the checksum of I.Start_date...this is probably why it's updating so many
records.
Regards,
Terry
October 24, 2008 at 8:06 pm
Generally, if you want to add or modify any data written or changed in the target table, use an Instead Of trigger:
create TRIGGER [trg_Transport_U] ON [dbo].[Transport]
Instead of UPDATE
AS
SET NOCOUNT ON
update t
set t.col1 = i.col1,
t.col2 = i.col2,
....
t.Date_Activate = getdate()
from dbo.Transport t
join Inserted i
on t.KeyCol = i.KeyCol
Use After (ON) triggers to write to audit and/or history tables.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply