EXEMPLO_1212310 Database

Parameters

Name	Type	Mode
@AuditOperation	nvarchar	IN

Definition

CREATE PROCEDURE p_EnableAuditLog
(
/*Version=07.08.19*/
@AuditOperation NVARCHAR = NULL /* CC=Create Config tables DC=Drop config tables CA=Create Context Audit Tables DA=Drop context Audit Tables*/
)
AS
DECLARE @CurrentSchema NVARCHAR(30) = schema_name(),
        @Owner NVARCHAR(30),
        @TriggerName NVARCHAR(30),
        @TableName NVARCHAR(30),
        @LogVersion INT,
        @IndexSpace VARCHAR(30),
        @SQLCommand NVARCHAR(MAX)
BEGIN
SELECT @LogVersion = LOGVERSION FROM ZAUDITCONFIG
IF @LogVersion = 1
BEGIN
  /*Desabilita Log antigo*/
  EXEC sp_rename 'ZLOGPROCESSOS', 'ZLOGPROCESSOS_OLD';
  /*Cria tabelas de auditoria de schema e context
  PRINT 'EXEC P_CREATEAUDITCONFIGTABLES @AuditOperation =''CA'''*/
  EXEC sp_executesql N'EXEC P_CREATEAUDITCONFIGTABLES @AuditOperation =''CA'''
  /*Habilita tabelas antigas no novo Log de Auditoria*/
  SET @SQLCommand = 'INSERT INTO ZAUDITITEMS(OBJECTNAME, STATUS, RECCREATEDBY, RECCREATEDON)' + CHAR(10)
  SET @SQLCommand += 'SELECT DISTINCT TABELA, CASE WHEN MAX(LOGATIVO) = ''S'' THEN 1 ELSE 0 END LOGATIVO, ''SYSTEM'', GETDATE() FROM ZLOGCAMPOS' + CHAR(10)
  SET @SQLCommand += 'WHERE NOT EXISTS (SELECT 1 FROM ZAUDITITEMS WHERE ZAUDITITEMS.OBJECTNAME = ZLOGCAMPOS.TABELA)' + CHAR(10)
  SET @SQLCommand += 'AND NOT EXISTS (SELECT 1 FROM SYS.VIEWS WHERE ZLOGCAMPOS.TABELA COLLATE SQL_Latin1_General_CP1_CI_AI = sys.views.name COLLATE SQL_Latin1_General_CP1_CI_AI)' + CHAR(10)
  SET @SQLCommand += 'AND TABELA NOT IN (''HCRMLOG'',''PLOGCALCTEXTO'')' + CHAR(10)
  SET @SQLCommand += 'AND TABELA IN (SELECT NAME COLLATE SQL_Latin1_General_CP1_CI_AI FROM SYS.TABLES WHERE TYPE = ''U'')' + CHAR(10)
  SET @SQLCommand += 'GROUP BY TABELA'
  /*PRINT @SqlCommand*/
  EXEC sp_executesql @SqlCommand
  /*Update tempo de expiração do log*/
  SET @SQLCommand = 'UPDATE ZAUDITCONFIG SET LOGSDAYLIFETIME = (SELECT NUMDIASLOG FROM ZLOGPARAMS)'
  /*PRINT @SqlCommand*/
  EXEC sp_executesql @SqlCommand
  /*Drop das triggers do antigo log de auditoria*/
  DECLARE [triggers] CURSOR READ_ONLY FOR
  SELECT T.NAME FROM SYS.triggers T INNER JOIN ZAUDITITEMS Z ON Z.OBJECTNAME COLLATE SQL_Latin1_General_CP1_CI_AI = OBJECT_NAME(T.PARENT_ID) COLLATE SQL_Latin1_General_CP1_CI_AI
  WHERE T.NAME LIKE 'LOG%'
  OPEN [triggers]
  FETCH NEXT FROM [triggers]
  INTO @TriggerName
  WHILE @@FETCH_STATUS = 0
  BEGIN
  SET @SQLCommand = 'DROP TRIGGER ' + @TriggerName
  /*PRINT @SQLCommand*/
  EXEC sp_executesql @SQLCommand
  FETCH NEXT FROM [triggers]
    INTO @TriggerName
  END
  CLOSE [triggers]
  DEALLOCATE [triggers]
  /*CRIA AS TABELAS E TRIGGERS REGISTRADAS NA ZAUDITITEMS SE LOG ESTIVE ATIVO
  PRINT 'EXEC p_AuditLog @AuditOperation = ''C''';*/
  IF EXISTS (SELECT 1 FROM ZLOGPARAMS WHERE LOGATIVO = 'S')
  BEGIN
  EXEC sp_executesql N'EXEC p_AuditLog @AuditOperation = ''C''';
  /*Define LOG ANTIGO como desabilitado*/
  EXEC sp_executesql N'UPDATE ZLOGPARAMS SET LOGATIVO = ''N''';
  /*Define LOG NOVO como habilitado*/
  EXEC sp_executesql N'UPDATE ZAUDITCONFIG SET STATUS = 1';
  END
