1: BEGIN
2:
3: SET NOCOUNT ON
4:
5: -- Declare variables to represent the input data
6: DECLARE @c0 nvarchar(20) = N'B', @c1 nvarchar(20) = N'C', @c2 nvarchar(20) = N'B',
7: @c3 nvarchar(20) = NULL, @c4 nvarchar(20) = N'D', @c5 nvarchar(20) = N'A',
8: @c6 nvarchar(20) = NULL, @c7 nvarchar(20) = NULL, @c8 nvarchar(20) = NULL,
9: @c9 nvarchar(20) = NULL, @user nvarchar(20) = N'Test',
10: @pkid int = 10
11:
12: -- Create a table variable that is equivalent to the source table
13: -- table variables are stored in tempdb so make sure we use this
14: -- database's collation
15: DECLARE @tgtData TABLE (
16: pkid int,
17: col0 nvarchar(20) COLLATE DATABASE_DEFAULT,
18: col1 nvarchar(20) COLLATE DATABASE_DEFAULT,
19: col2 nvarchar(20) COLLATE DATABASE_DEFAULT,
20: col3 nvarchar(20) COLLATE DATABASE_DEFAULT,
21: col4 nvarchar(20) COLLATE DATABASE_DEFAULT,
22: col5 nvarchar(20) COLLATE DATABASE_DEFAULT,
23: col6 nvarchar(20) COLLATE DATABASE_DEFAULT,
24: col7 nvarchar(20) COLLATE DATABASE_DEFAULT,
25: col8 nvarchar(20) COLLATE DATABASE_DEFAULT,
26: col9 nvarchar(20) COLLATE DATABASE_DEFAULT,
27: modified datetime,
28: modifiedBy nvarchar(20) COLLATE DATABASE_DEFAULT)
29:
30: -- Insert new values into the table variable, can repeat multiple times
31: INSERT INTO @tgtData VALUES (@pkid, @c0, @c1, @c2, @c3, @c4, @c5, @c6,
@c7, @c8, @c9, GETDATE(), @user)
32:
33: -- This query eliminates NULLs as part of an aggregate so turn ANSI WARNINGS off
34: SET ANSI_WARNINGS OFF;
35:
36: -- Audit and change are one atomic operation, so need a transaction
37: BEGIN TRAN;
38:
39: -- Previous statement is completed with the required ;
40: -- THIS IS THE AUDIT statement
41: WITH
42: -- mergelist is a cartesian join of columns and affected row identifiers
43: mergelist (pkid, colname) AS (
44: SELECT pkid, colname
45: FROM
46: -- Get a list of row identifiers which are being updated
47: (SELECT DISTINCT pkid FROM @tgtData) AS ids,
48: -- Get a full list of columns
49: ( SELECT 'col0' AS colname UNION ALL
50: SELECT 'col1' AS colname UNION ALL
51: SELECT 'col2' AS colname UNION ALL
52: SELECT 'col3' AS colname UNION ALL
53: SELECT 'col4' AS colname UNION ALL
54: SELECT 'col5' AS colname UNION ALL
55: SELECT 'col6' AS colname UNION ALL
56: SELECT 'col7' AS colname UNION ALL
57: SELECT 'col8' AS colname UNION ALL
58: SELECT 'col9' AS colname
59: ) AS cols
60: ),
61: -- srcData is an unpivoted set of column values from the source table with NULLs missing
62: srcData (pkid, colname, value) AS (
63: SELECT t.pkid, t.colname, t.value
64: FROM tblToAudit UNPIVOT (value FOR colname IN (col0, col1, col2, col3, col4, col5,
col6, col7, col8, col9)) AS t
65: ),
66: -- tgtData is an unpivoted set of column values from the new data with NULLs missing
67: tgtData (pkid, colname, value, modified, modifiedBy) AS (
68: SELECT t.pkid, t.colname, t.value, t.modified, t.modifiedBy
69: FROM @tgtData UNPIVOT (value FOR colname IN (col0, col1, col2, col3, col4, col5,
col6, col7, col8, col9)) AS t
70: ),
71: -- changeList is the expanded set of srcData and tgtData including NULLs and with
72: -- change indicating which values are NULL
73: changeList (pkid, colname, old_value, new_value, change, modified, modifiedBy) AS (
74: SELECT mergelist.pkid, mergelist.colname, srcData.value, tgtData.value,
75: (2*COUNT(srcData.value) OVER (PARTITION BY mergelist.pkid, mergelist.colname)
76: + COUNT(tgtData.value) OVER (PARTITION BY mergelist.pkid, mergelist.colname))
77: AS change,
78: tgtData.modified, tgtData.modifiedBy
79: FROM mergeList
80: LEFT JOIN srcData
81: ON mergelist.pkid = srcData.pkid AND mergelist.colname = srcData.colname
82: LEFT JOIN tgtData
83: ON mergelist.pkid = tgtData.pkid AND mergelist.colname = tgtData.colname
84: )
85: -- inserting into the audit table
86: INSERT INTO tblAudit ([object_name], pkid, column_name, prior_value, new_value,
modified, modifiedBy, msg)
87: SELECT N'tblToAudit', pkid, colname,
88: old_value, new_value,
89: modified, modifiedBy,
90: -- use the change column to determine the action being taken
91: CASE change
92: WHEN 1 THEN N'set'
93: WHEN 2 THEN N'reset'
94: WHEN 3 THEN N'modify'
95: END AS msg
96: FROM changeList
97: -- change = 0 means both source and target values are NULL
98: WHERE changeList.change > 0 AND
99: -- change = 3 means both source and target are NOT NULL, so need to compare them
100: (changeList.change < 3 OR (old_value <> new_value))
101:
102: -- UPDATE existing rows
103: UPDATE tblToAudit
104: SET
105: tblToAudit.col0 = t.col0,
106: tblToAudit.col1 = t.col1,
107: tblToAudit.col2 = t.col2,
108: tblToAudit.col3 = t.col3,
109: tblToAudit.col4 = t.col4,
110: tblToAudit.col5 = t.col5,
111: tblToAudit.col6 = t.col6,
112: tblToAudit.col7 = t.col7,
113: tblToAudit.col8 = t.col8,
114: tblToAudit.col9 = t.col9,
115: tblToAudit.lastModified = t.modified,
116: tblToAudit.lastModifiedBy = t.modifiedBy
117: FROM tblToAudit INNER JOIN @tgtData AS t ON tblToAudit.pkid = t.pkid
118:
119: -- inserting identity column
120: SET IDENTITY_INSERT tblToAudit ON
121:
122: -- INSERT new rows (not inserting identity column)
123: INSERT INTO tblToAudit (pkid, col0, col1, col2, col3, col4, col5, col6,
col7, col8, col9, lastModified, lastModifiedBy)
124: SELECT t.pkid, t.col0, t.col1, t.col2, t.col3, t.col4, t.col5, t.col6,
t.col7, t.col8, t.col9, t.modified, t.modifiedBy
125: FROM @tgtData AS t LEFT JOIN tblToAudit ON t.pkid = tblToAudit.pkid
126: WHERE tblToAudit.pkid IS NULL
127:
128: -- reset identity insertion option
129: SET IDENTITY_INSERT tblToAudit OFF
130:
131: -- Commit the transaction
132: COMMIT
133:
134: END
135: GO
Subscribe