Update Guid column on any update to table row

I have a column Last_Updated (uniqueidentifier) on a table. I'd like
this column to get a new guid (NEWID()) each time any column in the
row is updated.

Is there an automatic way to do this outside of triggers?

Here's an example of what I'm looking for:

CREATE TABLE GuidTest (ID INT PRIMARY KEY IDENTITY, Status INT,
LastUpdated UNIQUEIDENTIFIER DEFAULT (NEWID()))
INSERT INTO GuidTest (Status) VALUES (10)
INSERT INTO GuidTest (Status) VALUES (20)
INSERT INTO GuidTest (Status) VALUES (30)

SELECT ID, Status, LastUpdated FROM GuidTest
UPDATE GuidTest SET Status = 31 WHERE ID = 3
SELECT ID, Status, LastUpdated FROM GuidTest
-- Would like GUID on ID=3 to be different in second select

DROP TABLE GuidTest

Thanks,
Michael

Re: Update Guid column on any update to table row

Outside of a trigger, no. But it is exactly the sort of thing that
triggers are for.

Roy Harvey
Beacon Falls, CT

On Mon, 23 Jul 2007 13:27:13 -0000, michael wrote:

>I have a column Last_Updated (uniqueidentifier) on a table. I'd like
>this column to get a new guid (NEWID()) each time any column in the
>row is updated.
>
>Is there an automatic way to do this outside of triggers?
>
>Here's an example of what I'm looking for:
>
>CREATE TABLE GuidTest (ID INT PRIMARY KEY IDENTITY, Status INT,
>LastUpdated UNIQUEIDENTIFIER DEFAULT (NEWID()))
>INSERT INTO GuidTest (Status) VALUES (10)
>INSERT INTO GuidTest (Status) VALUES (20)
>INSERT INTO GuidTest (Status) VALUES (30)
>
>SELECT ID, Status, LastUpdated FROM GuidTest
>UPDATE GuidTest SET Status = 31 WHERE ID = 3
>SELECT ID, Status, LastUpdated FROM GuidTest
>-- Would like GUID on ID=3 to be different in second select
>
>DROP TABLE GuidTest
>
>Thanks,
>Michael

Re: Update Guid column on any update to table row

Thanks, Roy

Re: Update Guid column on any update to table row

michael (mharen@gmail.com) writes:
> I have a column Last_Updated (uniqueidentifier) on a table. I'd like
> this column to get a new guid (NEWID()) each time any column in the
> row is updated.
>
> Is there an automatic way to do this outside of triggers?

I don't know what the purpose with this guid is, but there is a special
data type in SQL Server, timestamp, for this purpose. A timestamp is a
binary(8) values with no relation to date and time. Such a column is
automatically updated each time a row is touched. Furthermore, the
value is database-unique and monotonically growing.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx