ALTER PROCEDURE [dbo].[SP_VERIFICALOG2] AS
DECLARE @sql varchar(max)
DECLARE @texto varchar(max)
DECLARE @Existe as int,
@TABLE_NAME sysname
SET NOCOUNT ON
select @Existe = count(*) from dbo.sysobjects where id = object_id(N'[dbo].[LOGSISTEMA]')
print '@Existe'+ convert(varchar,@Existe)
if @Existe = 0
begin
print '2'
select @texto= @texto + 'CREATE TABLE [dbo].[LOGSISTEMA ]('
select @texto= @texto + '[IDLog] [int] IDENTITY(1,1) NOT NULL,'
select @texto= @texto + '[DataAlteracao] [smalldatetime] NULL,'
select @texto= @texto + '[Hora] [Varchar] (10) NULL,'
select @texto= @texto + '[Usuario] [int] NULL,'
select @texto= @texto + '[Computador] [varchar](255) NULL,'
select @texto= @texto + '[TipoLog] [Smalldatetime] NULL,'
select @texto= @texto + '[Tabela] [varchar](50) NULL,'
select @texto= @texto + '[CampoChave] [varchar](50) NULL,'
select @texto= @texto + '[ValorCampoChave] [varchar](255) NULL,'
select @texto= @texto + '[CampoAlter] [varchar] (50) NULL,'
select @texto= @texto + '[ValorAntigo] [varchar](255) NULL,'
select @texto= @texto + '[ValorNovo] [varchar](255) NULL'
select @texto = @texto+ ' ) ON [PRIMARY]'
print @texto
execute (@texto)
end
DECLARE cTabLog CURSOR FAST_FORWARD FOR
SELECT tabela FROM Tabelaslog
print 'Abrindo o cursor de clientes'
OPEN cTablog
FETCH NEXT FROM cTabLog INTO @TABLE_NAME
WHILE (@@FETCH_STATUS=0)
BEGIN
EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_LOG'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_LOG')
SELECT @sql ='create trigger ' + @TABLE_NAME+ '_LOG on ' + @TABLE_NAME+ ' for insert, update, delete
as
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(8000),
@UpdateDate varchar(21) ,
@UpdateHora varchar(21),
@Usuario varchar(128),
@Computer varchar(128),
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)
select @TableName = ''' + @TABLE_NAME+ '''
-- date and user
select @UpdateDate = convert(varchar(8), getdate(), 112),@UpdateHora = convert(varchar(8), getdate(), 114)
select @Computer = host_name(),@Usuario = (SELECT TOP 1 COD_USUARIO FROM USUARIOS WHERE COMPUTADOR = HOST_NAME() ORDER BY ULTIMOACESSO)
-- Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = ''U''
else
select @Type = ''I''
else
select @Type = ''D''
-- get list of columns
select * into #ins from inserted
select * into #del from deleted
-- Get primary key columns for full outer join
select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key fields select for insert
select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + ''''''''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
if @PKCols is null
begin
raiserror(''no PK on table %s'', 16, -1, @TableName)
return
end
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
select @sql = ''insert LOGSISTEMA (DataAlteracao, Hora,Usuario,Computador,TipoLog,Tabela,CampoChave, ValorCampoChave, CampoAlter, ValorAntigo, ValorNovo)''
select @sql = @sql + ''select'''''' + @UpdateDate + ''''''''
select @sql = @sql + '','''''' + @UpdateHora + ''''''''
select @sql = @sql + '','''''' + @Usuario + ''''''''
select @sql = @sql + '','''''' + @Computer + ''''''''
select @sql = @sql + '','''''' + @Type + ''''''''
select @sql = @sql + '','''''' + @TableName + ''''''''
select @sql = @sql + '','' + @PKValueSelect
select @sql = @sql + '','''''' + @fieldname + ''''''''
select @sql = @sql + '',convert(varchar(1000),d.'' + @fieldname + '')''
select @sql = @sql + '',convert(varchar(1000),i.'' + @fieldname + '')''
select @sql = @sql + '','''''' + @UpdateDate + ''''''''
select @sql = @sql + '','''''' + @UserName + ''''''''
select @sql = @sql + '' from #ins i full outer join #del d''
select @sql = @sql + @PKCols
select @sql = @sql + '' where i.'' + @fieldname + ''d.'' + @fieldname
select @sql = @sql + ''(i.'' + @fieldname + ''is null and d.'' + @fieldname + ''is not null)''
select @sql = @sql + '' OR (i.'' + @fieldname + ''is not null and d.'' + @fieldname + '' is null)''
select @sql = @sql + ''end end'
SELECT @sql
EXEC(@sql)
print @sql
FETCH NEXT FROM cTabLog INTO @TABLE_NAME
CLOSE cTabLog
DEALLOCATE cTabLog
END