1: CREATE PROCEDURE dbo.generic_audit_procedure
2: (
3: @procid INT,
4: @xml XML
5: )
6: AS
7: BEGIN
8: SET NOCOUNT ON
9:
10: -- check if there are changes, in which case, xml will not be empty
11: IF ((@xml IS NULL) OR (CAST(@xml AS NVARCHAR(MAX)) = N''))
12: BEGIN
13: RETURN 0
14: END
15:
16: -- get the current table id for the current process (trigger)
17: DECLARE @tablename NVARCHAR(30)
18:
19: SELECT @tablename = OBJECT_NAME(t.id)
20: FROM sys.sysobjects p
21: INNER JOIN sys.sysobjects t
22: ON p.parent_obj = t.id
23: WHERE p.id = @procid
24:
25: IF ((@tablename IS NULL) OR (@tablename = N''))
26: BEGIN
27: RAISERROR(N'Could not get table name', 16, 16)
28: ROLLBACK TRAN
29: RETURN
30: END
31:
32: -- get the primary key column for the current table
33: DECLARE @idname NVARCHAR(30)
34:
35: SELECT @idname = name
36: FROM sys.syscolumns
37: WHERE id = OBJECT_ID(@tablename)
38: AND colstat = 1
39:
40: IF ((@idname IS NULL) OR (@idname = ''))
41: BEGIN
42: RAISERROR(N'Could not get id column for the current table', 16, 16)
43: ROLLBACK TRAN
44: RETURN
45: END
46:
47: -- get the current user from the context
48: DECLARE @username NVARCHAR(30)
49:
50: SELECT @username = CONVERT(NVARCHAR(30), CONTEXT_INFO())
51:
52: IF ((@username IS NULL) OR (@username = ''))
53: BEGIN
54: RAISERROR(N'Could not get current username', 16, 16)
55: ROLLBACK TRAN
56: RETURN
57: END
58:
59: DECLARE @tmp TABLE([key] NVARCHAR(MAX), [value] XML)
60:
61: INSERT INTO @tmp SELECT q.[key], q.[value]
62: FROM
63: (
64: SELECT T.N.value(N'(*[local-name(.)=sql:variable("@idname")])[1]', N'INT') AS [key], T.N.query(N'*') AS [value]
65: FROM @xml.nodes('/row') AS T(N)
66: ) q
67:
68: DECLARE @ROWS INT
69: SET @ROWS = 0
70:
71: -- loop all modified records
72: WHILE (1 = 1)
73: BEGIN
74: DECLARE @id NVARCHAR(MAX)
75: DECLARE @value XML
76:
77: -- pick the first record
78: SELECT TOP 1 @id = [key], @value = [value]
79: FROM @tmp
80:
81: IF (@@ROWCOUNT = 0)
82: BEGIN
83: BREAK
84: END
85:
86: -- insert into the shared table
87: INSERT INTO dbo.history (table_name, table_id, [user], [changes])
88: VALUES (@tablename, @id, @username, CAST(@value AS NVARCHAR(MAX)))
89:
90: -- increment the updated rows
91: SET @ROWS = @ROWS + @@ROWCOUNT
92:
93: -- remove the processed record
94: DELETE FROM @tmp
95: WHERE [key] = @id
96: END
97:
98: RETURN @ROWS
99: END