← Back to branch summary

~halega/+junk/sqlinstaller

« back to all changes in this revision

Viewing changes to SQLInstaller.Sample/Scripts/SqlServer/Install/AuditCreate.PostInstall.sql

  • Committer: sk
  • Date: 2011-09-10 05:32:36 UTC
  • Revision ID: halega@halega.com-20110910053236-1877r3p0k4a64bgx
Tags: 1.2.2
1.2.2

Show diffs side-by-side

added added

removed removed

Lines of Context:
SQLInstaller.Sample/Scripts/SqlServer/Install/AuditCreate.PostInstall.sql
 
1
/****** Special procedure to create auditing ******/
 
2
DECLARE @table_name nvarchar(250)
 
3
DECLARE @column_name nvarchar(250)
 
4
DECLARE @ddl nvarchar(20)
 
5
DECLARE @identity int
 
6
DECLARE @sql nvarchar(4000)
 
7
DECLARE @columns nvarchar(4000)
 
8
DECLARE @column_list nvarchar(4000)
 
9
DECLARE @trigger_columns nvarchar(4000)
 
10
 
 
11
DECLARE curTables CURSOR FOR
 
12
        SELECT table_name
 
13
        FROM information_schema.tables
 
14
        WHERE table_type = 'BASE TABLE' AND 
 
15
                OBJECTPROPERTY (OBJECT_ID(table_name), 'IsMsShipped') = 0 AND
 
16
                table_name NOT LIKE '%Audit%'
 
17
 
 
18
OPEN curTables
 
19
FETCH NEXT FROM curTables INTO @table_name
 
20
WHILE @@FETCH_STATUS = 0
 
21
BEGIN                   
 
22
 
 
23
        DECLARE curColumns CURSOR FOR
 
24
                SELECT column_name, COLUMNPROPERTY(OBJECT_ID(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name)), column_name, 'IsIdentity') AS isidentity
 
25
                FROM information_schema.columns
 
26
                WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name)), 'IsMSShipped') = 0
 
27
                AND data_type NOT IN ('text','ntext','image')
 
28
                AND TABLE_NAME = @table_name
 
29
                ORDER BY ordinal_position
 
30
        
 
31
        SET @columns = CHAR(39) + 'X' + CHAR(39) + ' AS AuditID,'+ CHAR(39) + 'I' + CHAR(39) + ' AS AuditCode'
 
32
        SET @column_list = 'AuditCode'
 
33
        SET @trigger_columns = ''
 
34
        OPEN curColumns
 
35
        FETCH NEXT FROM curColumns INTO @column_name, @identity
 
36
        WHILE @@FETCH_STATUS = 0
 
37
        BEGIN           
 
38
                SET @columns = @columns + ','
 
39
                SET @trigger_columns = @trigger_columns + 't.' + @column_name + ','
 
40
                SET @columns = @columns + CASE @identity WHEN 1 THEN 'CAST('+@column_name+' AS int) AS ' + @column_name ELSE @column_name END
 
41
                SET @column_list = @column_list + ','+@column_name
 
42
                FETCH NEXT FROM curColumns INTO @column_name, @identity
 
43
        END
 
44
        CLOSE curColumns
 
45
        DEALLOCATE curColumns
 
46
        SET @columns = @columns + ',getdate() AS AuditOn,SYSTEM_USER AS AuditUserName'
 
47
        SET @column_list = @column_list + ',AuditOn,AuditUserName'
 
48
        SET @sql = '
 
49
IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = ' + CHAR(39) + @table_name + 'Audit' + CHAR(39) + ')
 
50
BEGIN
 
51
        SELECT ' + @columns + ' INTO dbo.' + @table_name + 'Audit FROM ' + @table_name + ' WHERE 1=2
 
52
        ALTER TABLE dbo.' + @table_name + 'Audit ALTER COLUMN 
 
53
                AuditID uniqueidentifier NOT NULL
 
54
        ALTER TABLE dbo.' + @table_name + 'Audit ADD CONSTRAINT '+@table_name+'_Sid
 
