1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
USE [Unmtd]
GO
/****** Object: StoredProcedure [dbo].[spx_AutoAudit] Script Date: 07/23/2018 13:32:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spx_AutoAudit] (
@TableName VARCHAR(50),
@TableNameText VARCHAR(200),
@SchemaName SYSNAME='dbo',
@NameUser nvarchar(50),
@NameComp SYSNAME
)
AS
SET NoCount ON
-- script to create autoAudit triggers
DECLARE
@SQL NVARCHAR(max),
@ColumnName sysname,
@PKColumnName sysname
-- drop existing insert trigger
SET @SQL = 'If EXISTS (Select * from sys.objects where name = '
+ '''' + @TableName + '_Audit_Insert' + ''' )'
+ ' DROP TRIGGER ' + @SchemaName + '.' + @TableName + '_Audit_Insert'
EXEC (@SQL)
-- drop existing update trigger
SET @SQL = 'If EXISTS (Select * from sys.objects where name = '
+ '''' + @TableName + '_Audit_Update' + ''' )'
+ ' DROP TRIGGER ' + @SchemaName + '.' + @TableName + '_Audit_Update'
EXEC (@SQL)
-- drop existing modified trigger
SET @SQL = 'If EXISTS (Select * from sys.objects where name = '
+ '''' + @TableName + '_Modified' + ''' )'
+ ' DROP TRIGGER ' + @SchemaName + '.' + @TableName + '_Modified'
EXEC (@SQL)
-- drop existing delete trigger
SET @SQL = 'If EXISTS (Select * from sys.objects where name = '
+ '''' + @TableName + '_Audit_Delete' + ''' )'
+ ' DROP TRIGGER ' + @SchemaName + '.' + @TableName + '_Audit_Delete'
EXEC (@SQL)
-- drop existing delete view
SET @SQL = 'If EXISTS (Select * from sys.objects where name = '
+ '''v' + @TableName + '_Deleted' + ''' )'
+ ' DROP VIEW ' + @SchemaName + '.v' + @TableName + '_Deleted'
EXEC (@SQL)
-- get PK Column (1)
select @PKColumnName = c.name
from sys.tables t
join sys.schemas s
on s.schema_id = t.schema_id
join sys.indexes i
on t.object_id = i.object_id
join sys.index_columns ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
join sys.columns c
on ic.object_id = c.object_id
and ic.column_id = c.column_id
where is_primary_key = 1 AND t.name = @TableName AND s.name = @SchemaName AND ic.index_column_id = 1
-- build modified trigger
SET @SQL = 'CREATE TRIGGER ' + @SchemaName + '.' + @TableName + '_Modified' + ' ON '+ @SchemaName + '.' + @TableName + Char(13) + Char(10)
+ ' AFTER Update' + Char(13) + Char(10) + ' NOT FOR REPLICATION AS' + Char(13) + Char(10)
+ ' SET NoCount On ' + Char(13) + Char(10)
+ ' -- generated by AutoAudit on ' + Convert(VARCHAR(30), GetDate(),100) + Char(13) + Char(10)
+ ' If Trigger_NestLevel() > 1 Return;' + Char(13) + Char(10)
-- + ' If (Update(Created) or Update(Modified)) AND Trigger_NestLevel() = 1' + Char(13) + Char(10)
-- + ' Begin; Raiserror(''Update failed.'', 16, 1); Rollback; Return; End;' + Char(13) + Char(10)
-- + ' -- Update the Modified date' + Char(13) + Char(10)
-- + ' UPDATE [' + @SchemaName + '].[' + @TableName + ']'+ Char(13) + Char(10)
-- + ' SET Modified = getdate() ' + Char(13) + Char(10)
-- + ' [RowVersion] = [' + @TableName + '].[RowVersion] + 1 ' + Char(13) + Char(10)
-- + ' FROM [' + @SchemaName + '].[' + @TableName + ']' + Char(13) + Char(10)
-- + ' JOIN Inserted' + Char(13) + Char(10)
-- + ' ON [' + @TableName + '].[' + @PKColumnName + '] = Inserted.[' + @PKColumnName + ']'
EXEC (@SQL)
-------------------------------------------
-- build insert trigger
SET @SQL = 'CREATE TRIGGER ' + @SchemaName + '.' + @TableName + '_Audit_Insert' + ' ON '+ @SchemaName + '.' + @TableName + Char(13) + Char(10)
+ ' AFTER Insert' + Char(13) + Char(10) + ' NOT FOR REPLICATION AS' + Char(13) + Char(10)
+ ' SET NoCount On ' + Char(13) + Char(10)
+ ' -- generated by AutoAudit on ' + Convert(VARCHAR(30), GetDate(),100) + Char(13) + Char(10)
+ 'DECLARE @AuditTime DATETIME' + Char(13) + Char(10)
+ 'SET @AuditTime = GetDate()' + Char(13) + Char(10) + Char(13) + Char(10)
-- for each column
select @SQL = @SQL +
' INSERT dbo.Audit (SysUser, NameUser, NameComp,TableNameText,TableName, Operation, PrimaryKey, ColumnName, NewValue)' + Char(13) + Char(10)
+ ' SELECT suser_sname(),'''+@NameUser+''',host_name(),'''+@TableNameText+''','
+ '''' + @SchemaName + '.' + @TableName + ''', ''ВСТ'','
+ ' Inserted.[' + @PKColumnName + '],' + Char(13) + Char(10)
+ ' ''[' + c.name + ']'','
+ ' Cast(Inserted.[' + c.name + '] as VARCHAR(70))' + Char(13) + Char(10)
+ ' FROM Inserted' + Char(13) + Char(10)
+ ' WHERE Inserted.['+ c.name + '] is not null' + Char(13) + Char(10)+ Char(13) + Char(10)
from sys.tables as t
join sys.columns as c
on t.object_id = c.object_id
join sys.schemas as s
on s.schema_id = t.schema_id
join sys.types as ty
on ty.user_type_id = c.user_type_id
join sys.types st
on ty.system_type_id = st.user_type_id
where t.name = @TableName AND s.name = @SchemaName
-- AND c.name NOT IN ('created', 'modified','RowVersion')
AND c.is_computed = 0
AND st.name IN ('tinyint', 'smallint', 'int', 'money', 'smallmoney', 'decimal', 'bigint', 'datetime', 'smalldateteime', 'numeric', 'varchar', 'nvarchar', 'char', 'nchar', 'bit')
order by c.column_id
EXEC (@SQL)
--------------------------
-- build update trigger
SET @SQL = 'CREATE TRIGGER ' + @SchemaName + '.' + @TableName + '_Audit_Update' + ' ON '+ @SchemaName + '.' + @TableName + Char(13) + Char(10)
+ ' AFTER Update' + Char(13) + Char(10) + ' NOT FOR REPLICATION AS' + Char(13) + Char(10)
+ ' SET NoCount On ' + Char(13) + Char(10)
+ ' -- generated by AutoAudit on ' + Convert(VARCHAR(30), GetDate(),100) + Char(13) + Char(10)
+ 'DECLARE @AuditTime DATETIME' + Char(13) + Char(10)
+ 'SET @AuditTime = GetDate()' + Char(13) + Char(10) + Char(13) + Char(10)
-- for each column
select @SQL = @SQL +
+ ' IF UPDATE([' + c.name + '])' + Char(13) + Char(10)
+ ' INSERT dbo.Audit (SysUser, NameUser, NameComp,TableNameText,TableName, Operation, PrimaryKey, ColumnName, OldValue, NewValue)' + Char(13) + Char(10)
+ ' SELECT suser_sname(), '''+@NameUser+''',host_name(),'''+@TableNameText+''','
+ '''' + @SchemaName + '.' + @TableName + ''', ''ОБН'','
+ ' Inserted.[' + @PKColumnName + '],' + Char(13) + Char(10)
+ ' ''[' + c.name+ ']'','
+ ' Cast(Deleted.[' + c.name + '] as VARCHAR(70)), '
+ ' Cast(Inserted.[' + c.name + '] as VARCHAR(70))' + Char(13) + Char(10)
+ ' FROM Inserted' + Char(13) + Char(10)
+ ' JOIN Deleted' + Char(13) + Char(10)
+ ' ON Inserted.[' + @PKColumnName + '] = Deleted.[' + @PKColumnName + ']' + Char(13) + Char(10)
+ ' AND isnull(Inserted.[' + c.name + '],'''') <> isnull(Deleted.[' + c.name + '],'''')' + Char(13) + Char(10)+ Char(13) + Char(10)
from sys.tables as t
join sys.columns as c
on t.object_id = c.object_id
join sys.schemas as s
on s.schema_id = t.schema_id
join sys.types as ty
on ty.user_type_id = c.user_type_id
join sys.types st
on ty.system_type_id = st.user_type_id
where t.name = @TableName AND s.name = @SchemaName
-- AND c.name NOT IN ('created', 'modified','RowVersion')
AND c.is_computed = 0
AND st.name IN ('tinyint', 'smallint', 'int', 'money', 'smallmoney', 'decimal', 'bigint', 'datetime', 'smalldateteime', 'numeric', 'varchar', 'nvarchar', 'char', 'nchar', 'bit')
order by c.column_id
EXEC (@SQL)
--------------------------
-- build delete trigger
SET @SQL = 'CREATE TRIGGER ' + @SchemaName + '.' + @TableName + '_Audit_Delete' + ' ON '+ @SchemaName + '.' + @TableName + Char(13) + Char(10)
+ ' AFTER Delete' + Char(13) + Char(10) + ' NOT FOR REPLICATION AS' + Char(13) + Char(10)
+ ' SET NoCount On ' + Char(13) + Char(10)
+ ' -- generated by AutoAudit on ' + Convert(VARCHAR(30), GetDate(),100) + Char(13) + Char(10)
+ 'DECLARE @AuditTime DATETIME' + Char(13) + Char(10)
+ 'SET @AuditTime = GetDate()' + Char(13) + Char(10) + Char(13) + Char(10)
+ ' INSERT dbo.Audit ( SysUser, NameUser, NameComp,TableNameText, TableName, Operation, PrimaryKey)' + Char(13) + Char(10)
+ ' SELECT suser_sname(),'''+@NameUser+''',host_name(),'''+@TableNameText+''','
+ '''' + @SchemaName + '.' + @TableName + ''', ''УДЛ'','
+ ' Deleted.[' + @PKColumnName + ']' + Char(13) + Char(10)
+ ' FROM Deleted' + Char(13) + Char(10)
EXEC (@SQL)
SET @SQL = 'CREATE VIEW ' + @SchemaName + '.v' + @TableName + '_Deleted' + Char(13) + Char(10)
+ 'AS ' + Char(13) + Char(10)
-- + ' -- generated by AutoAudit Version ' + @Version + ' on ' + Convert(VARCHAR(30), GetDate(),100) + Char(13) + Char(10)
-- + ' -- created by Paul Nielsen ' + Char(13) + Char(10)
-- + ' -- www.SQLServerBible.com ' + Char(13) + Char(10)
-- + ' -- autoaudit.codeplex.com ' + Char(13) + Char(10) + Char(13) + Char(10)
+ 'SELECT ' + Char(13) + Char(10)
-- for each column
SELECT @SQL = @SQL +
' Max(Case ColumnName WHEN ''[' + c.name + ']'' THEN OldValue ELSE '''' END) AS [' + c.name +'],' + Char(13) + Char(10)
from sys.tables as t
join sys.columns as c
on t.object_id = c.object_id
join sys.schemas as s
on s.schema_id = t.schema_id
join sys.types as ty
on ty.user_type_id = c.user_type_id
--join sys.types st
-- on ty.system_type_id = st.user_type_id
where t.name = @TableName AND s.name = @SchemaName
AND c.is_computed = 0
-- version 1.09 modified list of data types
-- v 1.09 changed to ty.name to accomodate SQL 2008 CLR data types
AND ty.name NOT IN ('text', 'ntext', 'image', 'geography','xml', 'binary', 'varbinary', 'timestamp')
order by c.column_id
SET @SQL = @SQL
+ ' MAX(LogTime) AS ''Deleted''' + Char(13) + Char(10)
+ ' FROM dbo.Audit' + Char(13) + Char(10)
+ ' Where TableName = ''' +@SchemaName + '.' + @TableName + ''' AND Operation = ''УДЛ''' + Char(13) + Char(10)
+ ' GROUP BY PrimaryKey'
EXEC (@SQL)
RETURN -- END OF SPROC
