Listing 1: Creation Script for the T1 Table and Three Audit Tables
SET NOCOUNT ON;
USE tempdb;
GO

IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
IF OBJECT_ID('dbo.T1AuditInsDelDetail') IS NOT NULL
  DROP TABLE dbo.T1AuditInsDelDetail;
IF OBJECT_ID('dbo.T1AuditUpdDetail') IS NOT NULL
  DROP TABLE dbo.T1AuditUpdDetail;
IF OBJECT_ID('dbo.T1AuditHeader') IS NOT NULL
  DROP TABLE dbo.T1AuditHeader;
GO

CREATE TABLE dbo.T1
(  keycol int NOT NULL PRIMARY KEY,
  intcol int NULL,
  varcharcol varchar(10) NULL );
GO

CREATE TABLE dbo.T1AuditHeader
( id         int       NOT NULL IDENTITY PRIMARY KEY,
  dt         datetime  NOT NULL DEFAULT(GETDATE()),
  loginname  SYSNAME   NOT NULL DEFAULT(SUSER_SNAME()),
  app        SYSNAME   NOT NULL DEFAULT(APP_NAME()),
  host       SYSNAME   NOT NULL DEFAULT(HOST_NAME()),
  failed     bit       NOT NULL DEFAULT(0),
  dmltype    char(1)   NOT NULL CHECK (dmltype IN('I', 'U', 'D')),
  row_count  int       NOT NULL,
  comment    nvarchar(100) NULL );
GO

CREATE TABLE dbo.T1AuditInsDelDetail
(  id         int           NOT NULL IDENTITY PRIMARY KEY,
  headerid   int         NOT NULL REFERENCES dbo.T1AuditHeader,
  keycol     int         NOT NULL,
  intcol     int             NULL,
  varcharcol varchar(10)     NULL );
GO

CREATE TABLE dbo.T1AuditUpdDetail
( id         int         NOT NULL IDENTITY PRIMARY KEY,
  headerid   int        NOT NULL REFERENCES dbo.T1AuditHeader,
  keycol     int         NOT NULL,
  colname    sysname     NOT NULL,
  oldval     sql_variant NULL,
  newval     sql_variant NULL );
GO