240 lines
7.9 KiB
Transact-SQL
240 lines
7.9 KiB
Transact-SQL
/****** Object: StoredProcedure [dbo].[jcbAuditConfig] Script Date: 03/29/2011 17:32:35 ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jcbAuditConfig]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[jcbAuditConfig]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[jcbAuditConfig] Script Date: 03/29/2011 17:33:30 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
create procedure [dbo].[jcbAuditConfig]
|
|
@tablename varchar(max)
|
|
as begin
|
|
--print 'table name: ' + @tablename
|
|
declare @cmd varchar(max)
|
|
set @cmd = ''
|
|
|
|
--new table name variable
|
|
declare @newtablename varchar(max)
|
|
set @newtablename = 'tbl' + @tablename
|
|
--print 'new table name: ' + @newtablename
|
|
|
|
--rename table
|
|
set @cmd = 'sp_rename ' + '''' + @tablename + '''' + ',' + '''' + @newtablename + ''''
|
|
--print 'rename table: ' + @cmd
|
|
exec (@cmd)
|
|
set @cmd = ''
|
|
|
|
--add column DeleteStatus
|
|
set @cmd = 'alter table ' + @newtablename + ' add DeleteStatus int not null constraint DF_' + @newtablename + '_DeleteStatus default ((0))'
|
|
--print 'add column deletestatus: ' + @cmd
|
|
exec (@cmd)
|
|
set @cmd = ''
|
|
|
|
--create index on deletestatus
|
|
set @cmd = 'CREATE NONCLUSTERED INDEX IX_' + @newtablename + 'DeleteStatus ON ' + @newtablename + '
|
|
(
|
|
[DeleteStatus] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
'
|
|
exec (@cmd)
|
|
set @cmd = ''
|
|
|
|
--primary key variable
|
|
declare @primarykey varchar(max)
|
|
select @primarykey = coalesce(@primarykey + ' and ','') +
|
|
'dd.' + c.name + ' = ' + 'ii.' + c.name
|
|
from sysindexes i
|
|
inner join sysobjects o ON i.id = o.id
|
|
inner join sysobjects pk ON i.name = pk.name and pk.parent_obj = i.id and pk.xtype = 'PK'
|
|
inner join sysindexkeys ik on i.id = ik.id and i.indid = ik.indid
|
|
inner join syscolumns c ON ik.id = c.id and ik.colid = c.colid
|
|
where o.name = @newtablename
|
|
order by ik.keyno
|
|
--print 'primary key: ' + @primarykey
|
|
|
|
--shadow table name variable
|
|
declare @shadowtablename varchar(max)
|
|
if substring(@tablename,len(@tablename)-2,3) = 'ies'
|
|
set @shadowtablename = replace(@tablename,'ies','y') + 'Audits'
|
|
else
|
|
set @shadowtablename = substring(@tablename,1,len(@tablename)-1) + 'Audits'
|
|
--print 'shadow table name: ' + @shadowtablename
|
|
|
|
--create base shadow table
|
|
set @cmd = 'create table ' + @shadowtablename + '(AuditID bigint identity (1,1) not null CONSTRAINT [PK_' + @shadowtablename + '] PRIMARY KEY CLUSTERED ([AuditID] ASC))'
|
|
--print 'create shadow table: ' + @cmd
|
|
exec (@cmd)
|
|
set @cmd = ''
|
|
|
|
--temp variables for cursor
|
|
declare @columnname varchar(max)
|
|
declare @columntype varchar(max)
|
|
declare @columnlength smallint
|
|
declare @columnnullable int
|
|
declare @columncollation sysname
|
|
declare @columnprecision tinyint
|
|
declare @columnscale tinyint
|
|
|
|
--separator variable
|
|
declare @sep varchar(max)
|
|
set @sep = ''
|
|
|
|
--table columns variable
|
|
declare @tablecolumns varchar(max)
|
|
set @tablecolumns = ''
|
|
|
|
--ii table columns variable
|
|
declare @iitablecolumns varchar(max)
|
|
set @iitablecolumns = ''
|
|
|
|
--dd table columns variable
|
|
declare @ddtablecolumns varchar(max)
|
|
set @ddtablecolumns = ''
|
|
|
|
--view columns variable
|
|
declare @viewcolumns varchar(max)
|
|
set @viewcolumns = ''
|
|
|
|
--trigger columns variable
|
|
declare @trigcolumns varchar(max)
|
|
set @trigcolumns = ''
|
|
|
|
--cursor for getting columns
|
|
declare tc cursor read_only for
|
|
select c.name,t.name typename,c.length,c.isnullable,c.collation,c.xprec,c.xscale
|
|
from sysobjects o
|
|
inner join syscolumns c on o.id = c.id
|
|
inner join systypes t on c.xtype = t.xtype
|
|
where 1=1
|
|
and o.name = @newtablename
|
|
and t.name != 'sysname'
|
|
order by c.colorder
|
|
|
|
--alter shadow table command
|
|
set @cmd = 'alter table ' + @shadowtablename + ' add '
|
|
--print 'alter shadow table start: ' + @cmd
|
|
|
|
open tc
|
|
fetch next from tc
|
|
into @columnname,@columntype,@columnlength,@columnnullable,@columncollation,@columnprecision,@columnscale
|
|
while @@fetch_status = 0
|
|
begin
|
|
if @columntype not in ('text','ntext','image')
|
|
begin
|
|
if @columntype not in ('timestamp')
|
|
begin
|
|
set @cmd = @cmd + @sep + @columnname + ' ' + @columntype
|
|
if @columntype in ('binary','char','nchar','nvarchar','varbinary','varchar')
|
|
begin
|
|
if @columnlength = -1
|
|
set @cmd = @cmd + '(max)'
|
|
else
|
|
set @cmd = @cmd + '(' + cast(@columnlength as varchar(10)) + ')'
|
|
end
|
|
if @columntype in ('decimal','numeric')
|
|
set @cmd = @cmd + '(' + cast(@columnprecision as varchar(10)) + ',' + cast(@columnscale as varchar(10)) + ')'
|
|
if @columntype in ('char','nchar','nvarchar','varchar')
|
|
set @cmd = @cmd + ' collate ' + @columncollation + ' '
|
|
if @columnnullable = 0
|
|
set @cmd = @cmd + ' not'
|
|
set @cmd = @cmd + ' null'
|
|
set @tablecolumns = @tablecolumns + @sep + @columnname
|
|
set @iitablecolumns = @iitablecolumns + @sep + 'ii.' + @columnname
|
|
set @ddtablecolumns = @ddtablecolumns + @sep + 'dd.' + @columnname
|
|
end
|
|
if @columnname <> 'DeleteStatus'
|
|
begin
|
|
set @viewcolumns = @viewcolumns + @sep + @columnname
|
|
if @columntype not in ('timestamp')
|
|
begin
|
|
set @trigcolumns = @trigcolumns + @sep + @columnname
|
|
end
|
|
end
|
|
set @sep = ','
|
|
end
|
|
fetch next from tc
|
|
into @columnname,@columntype,@columnlength,@columnnullable,@columncollation,@columnprecision,@columnscale
|
|
end
|
|
close tc
|
|
deallocate tc
|
|
|
|
--modify shadow table
|
|
--print 'modify shadow table: ' + @cmd
|
|
exec (@cmd)
|
|
set @cmd = ''
|
|
|
|
--add extended property for shadow table dts
|
|
set @cmd = 'sys.sp_addextendedproperty @name=N''MS_Description'', @value=N''{datetime}'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N''' + @shadowtablename + ''', @level2type=N''COLUMN'',@level2name=N''DTS'''
|
|
exec (@cmd)
|
|
set @cmd = ''
|
|
|
|
--add original indexes to shadowtable
|
|
--variables for cursor
|
|
declare @indexcolumnname varchar(max)
|
|
declare ic cursor for
|
|
select
|
|
c.name columnname
|
|
from sysindexes i
|
|
inner join sysobjects o ON i.id = o.id
|
|
inner join sysindexkeys ik on i.id = ik.id and i.indid = ik.indid
|
|
inner join syscolumns c ON ik.id = c.id and ik.colid = c.colid
|
|
where o.name = @newtablename
|
|
|
|
open ic
|
|
fetch next from ic into @indexcolumnname
|
|
while @@fetch_status = 0
|
|
begin
|
|
set @cmd = 'CREATE NONCLUSTERED INDEX IX_' + @shadowtablename + @indexcolumnname + ' on ' + @shadowtablename + '
|
|
(' + @indexcolumnname + ')
|
|
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
|
|
exec (@cmd)
|
|
set @cmd = ''
|
|
fetch next from ic into @indexcolumnname
|
|
end
|
|
close ic
|
|
deallocate ic
|
|
|
|
--create view
|
|
set @cmd = 'create view ' + @tablename + ' as select ' + @viewcolumns + ' from ' + @newtablename + ' where DeleteStatus = 0'
|
|
--print 'create view: ' + @cmd
|
|
exec (@cmd)
|
|
set @cmd = ''
|
|
|
|
--create update trigger on table
|
|
set @cmd = 'create trigger tr_' + @newtablename + '_Update on ' + @newtablename +
|
|
' for update as
|
|
begin
|
|
if exists (select * from inserted)
|
|
begin
|
|
insert into ' + @shadowtablename + '(' + @tablecolumns + ')
|
|
select ' + @ddtablecolumns + ' from deleted dd
|
|
inner join inserted ii on ' + @primarykey + '
|
|
end
|
|
end'
|
|
--print 'table trigger update: ' + @cmd
|
|
exec (@cmd)
|
|
set @cmd = ''
|
|
|
|
--create delete trigger on view
|
|
set @cmd = 'create trigger tr_' + @tablename + '_Delete on ' + @tablename +
|
|
' instead of delete as
|
|
begin
|
|
update ii set DeleteStatus = (select max(DeleteID) from DeleteLog where SPID = @@spid), DTS = getdate(),
|
|
UserID = (select top 1 UserID from DeleteLog where SPID = @@spid order by deleteid desc)
|
|
from ' + @newtablename + ' ii inner join deleted dd on ' + @primarykey + '
|
|
insert into ' + @shadowtablename + '(' + @tablecolumns + ')
|
|
select ' + @iitablecolumns + '
|
|
from ' + @newtablename + ' ii inner join deleted dd on ' + @primarykey + '
|
|
where ii.DeleteStatus > 0
|
|
end'
|
|
--print 'view trigger delete: ' + @cmd
|
|
exec (@cmd)
|
|
set @cmd = ''
|
|
end
|
|
|
|
|
|
|