END
ELSE IF @LogVersion = 2
BEGIN
  /*Drop das triggers do antigo log de auditoria*/
  DECLARE [triggers] CURSOR READ_ONLY FOR
  SELECT T.NAME FROM SYS.triggers T INNER JOIN ZAUDITITEMS Z ON Z.OBJECTNAME  COLLATE SQL_Latin1_General_CP1_CI_AI = OBJECT_NAME(T.PARENT_ID) COLLATE SQL_Latin1_General_CP1_CI_AI
  WHERE T.NAME LIKE 'LOG%'
  OPEN [triggers]
  FETCH NEXT FROM [triggers]
  INTO @TriggerName
  WHILE @@FETCH_STATUS = 0
  BEGIN
  SET @SQLCommand = 'DROP TRIGGER ' + @TriggerName
  /*PRINT @SQLCommand*/
  EXEC sp_executesql @SQLCommand
  FETCH NEXT FROM [triggers]
    INTO @TriggerName
  END
  CLOSE [triggers]
  DEALLOCATE [triggers]
  /*Rename das tabelas novo log de auditoria*/
  SET @SqlCommand = 'SELECT @Owner = AUDITSCHEMANAME, @IndexSpace = INDEXSPACE FROM ' + @CurrentSchema + '.ZAUDITCONFIG'
  EXECUTE sp_executesql @SqlCommand, N'@Owner varchar(128) out, @IndexSpace varchar(30) out', @Owner out, @indexSpace out
  DECLARE rename CURSOR READ_ONLY FOR
  SELECT OBJECTNAME FROM ZAUDITITEMS
  OPEN rename
  FETCH NEXT FROM rename
  INTO @TableName
  WHILE @@FETCH_STATUS = 0
  BEGIN
  /*Renomeia tabelas de auditoria*/
  SET @SQLCommand = 'EXEC SP_RENAME ' + '''' + @Owner + '.' + @TableName + '_LOG' + '''' + ', ' + '''' + @TableName + ''''
  /*PRINT @SQLCommand*/
  EXEC sp_executesql @SQLCommand
  /*Adiciona novos campos de controle nas tabelas de auditoria*/
  SET @SQLCommand = 'ALTER TABLE ' + @Owner + '.' + @TableName + ' ADD LOGAPP VARCHAR(128), LOGUSER VARCHAR(128)'
  /*PRINT @SQLCommand*/
  EXEC sp_executesql @SQLCommand
  /*Atualiza campo LOGAPP e LOGUSER*/
  SET @SQLCommand = 'UPDATE A SET A.LOGAPP = C.APPLICATIONNAME, A.LOGUSER = C.USERNAME FROM ' + @Owner + '.' + @TableName + ' A INNER JOIN ' + @Owner + '.ZAUDITCHANGES C ON C.AUDITID = A.AUDITID'
  /*PRINT @SQLCommand*/
  EXEC sp_executesql @SQLCommand
  /* Cria os indices faltantes*/
  SET @SQLCommand = 'CREATE NONCLUSTERED INDEX IX' + @TableName + '_AA ON ' + @Owner + '.' + @TableName + ' (AUDITID, AUDITACTION) INCLUDE (LOGID, PARENTLOGID) ON [' + @IndexSpace + ']'
  /*PRINT @SQLCommand*/
  EXEC sp_executesql @SQLCommand
  SET @SQLCommand = 'CREATE NONCLUSTERED INDEX IX' + @TableName + '_LR ON ' + @Owner + '.' + @TableName + ' (LOGAPP, LOGUSER) ON [' + @IndexSpace + ']'
  /*PRINT @SQLCommand*/
  EXEC sp_executesql @SQLCommand
  FETCH NEXT FROM rename
    INTO @TableName
  END
  CLOSE rename
  DEALLOCATE rename
  SET @SQLCommand = 'EXEC SP_RENAME ' + '''' + @Owner + '.ZAUDITCHANGES''' + ',' + '''ZAUDITCHANGES_OLD'''
  EXEC sp_executesql @SQLCommand
  SET @SQLCommand = 'EXEC SP_RENAME ' + '''' + @Owner + '.PKZAUDITCHANGES''' + ',' + '''PKZAUDITCHANGES_OLD'''
  EXEC sp_executesql @SQLCommand
  /*Cria tabelas de auditoria de schema e context*/
  EXEC sp_executesql N'EXEC P_CREATEAUDITCONFIGTABLES @AuditOperation =''CA'''
  SET @SQLCommand  = 'BEGIN' + CHAR(10)
  SET @SQLCommand += 'SET IDENTITY_INSERT ' + @Owner + '.ZAUDITCHANGES ON' + CHAR(10)
  SET @SQLCommand += 'INSERT INTO ' + @Owner + '.ZAUDITCHANGES(AUDITID, AUDITDATE, USERNAME, LOGINNAME, HOSTNAME, APPLICATIONNAME, APPLICATIONMODULE, MACHINEIP, AUDITACTION, RECORDSAFFECTED, AUDITOBJECTNAME)' + CHAR(10)
  SET @SQLCommand += 'SELECT AUDITID, AUDITDATE, USERNAME, LOGINNAME, HOSTNAME, APPLICATIONNAME, APPLICATIONMODULE, MACHINEIP, AUDITACTION, RECORDSAFFECTED, AUDITOBJECTNAME FROM ' + @Owner + '.ZAUDITCHANGES_OLD' + CHAR(10)
  SET @SQLCommand += 'SET IDENTITY_INSERT ' + @Owner + '.ZAUDITCHANGES OFF' + CHAR(10)
  SET @SQLCommand += 'END'
  EXEC sp_executesql @SQLCommand
  /*CRIA AS TABELAS E TRIGGERS REGISTRADAS NA ZAUDITITEMS SE LOG ATIVO*/
  IF EXISTS (SELECT 1 FROM ZAUDITCONFIG WHERE STATUS = 1)
  BEGIN
  EXEC sp_executesql N'EXEC p_AuditLog @AuditOperation = ''A'''
  EXEC sp_executesql N'EXEC p_AuditLog @AuditOperation = ''R'''
  EXEC sp_executesql N'EXEC p_AuditLog @AuditOperation = ''C'''
  END
END
UPDATE ZAUDITCONFIG SET LOGVERSION=3
EXEC sp_executesql N'EXEC p_ABATFUNLOGNEW'
END