The reason timestamp is binary is for speed. Timestamps
are made to allow version comparisons to be as fast as
possible. Unfortunate name though!
Triggers have usually worked well in my projects...
I like that no matter HOW the row is updated (someone
using Access/Query Analyzer, an App SP, etc, etc) the
trigger fires and my ChangedDTTM and ChangedBy columns
get updated...
But in the end, it's really about what you and your team
are comfortable with and can support...
I've also looked at TimeStamps. One thing that tipped me
off that it's a "version vehicle" is
that in someplace's in the BOL MS states that RowVersion
is an alias for the TimeStamp datatype...
"triggers are something I try to avoid, so
let's ignore that possibility"
Roy, why avoid triggers? It seems to me that a trigger
is the best way to deliver exactly what you need!
There's very little difference between a trigger and a
stored proc. You seem happy to use one, but not the
other.
Triggers are risky unless you /really/ know what you are
doing. They seem simply if you have been pulling DBA
duty for years, but for the occasional DBA they can lead
to spaghetti faster than an Italian chef on meth.
good and helpful blog entry!
Been there, done that. Didn't have a blog at the time...
I filed it under "fire the guy who named
that".
Hi Roy
Just wanted to write and apologise to you, because i
just realised that over the last several months i've
continually mispelled your surname as 'oshergrove'
instead of 'osherove'. no idea how i came up with the
extra letters... but i've cleared them out of my cache
now.
cheers
Leon
Roy -- maybe I'm saying the same thing as you when you
said:
"I can either get the date as part of a stored
procedure parameter and set it every update, or I can
just set it on updates and inserts automatically inside
the stored procedure using “GETDATE()” functionality in
SQL."
We use GETDATE as a default value on our DataModified
columns
(which are like standard footers on most tables)
So -- anytime you touch that row, GETDATE() fires...is
this
what you describe above?
Defaults when bound to columns always work faster than
triggers and one doesn't have to worry about triggers
generating errors this way!
So how would one actually use the MS version of
TIMESTAMP? I've seen a hundred posts like this,
explaining how timestamp isn't a date and all, but the
conclusion seems to always be the datetime/getdate()
hack.
That's fine, but I'd still like to know how to use the
MS version of TS/rowversion for concurrency. I'm having
problems casting the wacko MS timestamp value into some
C# data type so I can compare them on save/update.