how can I add a time stamp on a table

How can I know when a record on a table has been modified ?
I want to add a field and fill it with a date/time when the recors is modified
ThanksThe only way I know is to use a trigger (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp) to update the column.

-PatP|||Take a look at Lumigent Log Explorer. It allows you to peep into transaction logs to find out who did what when.|||Date and Time Functions
These scalar functions perform an operation on a date and time input value and return a string, numeric, or date and time value.

This table lists the date and time functions and their determinism property. For more information about function determinism, see Deterministic and Nondeterministic Functions.

Function Determinism
DATEADD Deterministic
DATEDIFF Deterministic
DATENAME Nondeterministic
DATEPART Deterministic except when used as DATEPART (dw, date). dw, the weekday datepart, depends on the value set by SET DATEFIRST, which sets the first day of the week.
DAY Deterministic
GETDATE Nondeterministic
GETUTCDATE Nondeterministic
MONTH Deterministic
YEAR Deterministic

See Also

Functions

1988-2000 Microsoft Corporation. All Rights Reserved.|||USE Northwind
GO

CREATE TABLE myTable99(
Col1 int IDENTITY(1,1) NOT NULL PRIMARY KEY
, Col2 char(1)
, ADD_TS datetime DEFAULT GetDate()
, ADD_BY varchar(255) DEFAULT System_User)
GO

-- OK We don't know who did what when, except when it was added

INSERT INTO myTable99(Col2)
SELECT 'A'

SELECT * FROM myTable99

UPDATE myTable99
SET Col2 = 'B'
WHERE Col1 = 1

SELECT * FROM myTable99
OK

-- OK Lets see what we can do
-- Alter the table to track the updates

ALTER TABLE myTable99 ADD UPDATE_TS datetime
GO

ALTER TABLE myTable99 ADD UPDATE_BY varchar(255)
GO

-- Set up a trigger to do the work

CREATE TRIGGER myTrigger99 ON myTable99
FOR UPDATE
AS
BEGIN
UPDATE m
SET UPDATE_BY = System_User
, UPDATE_TS = GetDate()
FROM myTable99 m
INNER JOIN inserted i
ON i.Col1 = m.Col1
END
GO

-- viola

INSERT INTO myTable99(Col2)
SELECT 'C'

SELECT * FROM myTable99

UPDATE myTable99
SET Col2 = 'D'
WHERE Col1 = 2
SELECT * FROM myTable99
OK

DROP TRIGGER myTrigger99
DROP TABLE myTable99
GO|||If you don't like triggers, then you could re-write the application to use only stored procedures to update the tables, then remove update permissions from the tables, to make sure no one sneaks in the back way.|||And if you don't care about getting an actual Date/Time value from the field (just uniqueness), then you can use the timestamp datatype. It's a binary value that is unique in the database, but does not actually represent a date or a time. The benefit is that it automatically updates when the row is updated without the need for any additional code.|||And if you don't care about getting an actual Date/Time value from the field (just uniqueness), then you can use the timestamp datatype. It's a binary value that is unique in the database, but does not actually represent a date or a time. The benefit is that it automatically updates when the row is updated without the need for any additional code.

Huh?

And as for using a sproc...it's no guarentee...

No reason not to use a trigger like this...

Anyone?|||SQL Server Books Online

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
...
A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated...
...
A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.

I'm just saying, if he's looking for a field that will automatically update without having to do any coding, a timestamp field will do that.

He never said he needed to know the date/time the record was updated, he said he wanted to know when a record is modified. You'd know the record has been modified when the timestamp field changes.|||Going with the stored procedure requires that the DBAs ensure that the programmers don't try to back-end him/her. This would take a (politically) strong DBA group, that can enforce such a rule. Or being able to revoke that all important update permission, which forces the application to use the stored procedure.|||Stored procedures are not sufficient to guarantee relational or data integrity. Somebody can and will eventually hook directly into the table and bypass your logic.

Yeah, the timestamp updates. But how do you KNOW it updated unless you retain the previous value?

The thing you have to worry about is when a record thinks it has been updated, but actually the new data is the same as the previous data. If you have a value in your database such as gender that is "Male" and run:

Update mytable set gender = 'Male'

... the update trigger will run even though the data has not changed. In cases where this distinction is important, I've solved the problem by running a binarychecksum comparison between the new record and the old record.|||Right. But we don't know how he's using the date/time field, so any specific recommendation is moot without additional details on his requirements.|||... Somebody can and will eventually hook directly into the table and bypass your logic...?
Can you give us an example on how you'd go about doing it?
:rolleyes:|||Doing what?

Hooking into the table?
update table set thecolumn = somebaddatavalue

Implementing better integrity?
Use a trigger.

Checking to see whether the data had changed?
Use something like where binarychecksum(inserted.*) <> binarychecksum(currentdata.*), but I'd have to look up my old code to see exactly what syntax I used. I seem to recall using having to use subqueries to get around some of the limitations of the binarychecksum input parameters.

If nanou9999 is interested, I look it up when I have time.|||That's why I keep saying you have to be able to remove the permission to update the table.|||That's why I keep saying you have to be able to remove the permission to update the table.EXACTLY!!!

So my question to blindman was how he'd go about "hooking" (what a term!) into a table, if ALL permissions are denied, and the only way to affect the data is through stored procedures.

I guess I need to be more elaborate in stating my questions, huh?! ;)

So, blindman, how would you "hook" into a table (...hmmmmm...your update will fail, you know)?|||Perhaps you trust your Database Administrators never to directly change data in a table, but I do not. Or perhaps I just build my database applications to be more robust than you do.

If a rule applies to the data, then implement it at the data level, not in every procedure that accesses the data. Common sense.

Now go ahead with your next inane, hair-splitting post, because I know you must, but I'm done with this thread. Ta-ta... :cool:|||... Or perhaps I just build my database applications to be more robust than you do.
I doubt it, but...Is this a challenge?
...If a rule applies to the data, then implement it at the data level, not in every procedure that accesses the data. Common sense. That's a front-end coder's answer, not an application architect's one, but then I never suspected you to be of that caliber either ;)
...Now go ahead with your next inane, hair-splitting post, because I know you must, but I'm done with this thread. Ta-ta...
And as you see I do, but only to demonstrate that you are not the one to decide whether the thread should be closed or not. BTW, the rest of us don't think of ourselves that high-up-in-the-sky either ;) Get off of your cloud of self-praising and adoration of your superiority, be simpler, and people will love you :p|||EDIT: Nevermind...sql