GetDate

I am using GetDate() as the default value on selected fields to record the
date/time that a record is inserted into my tables, however, is it possible
to use a similar procedure to automatically insert the date/time into a
field, but ONLY if the record is subject to an update - thus recording the
date/time a record was last updated.
ThanksKeith
You have to write a TRIGGER FOR UPDATE (For more details please refer to the
BOL) .
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||Hi,
Either you have to explicitly update (Overwrite) the date column with an
Update statement or use Update triggers
to obtain this.
update table
set col1 = @.col1 ,col2 = @.col2,
date = getdate()
where ...
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e9ywN5jFEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Keith
> You have to write a TRIGGER FOR UPDATE (For more details please refer to
the
> BOL) .
>
> "Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
the
> possible
the
>|||Keith can you use your client app to do this...if it's an asp app...you can
use a hidden field to update the column....
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||I know I can do this, but as I am in the early stages of this app, I wanted
to try and shift as much as possible to server side to minimise the
client-server traffic and 'hopefully' increase security.
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:eZw2fKlFEHA.3080@.tk2msftngp13.phx.gbl...
> Keith can you use your client app to do this...if it's an asp app...you
can
> use a hidden field to update the column....
> "Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
the
> possible
the
>|||Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||Actually, this functionality has been in place for over a decade in the form
of triggers:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0 return
update MyTable
set
LastUpdateDateTime = getdate ()
where
PK in (select PK from inserted)
go
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message
news:#0KV3omFEHA.1240@.TK2MSFTNGP10.phx.gbl...
Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||That's barely any client server traffic...plus triggers would eat up
more of your server resources...try both out and run some counters to
baseline some performance...
"Keith" <@..> wrote in message news:u5KuzTlFEHA.3724@.TK2MSFTNGP11.phx.gbl...
> I know I can do this, but as I am in the early stages of this app, I
wanted
> to try and shift as much as possible to server side to minimise the
> client-server traffic and 'hopefully' increase security.
>
> "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:eZw2fKlFEHA.3080@.tk2msftngp13.phx.gbl...
> can
news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> the
a
> the
>|||Yes, and do this in many places. However we removed these for performance re
asons on several tables, and the difference was measurable. Maybe using INST
EAD OF triggers for this would have helped the speed, though we never tested
this.
I don't know the performance implications of the SQL Anywhere solution as we
don't use the product. I just know the feature is available and it's specif
ied as DDL, kind of a default on update as well as insert.
It just sounded like a simple solution to a common requirement.
Mike Kruchten
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:ejHNOxmFEHA.35
40@.TK2MSFTNGP09.phx.gbl...
Actually, this functionality has been in place for over a decade in the form
of triggers:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0 return
update MyTable
set
LastUpdateDateTime = getdate ()
where
PK in (select PK from inserted)
go
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message news:#0KV3omFE
HA.1240@.TK2MSFTNGP10.phx.gbl...
Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>sql