|
Created On: 6/9/2004 11:29:00 PM --Set up the tables
IF EXISTS (select * FROM sysobjects WHERE id = object_id(N'[dbo].[Audit]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Audit]
go
CREATE TABLE Audit (TableName varchar(128), FieldName varchar(128), OldValue varchar(1000), NewValue varchar(1000), UpdateDate datetime, UserName varchar(128))
go
IF EXISTS (select * FROM sysobjects WHERE id = object_id(N'[dbo].[trigtest]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[trigtest]
go
CREATE TABLE trigtest (i int NOT null, j int not null, s varchar(10), t varchar(10))
go
ALTER TABLE trigtest ADD CONSTRAINT pk PRIMARY KEY (i, j)
go
CREATE TRIGGER tr_trigtest on trigtest FOR insert, update, DELETE
as
DECLARE @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128)
SELECT @TableName = 'trigtest'
-- date AND USER
SELECT @UserName = SYSTEM_USER ,
@UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)
-- get list OF columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted
-- Get PRIMARY KEY columns FOR FULL OUTER JOIN
SELECT @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
IF @PKCols IS NULL
BEGIN
raiserror('no PK ON TABLE %s', 16, -1, @TableName)
RETURN
END
SELECT @field = 0, @maxfield = max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = power(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0
BEGIN
SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION = @field
SELECT @sql = 'insert Audit (TableName, FieldName, OldValue, NewValue, UpdateDate, UserName)'
SELECT @sql = @sql + ' select ''' + @TableName + ''''
SELECT @sql = @sql + ',''' + @fieldname + ''''
SELECT @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
SELECT @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
SELECT @sql = @sql + ',''' + @UpdateDate + ''''
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ' FROM #ins i FULL OUTER JOIN #del d'
SELECT @sql = @sql + @PKCols
SELECT @sql = @sql + ' WHERE i.' + @fieldname + ' <> d.' + @fieldname
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NULL AND d.' + @fieldname + ' is NOT null)'
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NOT NULL AND d.' + @fieldname + ' is null)'
EXEC (@sql)
END
END
go
INSERT trigtest SELECT 1,1,'hi', 'bye'
INSERT trigtest SELECT 2,2,'hi', 'bye'
INSERT trigtest SELECT 3,3,'hi', 'bye'
UPDATE trigtest SET s = 'hibye' WHERE i <> 1
UPDATE trigtest SET s = 'bye' WHERE i = 1
UPDATE trigtest SET s = 'bye' WHERE i = 1
UPDATE trigtest SET t = 'hi' WHERE i = 1
SELECT * FROM Audit
SELECT * FROM trigtest
go
DROP TABLE Audit
go
DROP TABLE trigtest
go
|
|
