Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2004
    Location
    Cergy
    Posts
    65

    Question Unanswered: 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

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The only way I know is to use a trigger to update the column.

    -PatP

  3. #3
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    Take a look at Lumigent Log Explorer. It allows you to peep into transaction logs to find out who did what when.

  4. #4
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Angry time stamp

    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.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  7. #7
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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.
    That which does not kill me postpones the inevitable.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Seppuku
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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.
    That which does not kill me postpones the inevitable.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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.
    That which does not kill me postpones the inevitable.

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by blindman
    ... 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?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    That's why I keep saying you have to be able to remove the permission to update the table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •