· 8 years ago · Jan 21, 2018, 01:38 AM
1----------------------------------------------------------------------------------------------------
2-- AUDITING WITH TRIGGERS
3-- Written by Daniel Loth
4----------------------------------------------------------------------------------------------------
5
6--------------------------------------------------------------------------------
7-- Populate a numbers table
8--------------------------------------------------------------------------------
9drop table if exists Number
10go
11
12;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
13 ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
14 ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
15 ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
16 ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
17 ,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 65,536
18 ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5)
19
20select n-1 as Number
21into Number
22from Tally
23where n < 5001
24go
25
26
27--------------------------------------------------------------------------------
28-- Create audit number sequence generator
29--------------------------------------------------------------------------------
30drop sequence if exists audit_person_sequence
31go
32CREATE SEQUENCE [dbo].[audit_person_sequence]
33 AS [int]
34 MINVALUE 1
35 CACHE 1000
36
37GO
38
39--------------------------------------------------------------------------------
40-- Drop tables (if they exist) and then create them
41--------------------------------------------------------------------------------
42drop table if exists audit_person_created
43drop table if exists audit_person_deleted
44drop table if exists audit_person_updated
45drop table if exists audit_person
46drop table if exists person
47go
48
49create table person (
50 PersonNo int primary key,
51 GivenNames nvarchar(100) not null,
52 FamilyName nvarchar(100) not null
53)
54
55create table audit_person (
56 AuditId int primary key,
57 AuditDtm datetime2(0) not null
58)
59
60create table audit_person_created (
61 AuditId int primary key,
62 PersonNo int not null,
63 GivenNames nvarchar(100) not null,
64 FamilyName nvarchar(100) not null,
65
66 foreign key (AuditId) references audit_person(AuditId)
67)
68
69create table audit_person_deleted (
70 AuditId int primary key,
71 PersonNo int not null,
72 GivenNames nvarchar(100) not null,
73 FamilyName nvarchar(100) not null,
74
75 foreign key (AuditId) references audit_person(AuditId)
76)
77
78create table audit_person_updated (
79 AuditId int not null,
80 PersonNo int not null,
81 ColName nvarchar(128) not null,
82 OldValue nvarchar(100) not null,
83 NewValue nvarchar(100) not null,
84
85 primary key (AuditId, PersonNo, ColName),
86 foreign key (AuditId) references audit_person(AuditId)
87)
88
89go
90
91
92--------------------------------------------------------------------------------
93-- INSERT TRIGGER
94-- This trigger facilitates auditing of newly inserted person records.
95--------------------------------------------------------------------------------
96create trigger audit_person_insert_tr
97on person
98for insert
99as
100begin
101 declare @NowUtc datetime2(0) = getutcdate()
102
103 declare @Inserted table (
104 AuditId int primary key,
105 PersonNo int not null,
106 GivenNames nvarchar(100) not null,
107 FamilyName nvarchar(100) not null
108 )
109
110 insert into @Inserted (AuditId, PersonNo, GivenNames, FamilyName)
111 select next value for audit_person_sequence, i.PersonNo, i.GivenNames, i.FamilyName
112 from inserted i
113
114 insert into audit_person (AuditId, AuditDtm)
115 select i.AuditId, @NowUtc
116 from @Inserted i
117
118 insert into audit_person_created (AuditId, PersonNo, GivenNames, FamilyName)
119 select i.AuditId, i.PersonNo, i.GivenNames, i.FamilyName
120 from @Inserted i
121end
122go
123
124
125--------------------------------------------------------------------------------
126-- DELETE TRIGGER
127--
128-- This trigger facilitates auditing deleted person records.
129-- The audit_person_delete record will have column values as they existed in
130-- the person table prior to the DELETE query.
131--------------------------------------------------------------------------------
132create trigger audit_person_delete
133on person
134for delete
135as
136begin
137 declare @NowUtc datetime2(0) = getutcdate()
138
139 declare @Deleted table (
140 AuditId int primary key,
141 PersonNo int not null,
142 GivenNames nvarchar(100) not null,
143 FamilyName nvarchar(100) not null
144 )
145
146 insert into @Deleted (AuditId, PersonNo, GivenNames, FamilyName)
147 select next value for audit_person_sequence, d.PersonNo, d.GivenNames, d.FamilyName
148 from deleted d
149
150 insert into audit_person (AuditId, AuditDtm)
151 select d.AuditId, @NowUtc
152 from @Deleted d
153
154 insert into audit_person_deleted (AuditId, PersonNo, GivenNames, FamilyName)
155 select d.AuditId, d.PersonNo, d.GivenNames, d.FamilyName
156 from @Deleted d
157end
158go
159
160
161--------------------------------------------------------------------------------
162-- UPDATE TRIGGER
163-- This trigger records the new values of fields after an UPDATE query runs.
164--------------------------------------------------------------------------------
165create trigger audit_person_update
166on person
167for update
168as
169begin
170 declare @NowUtc datetime2(0) = getutcdate()
171
172 declare @Deleted table (
173 PersonNo int,
174 ColName nvarchar(128) not null,
175 OldValue nvarchar(100) not null,
176
177 primary key (PersonNo, ColName)
178 )
179
180 declare @Inserted table (
181 PersonNo int,
182 ColName nvarchar(128) not null,
183 NewValue nvarchar(100) not null,
184
185 primary key (PersonNo, ColName)
186 )
187
188 insert into @Inserted (PersonNo, ColName, NewValue)
189 select PersonNoIdentity, ColName, NewValue
190 from (
191 select
192 PersonNo as PersonNoIdentity,
193 cast(PersonNo as nvarchar(100)) as PersonNo,
194 GivenNames, FamilyName
195 from inserted
196 ) s
197 unpivot (
198 NewValue for ColName in (PersonNo, GivenNames, FamilyName) -- Pivot columns must be same data type AND length.
199 ) up
200
201 insert into @Deleted (PersonNo, ColName, OldValue)
202 select PersonNoIdentity, ColName, NewValue
203 from (
204 select PersonNo as PersonNoIdentity, cast(PersonNo as nvarchar(100)) as PersonNo, GivenNames, FamilyName
205 from deleted
206 ) s
207 unpivot (
208 NewValue for ColName in (PersonNo, GivenNames, FamilyName) -- Pivot columns must be same data type AND length.
209 ) up
210
211
212 declare @Updated table (
213 AuditId int,
214 PersonNo int,
215 ColName nvarchar(128) not null,
216 OldValue nvarchar(100) not null,
217 NewValue nvarchar(100) not null,
218
219 primary key (PersonNo, ColName)
220 )
221
222 declare @AuditIdToPersonNo table (
223 PersonNo int not null,
224 AuditId int not null,
225
226 primary key (PersonNo)
227 )
228
229 insert into @AuditIdToPersonNo (PersonNo, AuditId)
230 select PersonNo, next value for audit_person_sequence
231 from (
232 select distinct i.PersonNo
233 from @Inserted i
234 inner join @Deleted d
235 on i.PersonNo = d.PersonNo
236 and i.ColName = d.ColName
237 where i.NewValue <> d.OldValue
238 ) s
239
240 insert into @Updated (AuditId, PersonNo, ColName, OldValue, NewValue)
241 select u.AuditId, u.PersonNo, u.ColName, u.OldValue, u.NewValue
242 from (
243 select atp.AuditId, i.PersonNo, i.ColName, d.OldValue, i.NewValue
244 from @Inserted i
245 inner join @Deleted d
246 on i.PersonNo = d.PersonNo
247 and i.ColName = d.ColName
248 inner join @AuditIdToPersonNo atp
249 on i.PersonNo = atp.PersonNo
250 where i.NewValue <> d.OldValue
251 ) u
252
253 insert into audit_person (AuditId, AuditDtm)
254 select u.AuditId, @NowUtc
255 from @AuditIdToPersonNo u
256
257 insert into audit_person_updated (AuditId, PersonNo, ColName, OldValue, NewValue)
258 select AuditId, PersonNo, ColName, OldValue, NewValue
259 from @Updated u
260
261end
262go
263
264insert into Person (PersonNo, GivenNames, FamilyName)
265select Number, concat('GivenName-', Number), concat('FamilyName-', Number)
266from Number
267order by Number
268
269update Person
270set GivenNames = 'Daniel', FamilyName = 'Loth' -- UNSUPPORTED: Primary key update :( - PersonNo = (PersonNo + 1)
271where GivenNames in ('GivenName-0', 'GivenName-3')
272
273delete Person where FamilyName = 'Loth'
274
275select c.AuditId, p.AuditDtm, c.PersonNo, c.GivenNames, c.FamilyName
276from audit_person p
277inner join audit_person_created c
278 on p.AuditId = c.AuditId
279
280select u.AuditId, p.AuditDtm, u.ColName, u.OldValue, u.NewValue
281from audit_person p
282inner join audit_person_updated u
283 on p.AuditId = u.AuditId
284
285select d.AuditId, p.AuditDtm, d.PersonNo, d.GivenNames, d.FamilyName
286from audit_person p
287inner join audit_person_deleted d
288 on p.AuditId = d.AuditId