· 8 years ago · Jul 24, 2018, 11:10 PM
1/****** Object: StoredProcedure [dbo].[GenerateAudittrail] Script Date: 02/23/2010 09:06:24 ******/
2SET ANSI_NULLS ON
3GO
4
5SET QUOTED_IDENTIFIER ON
6GO
7
8
9
10CREATE PROCEDURE [dbo].[GenerateAudittrail]
11 @TableName varchar(128),
12 @Owner varchar(128) = 'dbo',
13 @AuditNameExtention varchar(128) = '_shadow',
14 @DropAuditTable bit = 0
15AS
16BEGIN
17
18 -- Check if table exists
19 IF not exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
20 BEGIN
21 PRINT 'ERROR: Table does not exist'
22 RETURN
23 END
24
25 -- Check @AuditNameExtention
26 IF @AuditNameExtention is null
27 BEGIN
28 PRINT 'ERROR: @AuditNameExtention cannot be null'
29 RETURN
30 END
31
32 -- Drop audit table if it exists and drop should be forced
33 IF (exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) and @DropAuditTable = 1)
34 BEGIN
35 PRINT 'Dropping audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']'
36 EXEC ('drop table ' + @TableName + @AuditNameExtention)
37 END
38
39 -- Declare cursor to loop over columns
40 DECLARE TableColumns CURSOR Read_Only
41 FOR SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
42 FROM sysobjects a
43 inner join syscolumns b on a.id = b.id
44 inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname'
45 WHERE a.id = object_id(N'[' + @Owner + '].[' + @TableName + ']')
46 and OBJECTPROPERTY(a.id, N'IsUserTable') = 1
47 ORDER BY b.colId
48
49 OPEN TableColumns
50
51 -- Declare temp variable to fetch records into
52 DECLARE @ColumnName varchar(128)
53 DECLARE @ColumnType varchar(128)
54 DECLARE @ColumnLength smallint
55 DECLARE @ColumnNullable int
56 DECLARE @ColumnCollation sysname
57 DECLARE @ColumnPrecision tinyint
58 DECLARE @ColumnScale tinyint
59
60 -- Declare variable to build statements
61 DECLARE @CreateStatement varchar(8000)
62 DECLARE @ListOfFields varchar(2000)
63 SET @ListOfFields = ''
64
65
66 -- Check if audit table exists
67 IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
68 BEGIN
69 -- AuditTable exists, update needed
70 PRINT 'Table already exists. Only triggers will be updated.'
71
72 FETCH Next FROM TableColumns
73 INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
74
75 WHILE @@FETCH_STATUS = 0
76 BEGIN
77 IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
78 BEGIN
79 SET @ListOfFields = @ListOfFields + @ColumnName + ','
80 END
81
82 FETCH Next FROM TableColumns
83 INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
84
85 END
86 END
87 ELSE
88 BEGIN
89 -- AuditTable does not exist, create new
90
91 -- Start of create table
92 SET @CreateStatement = 'CREATE TABLE [' + @Owner + '].[' + @TableName + @AuditNameExtention + '] ('
93 SET @CreateStatement = @CreateStatement + '[AuditId] [bigint] IDENTITY (1, 1) NOT NULL,'
94
95 FETCH Next FROM TableColumns
96 INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
97
98 WHILE @@FETCH_STATUS = 0
99 BEGIN
100 IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
101 BEGIN
102 SET @ListOfFields = @ListOfFields + @ColumnName + ','
103
104 SET @CreateStatement = @CreateStatement + '[' + @ColumnName + '] [' + @ColumnType + '] '
105
106 IF @ColumnType in ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar')
107 BEGIN
108 IF (@ColumnLength = -1)
109 Set @CreateStatement = @CreateStatement + '(max) '
110 ELSE
111 SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnLength as varchar(10)) + ') '
112 END
113
114 IF @ColumnType in ('decimal', 'numeric')
115 SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ') '
116
117 IF @ColumnType in ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
118 SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' '
119
120 IF @ColumnNullable = 0
121 SET @CreateStatement = @CreateStatement + 'NOT '
122
123 SET @CreateStatement = @CreateStatement + 'NULL, '
124 END
125
126 FETCH Next FROM TableColumns
127 INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
128 END
129
130 -- Add audit trail columns
131 SET @CreateStatement = @CreateStatement + '[AuditAction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,'
132 SET @CreateStatement = @CreateStatement + '[AuditDate] [datetime] NOT NULL ,'
133 SET @CreateStatement = @CreateStatement + '[AuditUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,'
134 SET @CreateStatement = @CreateStatement + '[AuditApp] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)'
135
136 -- Create audit table
137 PRINT 'Creating audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']'
138 EXEC (@CreateStatement)
139
140 -- Set primary key and default values
141 SET @CreateStatement = 'ALTER TABLE [' + @Owner + '].[' + @TableName + @AuditNameExtention + '] ADD '
142 SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditDate] DEFAULT (getdate()) FOR [AuditDate],'
143 SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditUser] DEFAULT (suser_sname()) FOR [AuditUser],CONSTRAINT [PK_' + @TableName + @AuditNameExtention + '] PRIMARY KEY CLUSTERED '
144 SET @CreateStatement = @CreateStatement + '([AuditId]) ON [PRIMARY], '
145 SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditApp] DEFAULT (''App=('' + rtrim(isnull(app_name(),'''')) + '') '') for [AuditApp]'
146
147 EXEC (@CreateStatement)
148
149 END
150
151 CLOSE TableColumns
152 DEALLOCATE TableColumns
153
154 /* Drop Triggers, if they exist */
155 PRINT 'Dropping triggers'
156 IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
157 EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Insert]')
158
159 IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
160 EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Update]')
161
162 IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
163 EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Delete]')
164
165 /* Create triggers */
166 PRINT 'Creating triggers'
167 EXEC ('CREATE TRIGGER tr_' + @TableName + '_Insert ON ' + @Owner + '.' + @TableName + ' FOR INSERT AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''I'' FROM Inserted')
168
169 EXEC ('CREATE TRIGGER tr_' + @TableName + '_Update ON ' + @Owner + '.' + @TableName + ' FOR UPDATE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''U'' FROM Inserted')
170
171 EXEC ('CREATE TRIGGER tr_' + @TableName + '_Delete ON ' + @Owner + '.' + @TableName + ' FOR DELETE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''D'' FROM Deleted')
172
173END
174
175
176
177
178GO