CURRENT DATE and TIME Question

[TenOCC]

I have an event that update my database, I would like to record the current
date and time when the record was updated.

Not quite sure of the command

Conn.Execute ("UPDATE dM SET OpenDate = ???????)

 

[Aaron Bertrand [SQL Server MVP]]

CURRENT_TIMESTAMP

 

[Aaron Bertrand [SQL Server MVP]]

Oh, sorry, you forgot to mention which database you were using. Please
specify.

SQL Server:

conn.execute "UPDATE dM SET OpenDate = CURRENT_TIMESTAMP WHERE ...",,129

Access:

conn.execute "UPDATE dM SET OpenDate = Now() WHERE ...",,129

 

[Bob Barrows [MVP]]

I have an event that update my database, I would like to record the
current date and time when the record was updated.

Not quite sure of the command

Conn.Execute ("UPDATE dM SET OpenDate = ???????)

What database? Type and version please.

Bob Barrows

 

[TenOCC]

Sorry SQL Server 2000

What database? Type and version please.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

Then you can use an update trigger in your database to update this column (I
am going to assume you have a column called id which is the primary key for
the dm table):

CREATE TRIGGER tr_dm_OpenDate
ON dm
FOR UPDATE, INSERT AS
UPDATE dm
SET OpenDate = CURRENT_TIMESTAMP
WHERE id IN
(SELECT id FROM inserted)

It's better to use a trigger for this because your table may be updated by
sources other than your asp application.

Bob Barrows

 

[SP]

Or you can just use

"Update dM Set OpenDate = getdate() where idfield = XXX...."

 

[Aaron Bertrand [SQL Server MVP]]

Or you can just use

"Update dM Set OpenDate = getdate() where idfield = XXX...."

You probably missed Bob's point, which was that it is not necessarily the
case that updates can be restricted to the ASP code alone, never mind how
many places the code will have to change (yes, stored procedures should be
used, but that's a different argument, and we are not clear on whether they
are currently in place).

There are arguments for both sides. Triggers definitely help to prevent ad
hoc updates from other sources (query analyzer, rogue applications etc) from
being missed and the row(s) not updated, however there is definitely a hit
on performance -- especially if some ******* says "UPDATE table SET col =
col" without a where clause.