· 8 years ago · Aug 31, 2018, 07:08 PM
1
2GO
3PRINT N'Dropping [clients].[DF_FILEDEFS_GUID]...';
4
5
6GO
7ALTER TABLE [clients].[FILEDEFS] DROP CONSTRAINT [DF_FILEDEFS_GUID];
8
9
10GO
11PRINT N'Dropping [clients].[DF_FILEDEFS_ISTEST]...';
12
13
14GO
15ALTER TABLE [clients].[FILEDEFS] DROP CONSTRAINT [DF_FILEDEFS_ISTEST];
16
17
18GO
19PRINT N'Dropping [clients].[FK_FILEDEFFIELDS_FILEDEFS]...';
20
21
22GO
23ALTER TABLE [clients].[FILEDEFFIELDS] DROP CONSTRAINT [FK_FILEDEFFIELDS_FILEDEFS];
24
25
26GO
27PRINT N'Dropping [clients].[FK_FILEDEFMAILTOS_FILEDEFS]...';
28
29
30GO
31ALTER TABLE [clients].[FILEDEFMAILTOS] DROP CONSTRAINT [FK_FILEDEFMAILTOS_FILEDEFS];
32
33
34GO
35PRINT N'Dropping [clients].[FK_FILEDEFWRITEBACKS_FILEDEFS]...';
36
37
38GO
39ALTER TABLE [clients].[FILEDEFWRITEBACKS] DROP CONSTRAINT [FK_FILEDEFWRITEBACKS_FILEDEFS];
40
41
42GO
43PRINT N'Dropping [clients].[FK_FILES_CLIENTS]...';
44
45
46GO
47ALTER TABLE [clients].[FILEDEFS] DROP CONSTRAINT [FK_FILES_CLIENTS];
48
49
50GO
51PRINT N'Dropping [clients].[FK_FILES_FILETYPES]...';
52
53
54GO
55ALTER TABLE [clients].[FILEDEFS] DROP CONSTRAINT [FK_FILES_FILETYPES];
56
57
58GO
59PRINT N'Dropping [queue].[FK_BATCHES_FILEDEFS]...';
60
61
62GO
63ALTER TABLE [queue].[BATCHES] DROP CONSTRAINT [FK_BATCHES_FILEDEFS];
64
65
66GO
67PRINT N'Dropping [queue].[FK_BATCHHISTORY_FILEDEFS]...';
68
69
70GO
71ALTER TABLE [queue].[BATCHHISTORY] DROP CONSTRAINT [FK_BATCHHISTORY_FILEDEFS];
72
73
74GO
75PRINT N'Starting rebuilding table [clients].[FILEDEFS]...';
76
77
78GO
79BEGIN TRANSACTION;
80
81SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
82
83SET XACT_ABORT ON;
84
85CREATE TABLE [clients].[tmp_ms_xx_FILEDEFS] (
86 [ROWID] [dbo].[ROWID] IDENTITY (1, 1) NOT NULL,
87 [GUID] VARCHAR (256) CONSTRAINT [DF_FILEDEFS_GUID] DEFAULT (newid()) NOT NULL,
88 [CLIENTID] [dbo].[ROWID] NOT NULL,
89 [FILETYPEID] [dbo].[ROWID] NOT NULL,
90 [PORTALUSR] [dbo].[NAME] NULL,
91 [PORTALPWDMD5] CHAR (32) NULL,
92 [LOSBATCHFIELD] [dbo].[NAME] NULL,
93 [LOSDATEFIELD] [dbo].[NAME] NULL,
94 [ISTEST] BIT CONSTRAINT [DF_FILEDEFS_ISTEST] DEFAULT ((0)) NOT NULL,
95 [COPYTOFOLDER] VARCHAR (1000) NULL,
96 [ADDCOPYTOFOLDER] VARCHAR (256) NULL,
97 CONSTRAINT [tmp_ms_xx_constraint_PK_FILES1] PRIMARY KEY CLUSTERED ([ROWID] ASC)
98);
99
100IF EXISTS (SELECT TOP 1 1
101 FROM [clients].[FILEDEFS])
102 BEGIN
103 SET IDENTITY_INSERT [clients].[tmp_ms_xx_FILEDEFS] ON;
104 INSERT INTO [clients].[tmp_ms_xx_FILEDEFS] ([ROWID], [GUID], [CLIENTID], [FILETYPEID], [PORTALUSR], [PORTALPWDMD5], [LOSBATCHFIELD], [ISTEST], [COPYTOFOLDER], [ADDCOPYTOFOLDER])
105 SELECT [ROWID],
106 [GUID],
107 [CLIENTID],
108 [FILETYPEID],
109 [PORTALUSR],
110 [PORTALPWDMD5],
111 [LOSBATCHFIELD],
112 [ISTEST],
113 [COPYTOFOLDER],
114 [ADDCOPYTOFOLDER]
115 FROM [clients].[FILEDEFS]
116 ORDER BY [ROWID] ASC;
117 SET IDENTITY_INSERT [clients].[tmp_ms_xx_FILEDEFS] OFF;
118 END
119
120DROP TABLE [clients].[FILEDEFS];
121
122EXECUTE sp_rename N'[clients].[tmp_ms_xx_FILEDEFS]', N'FILEDEFS';
123
124EXECUTE sp_rename N'[clients].[tmp_ms_xx_constraint_PK_FILES1]', N'PK_FILES', N'OBJECT';
125
126COMMIT TRANSACTION;
127
128SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
129
130
131GO
132PRINT N'Creating [clients].[FILEDEFS].[UX_FILES_CLIENT_FILETYPE_TEST]...';
133
134
135GO
136CREATE UNIQUE NONCLUSTERED INDEX [UX_FILES_CLIENT_FILETYPE_TEST]
137 ON [clients].[FILEDEFS]([CLIENTID] ASC, [FILETYPEID] ASC, [ISTEST] ASC);
138
139
140GO
141PRINT N'Creating [clients].[FILEDEFS].[UX_FILES_GUID]...';
142
143
144GO
145CREATE UNIQUE NONCLUSTERED INDEX [UX_FILES_GUID]
146 ON [clients].[FILEDEFS]([GUID] ASC);
147
148
149GO
150PRINT N'Creating [clients].[FILEDEFS].[IX_FILES_CLIENTID]...';
151
152
153GO
154CREATE NONCLUSTERED INDEX [IX_FILES_CLIENTID]
155 ON [clients].[FILEDEFS]([CLIENTID] ASC);
156
157
158GO
159PRINT N'Creating [clients].[FILEDEFS].[IX_FILES_FILETYPEID]...';
160
161
162GO
163CREATE NONCLUSTERED INDEX [IX_FILES_FILETYPEID]
164 ON [clients].[FILEDEFS]([FILETYPEID] ASC);
165
166
167GO
168PRINT N'Creating [bi].[HOLIDAYS]...';
169
170
171GO
172CREATE TABLE [bi].[HOLIDAYS] (
173 [ROWID] [dbo].[ROWID] IDENTITY (1, 1) NOT NULL,
174 [NAME] [dbo].[NAME] NOT NULL,
175 [UPDATEDON] DATE NOT NULL,
176 [UPDATEDBY] [dbo].[NAME] NOT NULL,
177 [DATED] DATE NOT NULL,
178 [YEAR] AS (datepart(year, [DATED])),
179 CONSTRAINT [PK_HOLIDAYS] PRIMARY KEY CLUSTERED ([ROWID] ASC)
180);
181
182
183GO
184PRINT N'Creating [bi].[HOLIDAYS].[IX_HOLIDAYS_DATED]...';
185
186
187GO
188CREATE UNIQUE NONCLUSTERED INDEX [IX_HOLIDAYS_DATED]
189 ON [bi].[HOLIDAYS]([DATED] ASC);
190
191
192GO
193PRINT N'Creating [bi].[HOLIDAYS].[IX_HOLIDAYS_YEAR]...';
194
195
196GO
197CREATE NONCLUSTERED INDEX [IX_HOLIDAYS_YEAR]
198 ON [bi].[HOLIDAYS]([YEAR] ASC);
199
200
201GO
202PRINT N'Creating [bi].[MILESTONES]...';
203
204
205GO
206CREATE TABLE [bi].[MILESTONES] (
207 [ROWID] [dbo].[ROWID] IDENTITY (1, 1) NOT NULL,
208 [NAME] [dbo].[NAME] NOT NULL,
209 [UPDATEDON] DATE NOT NULL,
210 [UPDATEDBY] [dbo].[NAME] NOT NULL,
211 [NAMEOVERRIDE] [dbo].[NAME] NULL,
212 [TREATAS] AS (isnull([NAMEOVERRIDE], [NAME])),
213 [ORDERINDEX] FLOAT (53) NOT NULL,
214 CONSTRAINT [PK_MILESTONES] PRIMARY KEY CLUSTERED ([ROWID] ASC)
215);
216
217
218GO
219PRINT N'Creating [bi].[MILESTONES].[IX_MILESTONES_NAME]...';
220
221
222GO
223CREATE UNIQUE NONCLUSTERED INDEX [IX_MILESTONES_NAME]
224 ON [bi].[MILESTONES]([NAME] ASC);
225
226
227GO
228PRINT N'Creating [bi].[MILESTONES].[IX_MILESTONES_ORDERINDEX]...';
229
230
231GO
232CREATE NONCLUSTERED INDEX [IX_MILESTONES_ORDERINDEX]
233 ON [bi].[MILESTONES]([ORDERINDEX] ASC);
234
235
236GO
237PRINT N'Creating [clients].[FILEDEFFTPTOS]...';
238
239
240GO
241CREATE TABLE [clients].[FILEDEFFTPTOS] (
242 [RowId] [dbo].[ROWID] IDENTITY (1, 1) NOT NULL,
243 [FileDefId] [dbo].[ROWID] NOT NULL,
244 [HostName] VARCHAR (80) NULL,
245 [UserName] VARCHAR (80) NULL,
246 [Password] VARCHAR (80) NULL,
247 [SrcFolder] VARCHAR (256) NULL,
248 [DestFolder] VARCHAR (256) NULL,
249 [DestFileName] VARCHAR (80) NULL,
250 [PortNum] SMALLINT NULL,
251 [FileProtocol] VARCHAR (16) NULL,
252 [SFTPHostKeyFP] VARCHAR (80) NULL,
253 [FTPEncryption] TINYINT NULL,
254 CONSTRAINT [PK_FILEDEFFTPTOS] PRIMARY KEY CLUSTERED ([RowId] ASC)
255);
256
257
258GO
259PRINT N'Creating [clients].[FK_FILEDEFFIELDS_FILEDEFS]...';
260
261
262GO
263ALTER TABLE [clients].[FILEDEFFIELDS] WITH NOCHECK
264 ADD CONSTRAINT [FK_FILEDEFFIELDS_FILEDEFS] FOREIGN KEY ([FILEDEFID]) REFERENCES [clients].[FILEDEFS] ([ROWID]) ON DELETE CASCADE;
265
266
267GO
268PRINT N'Creating [clients].[FK_FILEDEFMAILTOS_FILEDEFS]...';
269
270
271GO
272ALTER TABLE [clients].[FILEDEFMAILTOS] WITH NOCHECK
273 ADD CONSTRAINT [FK_FILEDEFMAILTOS_FILEDEFS] FOREIGN KEY ([FILEDEFID]) REFERENCES [clients].[FILEDEFS] ([ROWID]) ON DELETE CASCADE;
274
275
276GO
277PRINT N'Creating [clients].[FK_FILEDEFWRITEBACKS_FILEDEFS]...';
278
279
280GO
281ALTER TABLE [clients].[FILEDEFWRITEBACKS] WITH NOCHECK
282 ADD CONSTRAINT [FK_FILEDEFWRITEBACKS_FILEDEFS] FOREIGN KEY ([FILEDEFID]) REFERENCES [clients].[FILEDEFS] ([ROWID]);
283
284
285GO
286PRINT N'Creating [clients].[FK_FILES_CLIENTS]...';
287
288
289GO
290ALTER TABLE [clients].[FILEDEFS] WITH NOCHECK
291 ADD CONSTRAINT [FK_FILES_CLIENTS] FOREIGN KEY ([CLIENTID]) REFERENCES [clients].[CLIENTS] ([ROWID]) ON DELETE CASCADE;
292
293
294GO
295PRINT N'Creating [clients].[FK_FILES_FILETYPES]...';
296
297
298GO
299ALTER TABLE [clients].[FILEDEFS] WITH NOCHECK
300 ADD CONSTRAINT [FK_FILES_FILETYPES] FOREIGN KEY ([FILETYPEID]) REFERENCES [companies].[FILETYPES] ([ROWID]);
301
302
303GO
304PRINT N'Creating [queue].[FK_BATCHES_FILEDEFS]...';
305
306
307GO
308ALTER TABLE [queue].[BATCHES] WITH NOCHECK
309 ADD CONSTRAINT [FK_BATCHES_FILEDEFS] FOREIGN KEY ([FILEDEFID]) REFERENCES [clients].[FILEDEFS] ([ROWID]);
310
311
312GO
313PRINT N'Creating [queue].[FK_BATCHHISTORY_FILEDEFS]...';
314
315
316GO
317ALTER TABLE [queue].[BATCHHISTORY] WITH NOCHECK
318 ADD CONSTRAINT [FK_BATCHHISTORY_FILEDEFS] FOREIGN KEY ([FILEDEFID]) REFERENCES [clients].[FILEDEFS] ([ROWID]);
319
320
321GO
322PRINT N'Creating [clients].[FK_FILEDEFFTPTOS_FILEDEFS]...';
323
324
325GO
326ALTER TABLE [clients].[FILEDEFFTPTOS] WITH NOCHECK
327 ADD CONSTRAINT [FK_FILEDEFFTPTOS_FILEDEFS] FOREIGN KEY ([FileDefId]) REFERENCES [clients].[FILEDEFS] ([ROWID]) ON DELETE CASCADE;
328
329
330GO
331PRINT N'Creating [bi].[AUDIT_TRIGGER_bi_HOLIDAYS]...';
332
333
334GO
335CREATE TRIGGER [bi].AUDIT_TRIGGER_bi_HOLIDAYS ON bi.HOLIDAYS
336 AFTER INSERT, UPDATE, DELETE AS BEGIN
337 SET NOCOUNT ON;
338
339 DECLARE @SCHEMA VARCHAR(100); SELECT @SCHEMA = 'bi'
340 DECLARE @TABLE VARCHAR(100); SELECT @TABLE = 'HOLIDAYS'
341
342 DECLARE @ICOUNT int; SELECT @ICOUNT = COUNT(1) FROM INSERTED
343 DECLARE @DCOUNT int; SELECT @DCOUNT = COUNT(1) FROM DELETED
344 DECLARE @ACTION CHAR(1); SELECT @ACTION = CASE @ICOUNT+@DCOUNT WHEN @ICOUNT THEN 'I' WHEN @DCOUNT THEN 'D' ELSE 'U' END
345
346 DECLARE @AUDID bigint
347 DECLARE @IROWID bigint
348 DECLARE @DROWID bigint
349 DECLARE @INAME varchar(100)
350 DECLARE @DNAME varchar(100)
351 DECLARE @IUPDATEDON date
352 DECLARE @DUPDATEDON date
353 DECLARE @IUPDATEDBY varchar(100)
354 DECLARE @DUPDATEDBY varchar(100)
355 DECLARE @IDATED date
356 DECLARE @DDATED date
357 DECLARE @IYEAR int
358 DECLARE @DYEAR int
359
360 IF @ACTION = 'I' BEGIN
361 DECLARE inserts CURSOR FOR SELECT ROWID,NAME,UPDATEDON,UPDATEDBY,DATED,YEAR FROM INSERTED
362 OPEN inserts
363 FETCH NEXT FROM inserts INTO @IROWID,@INAME,@IUPDATEDON,@IUPDATEDBY,@IDATED,@IYEAR
364 WHILE @@FETCH_STATUS = 0 BEGIN
365 EXECUTE @AUDID = audits.GenerateAuditId 'I'
366
367 -- PRIMARY KEY + ALWAYS
368 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'ROWID', NULL, @IROWID)
369
370 -- ALL OTHER FIELDS
371 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'NAME', NULL, @INAME)
372 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'UPDATEDON', NULL, @IUPDATEDON)
373 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'UPDATEDBY', NULL, @IUPDATEDBY)
374 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'DATED', NULL, @IDATED)
375 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'YEAR', NULL, @IYEAR)
376
377 FETCH NEXT FROM inserts INTO @IROWID,@INAME,@IUPDATEDON,@IUPDATEDBY,@IDATED,@IYEAR
378 END
379 CLOSE inserts
380 DEALLOCATE inserts
381 END
382
383 IF @ACTION = 'U' BEGIN
384 DECLARE inserts CURSOR FOR SELECT ROWID,NAME,UPDATEDON,UPDATEDBY,DATED,YEAR FROM INSERTED
385 OPEN inserts
386 DECLARE deletes CURSOR FOR SELECT ROWID,NAME,UPDATEDON,UPDATEDBY,DATED,YEAR FROM DELETED
387 OPEN deletes
388 FETCH NEXT FROM inserts INTO @IROWID,@INAME,@IUPDATEDON,@IUPDATEDBY,@IDATED,@IYEAR
389 FETCH NEXT FROM deletes INTO @DROWID,@DNAME,@DUPDATEDON,@DUPDATEDBY,@DDATED,@DYEAR
390 WHILE @@FETCH_STATUS = 0 BEGIN
391 EXECUTE @AUDID = audits.GenerateAuditId 'U'
392
393 -- PRIMARY KEY + ALWAYS
394 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'ROWID', @DROWID, @IROWID)
395
396 -- ALL OTHER FIELDS
397 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) SELECT @AUDID, @SCHEMA, @TABLE, 'NAME', @DNAME, @INAME WHERE NOT (@DNAME IS NULL AND @INAME IS NULL) AND (@DNAME <> @INAME OR @DNAME IS NULL OR @INAME IS NULL)
398 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) SELECT @AUDID, @SCHEMA, @TABLE, 'UPDATEDON', @DUPDATEDON, @IUPDATEDON WHERE NOT (@DUPDATEDON IS NULL AND @IUPDATEDON IS NULL) AND (@DUPDATEDON <> @IUPDATEDON OR @DUPDATEDON IS NULL OR @IUPDATEDON IS NULL)
399 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) SELECT @AUDID, @SCHEMA, @TABLE, 'UPDATEDBY', @DUPDATEDBY, @IUPDATEDBY WHERE NOT (@DUPDATEDBY IS NULL AND @IUPDATEDBY IS NULL) AND (@DUPDATEDBY <> @IUPDATEDBY OR @DUPDATEDBY IS NULL OR @IUPDATEDBY IS NULL)
400 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) SELECT @AUDID, @SCHEMA, @TABLE, 'DATED', @DDATED, @IDATED WHERE NOT (@DDATED IS NULL AND @IDATED IS NULL) AND (@DDATED <> @IDATED OR @DDATED IS NULL OR @IDATED IS NULL)
401 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) SELECT @AUDID, @SCHEMA, @TABLE, 'YEAR', @DYEAR, @IYEAR WHERE NOT (@DYEAR IS NULL AND @IYEAR IS NULL) AND (@DYEAR <> @IYEAR OR @DYEAR IS NULL OR @IYEAR IS NULL)
402
403 FETCH NEXT FROM inserts INTO @IROWID,@INAME,@IUPDATEDON,@IUPDATEDBY,@IDATED,@IYEAR
404 FETCH NEXT FROM deletes INTO @DROWID,@DNAME,@DUPDATEDON,@DUPDATEDBY,@DDATED,@DYEAR
405 END
406 CLOSE inserts
407 DEALLOCATE inserts
408 CLOSE deletes
409 DEALLOCATE deletes
410 END
411
412 IF @ACTION = 'D' BEGIN
413 DECLARE deletes CURSOR FOR SELECT ROWID,NAME,UPDATEDON,UPDATEDBY,DATED,YEAR FROM DELETED
414 OPEN deletes
415 FETCH NEXT FROM deletes INTO @DROWID,@DNAME,@DUPDATEDON,@DUPDATEDBY,@DDATED,@DYEAR
416 WHILE @@FETCH_STATUS = 0 BEGIN
417 EXECUTE @AUDID = audits.GenerateAuditId 'D'
418
419 -- PRIMARY KEY + ALWAYS
420 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'ROWID', @DROWID, NULL)
421
422 -- ALL OTHER FIELDS
423 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'NAME', @DNAME, NULL)
424 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'UPDATEDON', @DUPDATEDON, NULL)
425 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'UPDATEDBY', @DUPDATEDBY, NULL)
426 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'DATED', @DDATED, NULL)
427 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'YEAR', @DYEAR, NULL)
428
429 FETCH NEXT FROM deletes INTO @DROWID,@DNAME,@DUPDATEDON,@DUPDATEDBY,@DDATED,@DYEAR
430 END
431 CLOSE deletes
432 DEALLOCATE deletes
433 END
434
435END
436GO
437PRINT N'Creating [bi].[AUDIT_TRIGGER_bi_MILESTONES]...';
438
439
440GO
441CREATE TRIGGER [bi].AUDIT_TRIGGER_bi_MILESTONES ON bi.MILESTONES
442 AFTER INSERT, UPDATE, DELETE AS BEGIN
443 SET NOCOUNT ON;
444
445 DECLARE @SCHEMA VARCHAR(100); SELECT @SCHEMA = 'bi'
446 DECLARE @TABLE VARCHAR(100); SELECT @TABLE = 'MILESTONES'
447
448 DECLARE @ICOUNT int; SELECT @ICOUNT = COUNT(1) FROM INSERTED
449 DECLARE @DCOUNT int; SELECT @DCOUNT = COUNT(1) FROM DELETED
450 DECLARE @ACTION CHAR(1); SELECT @ACTION = CASE @ICOUNT+@DCOUNT WHEN @ICOUNT THEN 'I' WHEN @DCOUNT THEN 'D' ELSE 'U' END
451
452 DECLARE @AUDID bigint
453 DECLARE @IROWID bigint
454 DECLARE @DROWID bigint
455 DECLARE @INAME varchar(100)
456 DECLARE @DNAME varchar(100)
457 DECLARE @IUPDATEDON date
458 DECLARE @DUPDATEDON date
459 DECLARE @IUPDATEDBY varchar(100)
460 DECLARE @DUPDATEDBY varchar(100)
461 DECLARE @INAMEOVERRIDE varchar(100)
462 DECLARE @DNAMEOVERRIDE varchar(100)
463 DECLARE @ITREATAS varchar(100)
464 DECLARE @DTREATAS varchar(100)
465 DECLARE @IORDERINDEX float
466 DECLARE @DORDERINDEX float
467
468 IF @ACTION = 'I' BEGIN
469 DECLARE inserts CURSOR FOR SELECT ROWID,NAME,UPDATEDON,UPDATEDBY,NAMEOVERRIDE,TREATAS,ORDERINDEX FROM INSERTED
470 OPEN inserts
471 FETCH NEXT FROM inserts INTO @IROWID,@INAME,@IUPDATEDON,@IUPDATEDBY,@INAMEOVERRIDE,@ITREATAS,@IORDERINDEX
472 WHILE @@FETCH_STATUS = 0 BEGIN
473 EXECUTE @AUDID = audits.GenerateAuditId 'I'
474
475 -- PRIMARY KEY + ALWAYS
476 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'ROWID', NULL, @IROWID)
477
478 -- ALL OTHER FIELDS
479 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'NAME', NULL, @INAME)
480 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'UPDATEDON', NULL, @IUPDATEDON)
481 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'UPDATEDBY', NULL, @IUPDATEDBY)
482 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'NAMEOVERRIDE', NULL, @INAMEOVERRIDE)
483 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'TREATAS', NULL, @ITREATAS)
484 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'ORDERINDEX', NULL, @IORDERINDEX)
485
486 FETCH NEXT FROM inserts INTO @IROWID,@INAME,@IUPDATEDON,@IUPDATEDBY,@INAMEOVERRIDE,@ITREATAS,@IORDERINDEX
487 END
488 CLOSE inserts
489 DEALLOCATE inserts
490 END
491
492 IF @ACTION = 'U' BEGIN
493 DECLARE inserts CURSOR FOR SELECT ROWID,NAME,UPDATEDON,UPDATEDBY,NAMEOVERRIDE,TREATAS,ORDERINDEX FROM INSERTED
494 OPEN inserts
495 DECLARE deletes CURSOR FOR SELECT ROWID,NAME,UPDATEDON,UPDATEDBY,NAMEOVERRIDE,TREATAS,ORDERINDEX FROM DELETED
496 OPEN deletes
497 FETCH NEXT FROM inserts INTO @IROWID,@INAME,@IUPDATEDON,@IUPDATEDBY,@INAMEOVERRIDE,@ITREATAS,@IORDERINDEX
498 FETCH NEXT FROM deletes INTO @DROWID,@DNAME,@DUPDATEDON,@DUPDATEDBY,@DNAMEOVERRIDE,@DTREATAS,@DORDERINDEX
499 WHILE @@FETCH_STATUS = 0 BEGIN
500 EXECUTE @AUDID = audits.GenerateAuditId 'U'
501
502 -- PRIMARY KEY + ALWAYS
503 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'ROWID', @DROWID, @IROWID)
504
505 -- ALL OTHER FIELDS
506 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) SELECT @AUDID, @SCHEMA, @TABLE, 'NAME', @DNAME, @INAME WHERE NOT (@DNAME IS NULL AND @INAME IS NULL) AND (@DNAME <> @INAME OR @DNAME IS NULL OR @INAME IS NULL)
507 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) SELECT @AUDID, @SCHEMA, @TABLE, 'UPDATEDON', @DUPDATEDON, @IUPDATEDON WHERE NOT (@DUPDATEDON IS NULL AND @IUPDATEDON IS NULL) AND (@DUPDATEDON <> @IUPDATEDON OR @DUPDATEDON IS NULL OR @IUPDATEDON IS NULL)
508 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) SELECT @AUDID, @SCHEMA, @TABLE, 'UPDATEDBY', @DUPDATEDBY, @IUPDATEDBY WHERE NOT (@DUPDATEDBY IS NULL AND @IUPDATEDBY IS NULL) AND (@DUPDATEDBY <> @IUPDATEDBY OR @DUPDATEDBY IS NULL OR @IUPDATEDBY IS NULL)
509 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) SELECT @AUDID, @SCHEMA, @TABLE, 'NAMEOVERRIDE', @DNAMEOVERRIDE, @INAMEOVERRIDE WHERE NOT (@DNAMEOVERRIDE IS NULL AND @INAMEOVERRIDE IS NULL) AND (@DNAMEOVERRIDE <> @INAMEOVERRIDE OR @DNAMEOVERRIDE IS NULL OR @INAMEOVERRIDE IS NULL)
510 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) SELECT @AUDID, @SCHEMA, @TABLE, 'TREATAS', @DTREATAS, @ITREATAS WHERE NOT (@DTREATAS IS NULL AND @ITREATAS IS NULL) AND (@DTREATAS <> @ITREATAS OR @DTREATAS IS NULL OR @ITREATAS IS NULL)
511 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) SELECT @AUDID, @SCHEMA, @TABLE, 'ORDERINDEX', @DORDERINDEX, @IORDERINDEX WHERE NOT (@DORDERINDEX IS NULL AND @IORDERINDEX IS NULL) AND (@DORDERINDEX <> @IORDERINDEX OR @DORDERINDEX IS NULL OR @IORDERINDEX IS NULL)
512
513 FETCH NEXT FROM inserts INTO @IROWID,@INAME,@IUPDATEDON,@IUPDATEDBY,@INAMEOVERRIDE,@ITREATAS,@IORDERINDEX
514 FETCH NEXT FROM deletes INTO @DROWID,@DNAME,@DUPDATEDON,@DUPDATEDBY,@DNAMEOVERRIDE,@DTREATAS,@DORDERINDEX
515 END
516 CLOSE inserts
517 DEALLOCATE inserts
518 CLOSE deletes
519 DEALLOCATE deletes
520 END
521
522 IF @ACTION = 'D' BEGIN
523 DECLARE deletes CURSOR FOR SELECT ROWID,NAME,UPDATEDON,UPDATEDBY,NAMEOVERRIDE,TREATAS,ORDERINDEX FROM DELETED
524 OPEN deletes
525 FETCH NEXT FROM deletes INTO @DROWID,@DNAME,@DUPDATEDON,@DUPDATEDBY,@DNAMEOVERRIDE,@DTREATAS,@DORDERINDEX
526 WHILE @@FETCH_STATUS = 0 BEGIN
527 EXECUTE @AUDID = audits.GenerateAuditId 'D'
528
529 -- PRIMARY KEY + ALWAYS
530 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'ROWID', @DROWID, NULL)
531
532 -- ALL OTHER FIELDS
533 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'NAME', @DNAME, NULL)
534 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'UPDATEDON', @DUPDATEDON, NULL)
535 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'UPDATEDBY', @DUPDATEDBY, NULL)
536 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'NAMEOVERRIDE', @DNAMEOVERRIDE, NULL)
537 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'TREATAS', @DTREATAS, NULL)
538 INSERT INTO audits.TRAIL (AUDITID, TABLE_SCHEMA, TABLE_NAME, TABLE_FIELD, OLDVAL, NEWVAL) VALUES (@AUDID, @SCHEMA, @TABLE, 'ORDERINDEX', @DORDERINDEX, NULL)
539
540 FETCH NEXT FROM deletes INTO @DROWID,@DNAME,@DUPDATEDON,@DUPDATEDBY,@DNAMEOVERRIDE,@DTREATAS,@DORDERINDEX
541 END
542 CLOSE deletes
543 DEALLOCATE deletes
544 END
545
546END
547GO
548PRINT N'Refreshing [queue].[BATCHHISTORY_EX]...';
549
550
551GO
552EXECUTE sp_refreshsqlmodule N'[queue].[BATCHHISTORY_EX]';
553
554
555GO
556PRINT N'Refreshing [system].[setup]...';
557
558
559GO
560EXECUTE sp_refreshsqlmodule N'[system].[setup]';
561
562
563GO
564PRINT N'Refreshing [clients].[CloneClient]...';
565
566
567GO
568EXECUTE sp_refreshsqlmodule N'[clients].[CloneClient]';
569
570
571GO
572PRINT N'Refreshing [companies].[SendFileSummaries]...';
573
574
575GO
576EXECUTE sp_refreshsqlmodule N'[companies].[SendFileSummaries]';
577
578
579GO
580PRINT N'Checking existing data against newly created constraints';
581
582
583GO
584USE [$(DatabaseName)];
585
586
587GO
588ALTER TABLE [clients].[FILEDEFFIELDS] WITH CHECK CHECK CONSTRAINT [FK_FILEDEFFIELDS_FILEDEFS];
589
590ALTER TABLE [clients].[FILEDEFMAILTOS] WITH CHECK CHECK CONSTRAINT [FK_FILEDEFMAILTOS_FILEDEFS];
591
592ALTER TABLE [clients].[FILEDEFWRITEBACKS] WITH CHECK CHECK CONSTRAINT [FK_FILEDEFWRITEBACKS_FILEDEFS];
593
594ALTER TABLE [clients].[FILEDEFS] WITH CHECK CHECK CONSTRAINT [FK_FILES_CLIENTS];
595
596ALTER TABLE [clients].[FILEDEFS] WITH CHECK CHECK CONSTRAINT [FK_FILES_FILETYPES];
597
598ALTER TABLE [queue].[BATCHES] WITH CHECK CHECK CONSTRAINT [FK_BATCHES_FILEDEFS];
599
600ALTER TABLE [queue].[BATCHHISTORY] WITH CHECK CHECK CONSTRAINT [FK_BATCHHISTORY_FILEDEFS];
601
602ALTER TABLE [clients].[FILEDEFFTPTOS] WITH CHECK CHECK CONSTRAINT [FK_FILEDEFFTPTOS_FILEDEFS];
603
604
605GO
606PRINT N'Update complete.';
607
608
609GO