The Sales Engineer

I was looking for a way to have all my tables have a unique GUID (Globally Unique ID) as well as a “Last Updated” column that would automatically update both on INSERT and on UPDATE.

Through a few hours of experimentation, I created the following:

CREATE TABLE dbo.testing
    (guid            uniqueidentifier    NOT NULL DEFAULT NEWID(),
     lastUpdated     datetime2(7)        NOT NULL DEFAULT GETDATE(),
     col1            nchar(10)           NULL,
     col2            nchar(10)           NULL)
GO
CREATE TRIGGER dbo.trLastUpdatedTesting
ON dbo.testing
AFTER UPDATE -- not insert!
AS
BEGIN

    IF NOT UPDATE(lastUpdated)
    BEGIN
        UPDATE t
            SET t.lastUpdated = CURRENT_TIMESTAMP
            FROM dbo.testing AS t -- not b!
            INNER JOIN inserted AS i
            ON t.guid = i.guid;
    END
END
GO
INSERT INTO dbo.testing ([col1], [col2]) VALUES ('one', '1')
GO
INSERT INTO dbo.testing ([col1], [col2]) VALUES ('two', '2')
GO
SELECT * FROM dbo.testing
GO
UPDATE dbo.testing SET col1 = 'ONE' WHERE col2 = '1'
GO
SELECT * FROM dbo.testing
GO
DROP TABLE testing
GO

I now add these functions to all my table templates.

The good stuff is the TRIGGER to keep my lastUpdated column updated, and the DEFAULT settings of the guid and lastUpdated columns in the CREATE statement. The DEFAULT keywords only help for INSERTs, which is why I had to also have the trigger.

The guid never changes once created on INSERT, but the guid column has to be there for INSERT and every subsequent UPDATE. Both are here in an easy to use example that you can run, and get:

guid                                 lastUpdated                            col1       col2
------------------------------------ -------------------------------------- ---------- -----
09C02D9D-DF81-4C01-80D6-EEF644660C62            2011-11-02 18:55:03.3800000 one        1
685F7977-0DF2-4B44-8937-50A307069F0D            2011-11-02 18:55:03.4230000 two        2

(2 rows affected)
1> UPDATE dbo.testing SET col1 = 'ONE' WHERE col2 = '1'
2> GO

(1 rows affected)
1> SELECT * FROM dbo.testing
2> GO
guid                                 lastUpdated                            col1       col2
------------------------------------ -------------------------------------- ---------- -----
09C02D9D-DF81-4C01-80D6-EEF644660C62            2011-11-02 18:55:03.5230000 ONE        1
685F7977-0DF2-4B44-8937-50A307069F0D            2011-11-02 18:55:03.4230000 two        2

(2 rows affected)

Note the lastUpdated column value before & after the UPDATE. I hope this proof of concept helps you!

MS SQL 2008 R2

pat

:)

Related Articles

• No Related Post