· 7 years ago · Jan 17, 2019, 04:50 AM
1CREATE TABLE [dbo].[NewAuditLog](
2 [Type] [char](1) NULL,
3 [TableName] [varchar](128) NULL,
4 [PK] [varchar](1000) NULL,
5 [FieldName] [varchar](128) NULL,
6 [OldValue] [varchar](1000) NULL,
7 [NewValue] [varchar](1000) NULL,
8 [UpdateDate] [datetime] NULL,
9 [UserName] [varchar](128) NULL
10) ON [PRIMARY]
11
12CREATE TRIGGER [dbo].[TR_Employee_AUDIT] ON [dbo].[Employee_mstr] FOR UPDATE
13AS
14
15DECLARE @bit INT ,
16 @field INT ,
17 @maxfield INT ,
18 @char INT ,
19 @fieldname VARCHAR(128) ,
20 @TableName VARCHAR(128) ,
21 @PKCols VARCHAR(1000) ,
22 @sql VARCHAR(2000),
23 @UpdateDate VARCHAR(21) ,
24 @UserName VARCHAR(128) ,
25 @Type CHAR(1) ,
26 @PKSelect VARCHAR(1000),
27 @empcode VARCHAR(20)
28
29
30--You will need to change @TableName to match the table to be audited.
31-- Here we made GUESTS for your example.
32SELECT @TableName = 'Employee_Mstr'
33
34-- date and user
35SELECT @UserName = SYSTEM_USER ,
36 @UpdateDate = CONVERT (NVARCHAR(30),GETDATE(),126)
37
38-- Action
39IF EXISTS (SELECT * FROM inserted)
40 IF EXISTS (SELECT * FROM deleted)
41 SELECT @Type = 'U'
42 ELSE
43 SELECT @Type = 'I'
44ELSE
45 SELECT @Type = 'D'
46
47-- get list of columns
48SELECT * INTO #ins FROM inserted
49SELECT * INTO #del FROM deleted
50
51select @UserName = EMP_ModifiedBy, @empcode = emp_cd from #ins
52if isnull(@UserName,'') = ''
53select @UserName = EMP_ModifiedBy, @empcode = emp_cd from #del
54
55-- Get primary key columns for full outer join
56SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')
57 + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
58 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
59
60 INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
61 WHERE pk.TABLE_NAME = @TableName
62 AND CONSTRAINT_TYPE = 'PRIMARY KEY'
63 AND c.TABLE_NAME = pk.TABLE_NAME
64 AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
65
66-- Get primary key select for insert
67SELECT @PKSelect = COALESCE(@PKSelect+'+','')
68 + '''<' + COLUMN_NAME
69 + '=''+convert(varchar(100),
70coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
71 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
72 INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
73 WHERE pk.TABLE_NAME = @TableName
74 AND CONSTRAINT_TYPE = 'PRIMARY KEY'
75 AND c.TABLE_NAME = pk.TABLE_NAME
76 AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
77
78IF @PKCols IS NULL
79BEGIN
80 RAISERROR('no PK on table %s', 16, -1, @TableName)
81 RETURN
82END
83
84
85SELECT @field = 0,
86 @maxfield = MAX(ORDINAL_POSITION)
87 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
88 AND COLUMN_NAME NOT IN ('EMP_ModifiedOn','EMP_ModifiedBy')
89WHILE @field < @maxfield
90BEGIN
91 SELECT @field = MIN(ORDINAL_POSITION)
92 FROM INFORMATION_SCHEMA.COLUMNS
93 WHERE TABLE_NAME = @TableName
94 AND ORDINAL_POSITION > @field
95 AND COLUMN_NAME NOT IN ('EMP_ModifiedOn','EMP_ModifiedBy')
96 SELECT @bit = (@field - 1 )% 8 + 1
97 SELECT @bit = POWER(2,@bit - 1)
98 SELECT @char = ((@field - 1) / 8) + 1
99 IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0
100 OR @Type IN ('I','D')
101 BEGIN
102 SELECT @fieldname = COLUMN_NAME
103 FROM INFORMATION_SCHEMA.COLUMNS
104 WHERE TABLE_NAME = @TableName
105 AND ORDINAL_POSITION = @field
106 AND COLUMN_NAME NOT IN ('EMP_ModifiedOn','EMP_ModifiedBy')
107 SELECT @sql = '
108insert NewAuditLog ( Type,
109 TableName,
110 PK,
111 FieldName,
112 OldValue,
113 NewValue,
114 UpdateDate,
115 UserName)
116select ''' + @Type + ''','''
117 + @TableName + ''',''' + @empcode + ''',''' + @fieldname + ''''
118 + ',convert(varchar(1000),d.' + @fieldname + ')'
119 + ',convert(varchar(1000),i.' + @fieldname + ')'
120 + ',''' + @UpdateDate + ''''
121 + ',''' + @UserName + ''''
122 + ' from #ins i full outer join #del d'
123 + @PKCols
124 + ' where i.' + @fieldname + ' <> d.' + @fieldname
125 + ' or (i.' + @fieldname + ' is null and d.'
126 + @fieldname
127 + ' is not null)'
128 + ' or (i.' + @fieldname + ' is not null and d.'
129 + @fieldname
130 + ' is null)'
131 EXEC (@sql)
132 END
133END