55
                DEFAULT NEWSEQUENTIALID() FOR AuditID
 
56
        ALTER TABLE dbo.' + @table_name + 'Audit ALTER COLUMN 
 
57
                AuditCode char(1) NOT NULL
 
58
        ALTER TABLE dbo.' + @table_name + 'Audit ALTER COLUMN 
 
59
                AuditOn datetime NOT NULL
 
60
        ALTER TABLE dbo.' + @table_name + 'Audit ALTER COLUMN 
 
61
                AuditUserName nvarchar(255) NOT NULL
 
62
 
 
63
        ALTER TABLE dbo.' + @table_name + 'Audit ADD 
 
64
                CONSTRAINT Pk_' + @table_name + 'Audit PRIMARY KEY CLUSTERED 
 
65
                (
 
66
                        AuditID ASC
 
67
                )
 
68
 
 
69
END
 
70
'
 
71
        EXEC(@sql)
 
72
 
 
73
        IF EXISTS ( SELECT * FROM sysobjects so WHERE OBJECTPROPERTY(so.id, 'IsTrigger')=1 AND OBJECTPROPERTY(so.id, 'IsMSShipped')=0 AND so.name = 'Audit' + @table_name)
 
74
                SET @ddl = 'ALTER'
 
75
        ELSE
 
76
                SET @ddl = 'CREATE'
 
77
 
 
78
        SET @sql = @ddl + '
 
79
TRIGGER dbo.Audit' + @table_name + ' ON dbo.' + @table_name + '
 
80
FOR INSERT, UPDATE, DELETE
 
81
NOT FOR REPLICATION
 
82
AS
 
83
BEGIN
 
84
        SET NOCOUNT ON
 
85
        IF EXISTS(SELECT * FROM INSERTED)
 
86
                BEGIN
 
87
                        IF EXISTS(SELECT * FROM DELETED)
 
88
                                INSERT INTO dbo.' + @table_name + 'Audit 
 
89
                                (' + @column_list + ')
 
90
                                SELECT
 
91
                                        ' + CHAR(39) + 'U' + CHAR(39) + ' AS AuditCode,
 
92
                                        ' + @trigger_columns + '
 
93
                                        GETDATE() AS AuditOn,
 
94
                                        (SELECT CASE context_info WHEN 0x00 THEN SYSTEM_USER ELSE CAST(context_info AS nvarchar) END FROM master..sysprocesses WHERE spid=@@spid) AS AuditUserName
 
95
                                FROM INSERTED t
 
96
                        ELSE
 
97
                                INSERT INTO dbo.' + @table_name + 'Audit
 
98
                                (' + @column_list + ')
 
99
                                SELECT
 
100
                                        ' + CHAR(39) + 'I' + CHAR(39) + ' AS AuditCode, 
 
101
                                        ' + @trigger_columns + '
 
102
                                        GETDATE() AS AuditOn,
 
103
                                        (SELECT CASE context_info WHEN 0x00 THEN SYSTEM_USER ELSE CAST(context_info AS nvarchar) END FROM master..sysprocesses WHERE spid=@@spid) AS AuditUserName
 
104
                                FROM INSERTED t
 
105
                END
 
106
        ELSE
 
107
                INSERT INTO dbo.' + @table_name + 'Audit
 
108
                (' + @column_list + ')
 
109
                SELECT
 
110
                        ' + CHAR(39) + 'D' + CHAR(39) + ' AS AuditCode, 
 
111
                        ' + @trigger_columns + '
 
112
                        GETDATE() AS AuditOn,
 
113
                        (SELECT CASE context_info WHEN 0x00 THEN SYSTEM_USER ELSE CAST(context_info AS nvarchar) END FROM master..sysprocesses WHERE spid=@@spid) AS AuditUserName
 
114
                FROM DELETED t
 
115
        SET NOCOUNT OFF
 
116
END
 
117
'
 
118
        EXEC(@sql)
 
119
        FETCH NEXT FROM curTables INTO @table_name 
 
120
END
 
121
CLOSE curTables
 
122
 
 
123
DEALLOCATE curTables
 
124