Prev: CTE with IF statement
Next: nth line where column not null
From: CharlesL on 8 Apr 2010 10:28
Hello, I'm sure there's a simple solution here, but I can't seem to see the
forest for the trees.....

I have a field at the end of my table called DATE_MODIFIED - basically I
would like to update this fied with the current dat and time when a record
gets inserted or updated - the trigger works good, but it updates all
records in the table, rather than the record being done - how should I fix
this? Here's the trigger code on the table...

USE [TSONLINE]

GO

/****** Object: Trigger [dbo].[tr_Date_Modified] Script Date: 04/08/2010
07:27:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[tr_Date_Modified] ON [dbo].[TIME_ENTRY]

FOR INSERT,UPDATE

AS

Begin

Update dbo.TIME_ENTRY

set DATE_MODIFIED = getdate()

End


From: Plamen Ratchev on 8 Apr 2010 10:45
You have to join to the Inserted virtual table to update only rows that have been updated (note to change keycolumn to
your key column):

UPDATE dbo.TIME_ENTRY
SET DATE_MODIFIED = CURRENT_TIMESTAMP
FROM dbo.TIME_ENTRY AS T
JOIN Inserted AS I
ON T.keycolumn = I.keycolumn;

--
Plamen Ratchev
http://www.SQLStudio.com
From: --CELKO-- on 8 Apr 2010 14:35
I hope that you do not think this is an audit.

Think about what happens to your audit trail when the row is deleted.
Think about how easy it is to falsify that timestamp. This is why
auditors will not let you do this and require that the audit data be
separated from the base data. If you are in a HIPPA or similar
environment, you also have to track access to the data, which you
cannot do with a trigger.

From: m on 9 Apr 2010 21:07
IMHO, true auditing can only be done by the RDBMS itself and are out-of-band
from the data (one of the principal weaknesses of SQL). But simple
constructs, like what the OP has suggested, are surprisingly useful

"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:1f5d3514-7934-44f4-a857-c018ac8986c4(a)i37g2000yqn.googlegroups.com...
> I hope that you do not think this is an audit.
>
> Think about what happens to your audit trail when the row is deleted.
> Think about how easy it is to falsify that timestamp. This is why
> auditors will not let you do this and require that the audit data be
> separated from the base data. If you are in a HIPPA or similar
> environment, you also have to track access to the data, which you
> cannot do with a trigger.
>
 | 
Pages: 1
Prev: CTE with IF statement
Next: nth line where column not null