· 8 years ago · Aug 27, 2018, 01:10 AM
1CREATE PROCEDURE [dbo].[RowLevelAuditAdd]
2 @SchemaName NVARCHAR(50) = NULL
3 , @TableName NVARCHAR(50) = NULL
4AS
5BEGIN
6
7/*
8'--------------------------------------------------------------------------------------------------------------------
9' Purpose: Adds row level auditing columns to a table
10' Example: EXEC dbo.RowLevelAuditAdd 'dbo', 'ORGN';
11'--------------------------------------------------------------------------------------------------------------------
12
13
14 -----------------------------------------------------
15 -->>>>>>>>>>>>>>>>> FOR DEBUGGING <<<<<<<<<<<<<<<<<<<
16 -----------------------------------------------------
17 BEGIN
18 DECLARE @SchemaName NVARCHAR(50)
19 DECLARE @TableName NVARCHAR(50)
20 SET @SchemaName = 'dbo'
21 SET @TableName = 'ORGN'
22 -----------------------------------------------------
23 -----------------------------------------------------
24
25*/
26
27 SET XACT_ABORT ON
28 BEGIN TRANSACTION;
29 SET NOCOUNT ON
30
31 DECLARE @SqlCommand NVARCHAR(1000)
32 DECLARE @TableKey NVARCHAR(1000)
33 DECLARE @UserName NVARCHAR(50)
34 DECLARE @CreatedId NVARCHAR(50)
35 DECLARE @CreatedDate NVARCHAR(50)
36 DECLARE @ModifiedId NVARCHAR(50)
37 DECLARE @ModifiedDate NVARCHAR(50)
38 DECLARE @TodayDate NVARCHAR(50)
39
40 SET @CreatedId = 'CreatedId'
41 SET @CreatedDate = 'CreatedDate'
42 SET @ModifiedId = 'ModifiedId'
43 SET @ModifiedDate = 'ModifiedDate'
44 SET @UserName = LOWER(LEFT(RIGHT(SYSTEM_USER,(LEN(SYSTEM_USER)-CHARINDEX('',SYSTEM_USER))), 50))
45 SET @TodayDate = FORMAT(GETDATE(), 'dd-MMM-yyyy HH:mm:ss', 'en-US' )
46
47 PRINT '=====================================================================';
48 PRINT 'START - ALTER [' + @SchemaName + '].[' + @TableName + ']... ';
49
50 IF COL_LENGTH(@SchemaName + '.' + @TableName, @CreatedId) IS NULL
51 BEGIN
52
53 PRINT '=====================================================================';
54 PRINT 'START - ADD COLUMN [' + @CreatedId + ']... ';
55
56 PRINT '1. alter table add ' + @CreatedId + ' column... ';
57 SET @SqlCommand = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ADD [' + @CreatedId + '] [NVARCHAR](50) NULL'
58 EXEC (@SqlCommand)
59
60 PRINT '2. update new column to a value... ' + @UserName;
61 SET @SqlCommand = 'UPDATE [' + @SchemaName + '].[' + @TableName + '] SET [' + @CreatedId + '] =''' + @UserName + ''' WHERE [' + @CreatedId + '] IS NULL'
62 EXEC (@SqlCommand)
63
64 PRINT '3. alter table alter new column add constraints... ';
65 SET @SqlCommand = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ALTER COLUMN [' + @CreatedId + '] [NVARCHAR](50) NOT NULL'
66 EXEC (@SqlCommand)
67
68 SET @SqlCommand = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ADD CONSTRAINT [DF_' + @TableName + '_' + @CreatedId + '] DEFAULT (LOWER(LEFT(RIGHT(SYSTEM_USER,(LEN(SYSTEM_USER)-CHARINDEX('''',SYSTEM_USER))), 50))) FOR [' + @CreatedId + ']'
69 EXEC (@SqlCommand)
70
71 PRINT '4. add column description... ';
72 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Who created the record' , @level0type=N'SCHEMA',@level0name=@SchemaName, @level1type=N'TABLE',@level1name=@TableName, @level2type=N'COLUMN',@level2name=@CreatedId;
73
74 PRINT 'END - ADD COLUMN [' + @CreatedId + ']... ';
75 PRINT '=====================================================================';
76 END
77
78 IF COL_LENGTH(@SchemaName + '.' + @TableName, @CreatedDate) IS NULL
79 BEGIN
80
81 PRINT '=====================================================================';
82 PRINT 'START - ADD COLUMN [' + @CreatedDate + ']... ';
83
84 PRINT '1. alter table add ' + @CreatedDate + ' column... ';
85 SET @SqlCommand = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ADD [' + @CreatedDate + '] [DATETIME] NULL'
86 EXEC (@SqlCommand)
87
88 PRINT '2. update new column to a value... ' + @TodayDate;
89 SET @SqlCommand = 'UPDATE [' + @SchemaName + '].[' + @TableName + '] SET [' + @CreatedDate + '] = ''' + @TodayDate+ ''' WHERE [' + @CreatedDate + '] IS NULL'
90 EXEC (@SqlCommand)
91
92 PRINT '3. alter table alter new column add constraints... ';
93 SET @SqlCommand = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ALTER COLUMN [' + @CreatedDate + '] [DATETIME] NOT NULL'
94 EXEC (@SqlCommand)
95
96 SET @SqlCommand = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ADD CONSTRAINT [DF_' + @TableName + '_' + @CreatedDate + '] DEFAULT (GETDATE()) FOR [' + @CreatedDate + ']'
97 EXEC (@SqlCommand)
98
99 PRINT '4. add column description... ';
100 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The date and time the record was created' , @level0type=N'SCHEMA',@level0name=@SchemaName, @level1type=N'TABLE',@level1name=@TableName, @level2type=N'COLUMN',@level2name=@CreatedDate;
101
102 PRINT 'END - ADD COLUMN [' + @CreatedDate + ']... ';
103 PRINT '=====================================================================';
104 END
105
106 IF COL_LENGTH(@SchemaName + '.' + @TableName, @ModifiedId) IS NULL
107 BEGIN
108
109 PRINT '=====================================================================';
110 PRINT 'START - ADD COLUMN [' + @ModifiedId + ']... ';
111
112 PRINT '1. alter table add ' + @ModifiedId + ' column... ';
113 SET @SqlCommand = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ADD [' + @ModifiedId + '] [NVARCHAR](50) NULL'
114 EXEC (@SqlCommand)
115
116 PRINT '2. update new column to a value... ' + @UserName;
117 SET @SqlCommand = 'UPDATE [' + @SchemaName + '].[' + @TableName + '] SET [' + @ModifiedId + '] =''' + @UserName + ''' WHERE [' + @ModifiedId + '] IS NULL'
118 EXEC (@SqlCommand)
119
120 PRINT '3. alter table alter new column add constraints... ';
121 SET @SqlCommand = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ALTER COLUMN [' + @ModifiedId + '] [NVARCHAR](50) NOT NULL'
122 EXEC (@SqlCommand)
123
124 SET @SqlCommand = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ADD CONSTRAINT [DF_' + @TableName + '_' + @ModifiedId + '] DEFAULT (LOWER(LEFT(RIGHT(SYSTEM_USER,(LEN(SYSTEM_USER)-CHARINDEX('''',SYSTEM_USER))), 50))) FOR [' + @ModifiedId + ']'
125 EXEC (@SqlCommand)
126
127 PRINT '4. add column description... ';
128 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Who modified the record' , @level0type=N'SCHEMA',@level0name=@SchemaName, @level1type=N'TABLE',@level1name=@TableName, @level2type=N'COLUMN',@level2name=@ModifiedId;
129
130 PRINT 'END - ADD COLUMN [' + @ModifiedId + ']... ';
131 PRINT '=====================================================================';
132 END
133
134 IF COL_LENGTH(@SchemaName + '.' + @TableName, @ModifiedDate) IS NULL
135 BEGIN
136
137 PRINT '=====================================================================';
138 PRINT 'START - ADD COLUMN [' + @ModifiedDate + ']... ';
139
140 PRINT '1. alter table add ' + @ModifiedDate + ' column... ';
141 SET @SqlCommand = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ADD [' + @ModifiedDate + '] [DATETIME] NULL'
142 EXEC (@SqlCommand)
143
144 PRINT '2. update new column to a value... ' + @TodayDate;
145 SET @SqlCommand = 'UPDATE [' + @SchemaName + '].[' + @TableName + '] SET [' + @ModifiedDate + '] = ''' + @TodayDate+ ''' WHERE [' + @ModifiedDate + '] IS NULL'
146 EXEC (@SqlCommand)
147
148 PRINT '3. alter table alter new column add constraints... ';
149 SET @SqlCommand = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ALTER COLUMN [' + @ModifiedDate + '] [DATETIME] NOT NULL'
150 EXEC (@SqlCommand)
151
152 SET @SqlCommand = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ADD CONSTRAINT [DF_' + @TableName + '_' + @ModifiedDate + '] DEFAULT (GETDATE()) FOR [' + @ModifiedDate + ']'
153 EXEC (@SqlCommand)
154
155 PRINT '4. add column description... ';
156 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The date and time the record was modified' , @level0type=N'SCHEMA',@level0name=@SchemaName, @level1type=N'TABLE',@level1name=@TableName, @level2type=N'COLUMN',@level2name=@ModifiedDate;
157
158 PRINT 'END - ADD COLUMN [' + @ModifiedDate + ']... ';
159 PRINT '=====================================================================';
160 END
161
162 IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[' + @SchemaName + '].[TR_' + @TableName + '_LAST_UPDATED]'))
163 BEGIN
164
165 PRINT '=====================================================================';
166 PRINT 'START - ADD TRIGGER [TR_' + @TableName + '_LAST_UPDATED]... ';
167
168 PRINT '1. get primary key from information schema';
169 SELECT
170 @TableKey = COALESCE(@TableKey, '') + CASE WHEN ORDINAL_POSITION = 1 THEN 'ON' ELSE 'AND' END + ' t.' + COLUMN_NAME + ' = i.' + COLUMN_NAME + ' '
171 FROM
172 INFORMATION_SCHEMA.KEY_COLUMN_USAGE
173 WHERE
174 1=1
175 AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
176 AND TABLE_NAME = @TableName AND TABLE_SCHEMA = @SchemaName
177 ORDER BY
178 ORDINAL_POSITION
179
180 PRINT '2. build trigger dynamically';
181 SET @SqlCommand = 'CREATE TRIGGER [' + @SchemaName + '].[TR_' + @TableName + '_LAST_UPDATED]' + CHAR(13);
182 SET @SqlCommand += 'ON [' + @SchemaName + '].[' + @TableName + ']' + CHAR(13);
183 SET @SqlCommand += 'AFTER UPDATE' + CHAR(13);
184 SET @SqlCommand += 'AS' + CHAR(13);
185 SET @SqlCommand += 'BEGIN' + CHAR(13);
186 SET @SqlCommand += CHAR(9) + 'IF NOT UPDATE(' + @ModifiedDate + ')' + CHAR(13);
187 SET @SqlCommand += CHAR(9) + 'BEGIN' + CHAR(13);
188 SET @SqlCommand += CHAR(9) + CHAR(9) + 'UPDATE t' + CHAR(13);
189 SET @SqlCommand += CHAR(9) + CHAR(9) + 'SET' + CHAR(13);
190 SET @SqlCommand += CHAR(9) + CHAR(9) + ' t.' + @ModifiedDate + ' = CURRENT_TIMESTAMP' + CHAR(13);
191 SET @SqlCommand += CHAR(9) + CHAR(9) + ', t.' + @ModifiedId + ' = LOWER(LEFT(RIGHT(SYSTEM_USER,(LEN(SYSTEM_USER)-CHARINDEX('''',SYSTEM_USER))), 50))' + CHAR(13);
192 SET @SqlCommand += CHAR(9) + CHAR(9) + 'FROM [' + @SchemaName + '].[' + @TableName + '] AS t' + CHAR(13);
193 SET @SqlCommand += CHAR(9) + CHAR(9) + 'INNER JOIN inserted AS i' + CHAR(13);
194 SET @SqlCommand += CHAR(9) + CHAR(9) + @TableKey + ';' + CHAR(13);
195 SET @SqlCommand += CHAR(9) + 'END' + CHAR(13);
196 SET @SqlCommand += 'END;' + CHAR(13);
197 EXEC (@SqlCommand)
198
199 PRINT '3. enable trigger';
200 SET @SqlCommand = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ENABLE TRIGGER [TR_' + @TableName + '_LAST_UPDATED]';
201 EXEC (@SqlCommand)
202
203 PRINT 'END - ADD TRIGGER [' + @ModifiedDate + ']... ';
204 PRINT '=====================================================================';
205 END
206
207
208 PRINT 'END - ALTER [' + @SchemaName + '].[' + @TableName + ']... ';
209 PRINT '=====================================================================';
210
211 --PRINT '******* ROLLBACK TRANSACTION ******* ';
212 --ROLLBACK TRANSACTION;
213
214 PRINT '******* COMMIT TRANSACTION ******* ';
215 COMMIT TRANSACTION;
216
217END
218
219GO
220
221--create the table
222CREATE TABLE [dbo].[ORGN] (
223 [ORGN_ID] INT IDENTITY (1, 1) NOT NULL,
224 [ORGN_ABBR] VARCHAR (5) NOT NULL,
225 [ORGN_NAME] VARCHAR (100) NULL
226);
227
228--Add the keys
229ALTER TABLE [dbo].[ORGN]
230 ADD CONSTRAINT [PK_ORGN] PRIMARY KEY NONCLUSTERED ([ORGN_ID] ASC);
231ALTER TABLE [dbo].[ORGN]
232 ADD CONSTRAINT [UK_ORGN] UNIQUE NONCLUSTERED ([ORGN_ABBR] ASC);
233
234--Add some test records
235INSERT INTO dbo.ORGN (ORGN_ABBR, ORGN_NAME) VALUES('AABA', 'Altaba Inc');
236INSERT INTO dbo.ORGN (ORGN_ABBR, ORGN_NAME) VALUES('AAPL', 'Apple Inc');
237INSERT INTO dbo.ORGN (ORGN_ABBR, ORGN_NAME) VALUES('GOOG', 'Alphabet Inc');
238INSERT INTO dbo.ORGN (ORGN_ABBR, ORGN_NAME) VALUES('MSFT', 'Microsoft Corporation');
239INSERT INTO dbo.ORGN (ORGN_ABBR, ORGN_NAME) VALUES('TSLA', 'Tesla Inc');
240
241CREATE TABLE [dbo].[ORGN] (
242 [ORGN_ID] INT IDENTITY (1, 1) NOT NULL,
243 [ORGN_ABBR] VARCHAR (5) NOT NULL,
244 [ORGN_NAME] VARCHAR (100) NULL,
245 [CreatedId] NVARCHAR (50) NOT NULL,
246 [CreatedDate] DATETIME NOT NULL,
247 [ModifiedId] NVARCHAR (50) NOT NULL,
248 [ModifiedDate] DATETIME NOT NULL
249);
250
251ALTER TABLE [dbo].[ORGN]
252 ADD CONSTRAINT [PK_ORGN] PRIMARY KEY NONCLUSTERED ([ORGN_ID] ASC);
253
254ALTER TABLE [dbo].[ORGN]
255 ADD CONSTRAINT [UK_ORGN] UNIQUE NONCLUSTERED ([ORGN_ABBR] ASC);
256
257ALTER TABLE [dbo].[ORGN]
258 ADD CONSTRAINT [DF_ORGN_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate];
259
260ALTER TABLE [dbo].[ORGN]
261 ADD CONSTRAINT [DF_ORGN_CreatedId] DEFAULT (lower(left(right(suser_sname(),len(suser_sname())-charindex('',suser_sname())),(50)))) FOR [CreatedId];
262
263ALTER TABLE [dbo].[ORGN]
264 ADD CONSTRAINT [DF_ORGN_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate];
265
266ALTER TABLE [dbo].[ORGN]
267 ADD CONSTRAINT [DF_ORGN_ModifiedId] DEFAULT (lower(left(right(suser_sname(),len(suser_sname())-charindex('',suser_sname())),(50)))) FOR [ModifiedId];
268
269
270CREATE TRIGGER [dbo].[TR_ORGN_LAST_UPDATED]
271ON [dbo].[ORGN]
272AFTER UPDATE
273AS
274BEGIN
275 IF NOT UPDATE(ModifiedDate)
276 BEGIN
277 UPDATE t
278 SET
279 t.ModifiedDate = CURRENT_TIMESTAMP
280 , t.ModifiedId = LOWER(LEFT(RIGHT(SYSTEM_USER,(LEN(SYSTEM_USER)-CHARINDEX('',SYSTEM_USER))), 50))
281 FROM [dbo].[ORGN] AS t
282 INNER JOIN inserted AS i
283 ON t.ORGN_ID = i.ORGN_ID ;
284 END
285END;