Re: trigger updating timestamp

From: Keith Kratochvil (sqlguy.back2u_at_comcast.net)
Date: 07/28/04 

Date: Wed, 28 Jul 2004 08:43:17 -0500

First of all, let me address this:

>>I have used a timestamp field instead of datetime because I was afraid
datetime wouldn't provide the desired accuracy<<

OK, you were scared to use a datetime because it was not accurate....how the
heck are you going to convert the TIMESTAMP column into something that even
closely resembles a date with any sort of accuracy?

Second, what benefit do you get by having a column that means nothing to
normal humans (like timestamp) be updated on each insert? It seems to me
like you want to know when something was last updated. In that case I would
tend to use datetime that has an "accuracy of one three-hundredth of a
second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are
rounded to increments of .000, .003, or .007 seconds" (quote from Books
Online). 

-- 
Keith
"lukasz" <bbla32@op.pl> wrote in message
news:a8d104bd.0407280507.2fb83eba@posting.google.com...
> I have the following table:
>
> CREATE TABLE [dbo].[TablesLastUpdate] (
> [TableName] [char] (50) COLLATE Polish_CI_AS NOT NULL ,
> [LastUpdate] [timestamp] NOT NULL
> ) ON [PRIMARY]
> GO
>
>
> and I use it to keep record of last update time of other tables. I
> have used a timestamp field instead of datetime because I was afraid
> datetime wouldn't provide the desired accuracy (like not able to
> distinguish between two updates within one millisecond or so). I
> created a trigger for insert, update, and delete for the audited
> tables which looks as follows:
>
> CREATE TRIGGER LastUpdateXxx
> ON Xxx
> AFTER UPDATE, INSERT, DELETE
> AS
>
> IF @@rowcount = 0
> RETURN
>
> DECLARE @myvar char(10)
>
> UPDATE TablesLastUpdate
> SET @myvar = 'temp'
> WHERE TableName = 'Xxx'
>
> Because I have only two fields in TablesLastUpdate: one is the PK, and
> the other is timestamp, neither updateable, in order to update the
> timestamp I use the above trick. A few test runs showed this works,
> however, can anyone assure me that virtual update will always change
> the row's timestamp upon any change in the audited table? And BTW, why
> the UPDATE command allows such syntax? What's the purpose of this
> update?
>
> lukasz

Relevant Pages

  • trigger updating timestamp
    ... have used a timestamp field instead of datetime because I was afraid ... CREATE TRIGGER LastUpdateXxx ... UPDATE TablesLastUpdate ...
    (microsoft.public.sqlserver.programming)
  • Re: Please advise on approach
    ... has direct bearing on the issues and the context is difficult to communicate ... > of the term timestamp - it does not refer to the sql data type in this ... rather to the datetime at which the source produced the data - ... I'm only interested in it differentiating between records ...
    (microsoft.public.sqlserver.server)
  • Re: Convert help needed desperately
    ... overflow error converting expression to data type datetime. ... Is my sql server hosed???? ... > the timestamp data type. ...
    (microsoft.public.sqlserver.programming)
  • Re: Please advise on approach
    ... of the term timestamp - it does not refer to the sql data type in this ... rather to the datetime at which the source produced the data - ... I'm only interested in it differentiating between records ... > insertion order unless that order can be determined by the data itself. ...
    (microsoft.public.sqlserver.server)
  • Re: Please advise on approach
    ... timestamp has no relationship to date or time; ... datetime values are accurate to 3 milliseconds. ... insertion order unless that order can be determined by the data itself. ... pitfall is that reconstruction often implies "re-running" (e.g., ...
    (microsoft.public.sqlserver.server)