EXEMPLO_1212310 Database

Parameters

Name	Type	Mode

Definition

CREATE PROCEDURE p_CheckNovoLog
/*Version=07.08.19*/
AS
IF EXISTS (SELECT * FROM ZAUDITCONFIG WHERE LOGVERSION = 3)
BEGIN
PRINT 'VERSÃO LOG = 3, NOVO LOG'
    /* VERIFICA SE EXISTE O SCHEMA DO NOVO LOG*/
    IF NOT EXISTS (SELECT * FROM SYS.schemas WHERE name COLLATE SQL_Latin1_General_CP1_CI_AI = (SELECT AUDITSCHEMANAME COLLATE SQL_Latin1_General_CP1_CI_AI FROM ZAUDITCONFIG))
    PRINT 'ERRO: SCHEMA DO NOVO LOG NÃO EXISTE'
    /*VERIFICA SE EXISTEM AS TABELAS DO NOVO LOG*/
    IF NOT EXISTS (SELECT SCHEMA_NAME(SCHEMA_ID), NAME FROM SYS.TABLES WHERE NAME IN ('ZAUDITCONFIG'))
    PRINT 'ERRO: TABELA ZAUDITCONFIG NÃO EXISTE FAVOR VERIFICAR'
    IF NOT EXISTS (SELECT SCHEMA_NAME(SCHEMA_ID), NAME FROM SYS.TABLES WHERE NAME IN ('ZAUDITITEMS'))
    PRINT 'ERRO: TABELA ZAUDITITEMS NÃO EXISTE FAVOR VERIFICAR'
    IF NOT EXISTS (SELECT SCHEMA_NAME(SCHEMA_ID), NAME FROM SYS.TABLES WHERE NAME IN ('ZAUDITEXCEPTION'))
    PRINT 'ERRO: TABELA ZAUDITEXCEPTION NÃO EXISTE FAVOR VERIFICAR'
    IF NOT EXISTS (SELECT SCHEMA_NAME(SCHEMA_ID), NAME FROM SYS.TABLES WHERE NAME IN ('ZAUDITCHANGES'))
    PRINT 'ERRO: TABELA ZAUDITCHANGES NÃO EXISTE FAVOR VERIFICAR'
    IF NOT EXISTS (SELECT SCHEMA_NAME(SCHEMA_ID), NAME FROM SYS.TABLES WHERE NAME IN ('ZAUDITSCHEMAEVENTS'))
    PRINT 'ERRO: TABELA ZAUDITSCHEMAEVENTS NÃO EXISTE FAVOR VERIFICAR'
    /*Verifica funções e procedures*/
    IF NOT EXISTS (select * from sys.objects where name = 'FN_GETCONTEXT')
    PRINT 'ERRO: FUNÇÃO FN_GETCONTEXT NÃO EXISTE FAVOR VERIFICAR'
    IF NOT EXISTS (select * from sys.objects where name = 'P_AUDITLOG')
    PRINT 'ERRO: PROCEDURE P_AUDITLOG NÃO EXISTE FAVOR VERIFICAR'
    IF NOT EXISTS (select * from sys.objects where name = 'P_CREATEAUDITCONFIGTABLES')
    PRINT 'ERRO: PROCEDURE P_CREATEAUDITCONFIGTABLES NÃO EXISTE FAVOR VERIFICAR'
    IF NOT EXISTS (select * from sys.objects where name = 'P_DDLTRIGGERGENERATOR')
    PRINT 'ERRO: PROCEDURE P_DDLTRIGGERGENERATOR NÃO EXISTE FAVOR VERIFICAR'
    IF NOT EXISTS (select * from sys.objects where name = 'P_TABLETRIGGERGENERATOR')
    PRINT 'ERRO: PROCEDURE P_TABLETRIGGERGENERATOR NÃO EXISTE FAVOR VERIFICAR'
    IF NOT EXISTS (select * from sys.objects where name = 'P_TRIGGERGENERATOR')
    PRINT 'ERRO: PROCEDURE P_TRIGGERGENERATOR NÃO EXISTE FAVOR VERIFICAR'
    IF NOT EXISTS (select * from sys.objects where name = 'P_CHANGEFILELOCATION')
    PRINT 'ERRO: PROCEDURE P_CHANGEFILELOCATION NÃO EXISTE FAVOR VERIFICAR'
    /*Quantidade de tabelas no schema de auditoria x tabelas auditadas*/
    DECLARE @TableName VARCHAR(30),
            @Tables VARCHAR(MAX) = ''
    DECLARE Tables CURSOR READ_ONLY FOR
    SELECT OBJECTNAME FROM ZAUDITITEMS ITEMS WHERE STATUS = 1 AND NOT EXISTS (SELECT 1 FROM sys.tables T WHERE T.name COLLATE SQL_Latin1_General_CP1_CI_AI = ITEMS.OBJECTNAME AND T.schema_id = (SELECT schema_id(AUDITSCHEMANAME) FROM ZAUDITCONFIG));
    OPEN Tables
    FETCH NEXT FROM Tables
    INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF @Tables = ''
    SET @Tables = @TableName
    ELSE
    SET @Tables += ',' + @TableName
    FETCH Next FROM Tables
    INTO @TableName
    END
    CLOSE Tables
    DEALLOCATE Tables
    If @Tables != ''
    BEGIN
    PRINT 'ERRO: A(S) TABELA(S) AUDITADA(S) (' + @Tables + ') NÃO EXISTE(M) NO SCHEMA DA AUDITORIA, FAVOR VERIFICAR'
    END
    /*Verifica se todas as colunas existem na tabela de destino*/
    DECLARE @CurrentSchema varchar(64) = schema_name(),
            @Owner varchar(30),
            @ColumnName varchar(30),
            @ListOfFields varchar(max) = ''
    SELECT @Owner = AUDITSCHEMANAME FROM ZAUDITCONFIG
    DECLARE Tables CURSOR READ_ONLY FOR
    SELECT ZI.OBJECTNAME FROM ZAUDITITEMS ZI WHERE EXISTS (SELECT 1 FROM SYS.TABLES T WHERE T.NAME COLLATE SQL_Latin1_General_CP1_CI_AI  = ZI.OBJECTNAME COLLATE SQL_Latin1_General_CP1_CI_AI AND T.schema_id = SCHEMA_ID(@Owner))
    ORDER BY OBJECTNAME
    OPEN Tables
    FETCH NEXT FROM Tables
    INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE TableColumns CURSOR READ_ONLY FOR
        SELECT C2.COLUMN_NAME
        FROM [INFORMATION_SCHEMA].[COLUMNS] C2
        WHERE TABLE_NAME= @TableName
              AND TABLE_SCHEMA = @CurrentSchema
              AND COLUMNPROPERTY(OBJECT_ID(C2.TABLE_SCHEMA+'.'+C2.TABLE_NAME),C2.COLUMN_NAME,'ISCOMPUTED') = 0
              AND DATA_TYPE NOT IN (select name from sys.types where system_type_id in (select system_type_id from sys.types where system_type_id = user_type_id and name in ('TEXT','NTEXT','IMAGE','TIMESTAMP')))
              AND COLUMN_NAME NOT IN ('RECCREATEDBY', 'RECCREATEDON', 'RECMODIFIEDBY', 'RECMODIFIEDON')
        AND C2.COLUMN_NAME NOT IN (SELECT COLUMN_NAME
            FROM [INFORMATION_SCHEMA].[COLUMNS]
            WHERE TABLE_NAME= @TableName
            AND TABLE_SCHEMA = @Owner
            AND COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),COLUMN_NAME,'ISCOMPUTED') = 0)
        ORDER BY COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),COLUMN_NAME,'ColumnId')
        OPEN TableColumns
            FETCH NEXT FROM TableColumns
            INTO @ColumnName
            WHILE @@FETCH_STATUS = 0
            BEGIN
                IF @ListOfFields = ''
                SET @ListOfFields += @ColumnName
                ELSE
                SET @ListOfFields += ',' + @ColumnName
            FETCH Next FROM TableColumns
            INTO @ColumnName
            END
        CLOSE TableColumns
        DEALLOCATE TableColumns
    IF @ListOfFields != ''
    BEGIN
    PRINT 'ERRO: FAVOR VERIFICAR SE A(S) NOVA(S) COLUNA(S) ' + @ListOfFields + ' DA TABLE ' + @TableName + ' EXISTE(M) NA TABELA ESPELHO NO SCHEMA ' + @Owner
    END
    SET @ListOfFields = ''
    FETCH Next FROM Tables
    INTO @TableName
    END
    CLOSE Tables
    DEALLOCATE Tables
    /*Checa Se todas as triggers existem*/
    DECLARE @Insert VARCHAR(3),
            @Update VARCHAR(3),
            @Delete VARCHAR(3)
    DECLARE Triggers CURSOR READ_ONLY FOR
    SELECT  OBJECTNAME,
        CASE WHEN INS_ON = (SELECT COUNT(*) FROM SYS.TRIGGERS ST WHERE OBJECT_NAME(ST.PARENT_ID) COLLATE SQL_Latin1_General_CP1_CI_AI = ZI.OBJECTNAME AND ST.NAME COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%INSERT') THEN 'OK' ELSE 'NOK' END INS,
        CASE WHEN UPD_ON = (SELECT COUNT(*) FROM SYS.TRIGGERS ST WHERE OBJECT_NAME(ST.PARENT_ID) COLLATE SQL_Latin1_General_CP1_CI_AI = ZI.OBJECTNAME AND ST.NAME COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%UPDATE') THEN 'OK' ELSE 'NOK' END UPD,
        CASE WHEN DEL_ON = (SELECT COUNT(*) FROM SYS.TRIGGERS ST WHERE OBJECT_NAME(ST.PARENT_ID) COLLATE SQL_Latin1_General_CP1_CI_AI = ZI.OBJECTNAME AND ST.NAME COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%DELETE') THEN 'OK' ELSE 'NOK' END DEL
    FROM ZAUDITITEMS ZI WHERE ZI.STATUS = 1 AND
    ((CASE WHEN INS_ON = (SELECT COUNT(*) FROM SYS.TRIGGERS ST WHERE OBJECT_NAME(ST.PARENT_ID) COLLATE SQL_Latin1_General_CP1_CI_AI = ZI.OBJECTNAME AND ST.NAME COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%INSERT') THEN 'OK' ELSE 'NOK' END) = 'NOK' OR
    (CASE WHEN UPD_ON = (SELECT COUNT(*) FROM SYS.TRIGGERS ST WHERE OBJECT_NAME(ST.PARENT_ID) COLLATE SQL_Latin1_General_CP1_CI_AI = ZI.OBJECTNAME AND ST.NAME COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%UPDATE') THEN 'OK' ELSE 'NOK' END) = 'NOK' OR
    (CASE WHEN DEL_ON = (SELECT COUNT(*) FROM SYS.TRIGGERS ST WHERE OBJECT_NAME(ST.PARENT_ID) COLLATE SQL_Latin1_General_CP1_CI_AI = ZI.OBJECTNAME AND ST.NAME COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%DELETE') THEN 'OK' ELSE 'NOK' END) = 'NOK')
    OPEN Triggers
    FETCH NEXT FROM Triggers
    INTO @TableName, @Insert, @Update, @Delete
    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF @TableName IS NOT NULL
    PRINT 'ERRO: VERIFIQUE SE A(S) TRIGGER(S) DE ' +
          CASE WHEN @Insert = 'NOK' THEN 'INSERT' ELSE '' END +
          CASE WHEN @Update = 'NOK' AND @Insert = 'NOK' THEN ', UPDATE' WHEN @Update = 'NOK' and @Insert = 'OK' THEN 'UPDATE' ELSE '' END +
          CASE WHEN @Delete = 'NOK' AND @Update = 'NOK' THEN ', DELETE' WHEN @Delete = 'NOK' and @Update = 'OK' THEN 'DELETE' ELSE '' END +
          ' DA TABELA ' + @TableName + ' FORAM CRIADAS'
    FETCH Next FROM Triggers
    INTO @TableName, @Insert, @Update, @Delete
    END
    CLOSE Triggers
    DEALLOCATE Triggers
    PRINT 'CASO SEJAM APRESENTADAS MENSAGEMS DE ERRO, E NECESSITE DE ALGUMA AJUDA NA CORREÇÃO POR FAVOR ENTRAR EM CONTATO COM O SUPORTE TOTVS.'
END
ELSE
BEGIN
PRINT 'ERRO: LOG VERSÃO 1'
    /*VERIFICA SE EXISTEM AS TABELAS DO NOVO LOG*/
    IF NOT EXISTS (SELECT SCHEMA_NAME(SCHEMA_ID), NAME FROM SYS.TABLES WHERE NAME IN ('ZAUDITCONFIG'))
    PRINT 'ERRO: TABELA ZAUDITCONFIG NÃO EXISTE FAVOR VERIFICAR'
    IF NOT EXISTS (SELECT SCHEMA_NAME(SCHEMA_ID), NAME FROM SYS.TABLES WHERE NAME IN ('ZAUDITITEMS'))
    PRINT 'ERRO: TABELA ZAUDITITEMS NÃO EXISTE FAVOR VERIFICAR'
    IF NOT EXISTS (SELECT SCHEMA_NAME(SCHEMA_ID), NAME FROM SYS.TABLES WHERE NAME IN ('ZAUDITEXCEPTION'))
    PRINT 'ERRO: TABELA ZAUDITEXCEPTION NÃO EXISTE FAVOR VERIFICAR'
PRINT 'CASO SEJAM APRESENTADAS MENSAGEMS DE ERRO, E NECESSITE DE ALGUMA AJUDA NA CORREÇÃO POR FAVOR ENTRAR EM CONTATO COM O SUPORTE TOTVS.'
END