... until the collector arrives ...

This "blog" is really just a scratchpad of mine. There is not much of general interest here. Most of the content is scribbled down "live" as I discover things I want to remember. I rarely go back to correct mistakes in older entries. You have been warned :)

2010-01-21

Simple SQL Server Table Change Tracking

The following SQL Server script demonstrates a simple technique for maintaining a log of the changes made to a table. The main idea is embodied in the emphasized code.

CREATE TABLE zot
( X INT
, Y INT
)
GO

CREATE TABLE changeLog
( id INT IDENTITY
, time DATETIME
, tableName NVARCHAR(255)
, change NVARCHAR(MAX)
)
GO

CREATE TRIGGER TR_zot ON zot FOR INSERT, UPDATE, DELETE AS BEGIN
  INSERT INTO changeLog
    SELECT GETDATE(), 'zot'
    , COALESCE((SELECT * FROM DELETED FOR XML AUTO), '')
    + COALESCE((SELECT * FROM INSERTED FOR XML AUTO), '')
END
GO

INSERT INTO zot VALUES (1, 2)
INSERT INTO zot VALUES (3, 4)
INSERT INTO zot SELECT 4, 5 UNION SELECT 6, 7
UPDATE zot SET X=999 WHERE X < 4
DELETE FROM zot WHERE X=999
DELETE FROM zot

GO

SELECT * FROM changeLog
GO

DROP TABLE zot
DROP TABLE changeLog
GO

The result of this script shows what the change log entries would look like:

id time tableName change
1 2010-01-21 10:57:11.0 zot <INSERTED X="1" Y="2"/>
2 2010-01-21 10:57:11.0 zot <INSERTED X="3" Y="4"/>
3 2010-01-21 10:57:11.0 zot <INSERTED X="6" Y="7"/><INSERTED X="4" Y="5"/>
4 2010-01-21 10:57:11.1 zot <DELETED X="3" Y="4"/><DELETED X="1" Y="2"/><INSERTED X="999"Y="4"/><INSERTED X="999" Y="2"/>
5 2010-01-21 10:57:11.1 zot <DELETED X="999" Y="4"/><DELETED X="999" Y="2"/>
6 2010-01-21 10:57:11.1 zot <DELETED X="6" Y="7"/><DELETED X="4" Y="5"/>

2010-01-08

Windows 7 "All Tasks" View

Under Windows 7, you can open a handy All Tasks view by entering the following command in the Run dialog:

shell:::{ED7BA470-8E54-465E-825C-99712043E01C}

Windows Explorer will also display this view in any folder whose name contains that GUID (e.g. create a folder named all-tasks.{ED7BA470-8E54-465E-825C-99712043E01C}). Some bloggers call this "God Mode".

The All Tasks GUID is not presently included in the Microsoft's list of Canonical Names of Control Panel Items. This might be because the feature seems to be a bit unstable -- if I scroll to the bottom of the window quickly, Windows Explorer often crashes.

Blog Archive