Created
July 2, 2017 02:13
-
-
Save haroldcris/e8e6dbcbebc03d6271749670a2b42e34 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 1) Create AUDIT Table. | |
IF NOT EXISTS | |
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[Audit]') | |
AND OBJECTPROPERTY(id, N'IsUserTable') = 1) | |
CREATE TABLE Audit | |
(Type CHAR(1), | |
TableName VARCHAR(128), | |
PK VARCHAR(1000), | |
FieldName VARCHAR(128), | |
OldValue VARCHAR(1000), | |
NewValue VARCHAR(1000), | |
UpdateDate datetime, | |
UserName VARCHAR(128)) | |
GO | |
-- 2) CREATE an UPDATE Trigger on the GUESTS Table as follows. | |
CREATE TRIGGER TR_GUESTS_AUDIT ON GUESTS FOR UPDATE | |
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) , | |
@Type CHAR(1) , | |
@PKSelect VARCHAR(1000) | |
--You will need to change @TableName to match the table to be audited. | |
-- Here we made GUESTS for your example. | |
SELECT @TableName = 'GUESTS' | |
-- date and user | |
SELECT @UserName = SYSTEM_USER , | |
@UpdateDate = CONVERT (NVARCHAR(30),GETDATE(),126) | |
-- Action | |
IF EXISTS (SELECT * FROM inserted) | |
IF EXISTS (SELECT * FROM deleted) | |
SELECT @Type = 'U' | |
ELSE | |
SELECT @Type = 'I' | |
ELSE | |
SELECT @Type = 'D' | |
-- 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 | |
-- Get primary key select for insert | |
SELECT @PKSelect = COALESCE(@PKSelect+'+','') | |
+ '''<' + COLUMN_NAME | |
+ '=''+convert(varchar(100), | |
coalesce(i.' + COLUMN_NAME +',d.' + 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 | |
OR @Type IN ('I','D') | |
BEGIN | |
SELECT @fieldname = COLUMN_NAME | |
FROM INFORMATION_SCHEMA.COLUMNS | |
WHERE TABLE_NAME = @TableName | |
AND ORDINAL_POSITION = @field | |
SELECT @sql = ' | |
insert Audit ( Type, | |
TableName, | |
PK, | |
FieldName, | |
OldValue, | |
NewValue, | |
UpdateDate, | |
UserName) | |
select ''' + @Type + ''',''' | |
+ @TableName + ''',' + @PKSelect | |
+ ',''' + @fieldname + '''' | |
+ ',convert(varchar(1000),d.' + @fieldname + ')' | |
+ ',convert(varchar(1000),i.' + @fieldname + ')' | |
+ ',''' + @UpdateDate + '''' | |
+ ',''' + @UserName + '''' | |
+ ' from #ins i full outer join #del d' | |
+ @PKCols | |
+ ' where i.' + @fieldname + ' <> d.' + @fieldname | |
+ ' or (i.' + @fieldname + ' is null and d.' | |
+ @fieldname | |
+ ' is not null)' | |
+ ' or (i.' + @fieldname + ' is not null and d.' | |
+ @fieldname | |
+ ' is null)' | |
EXEC (@sql) | |
END | |
END | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment