EXEMPLO_1212310 Database

Parameters

Name	Type	Mode
@TableName	nvarchar	IN
@AuditOperation	nvarchar	IN
@TriggerType	nvarchar	IN
@Compression	int	IN

Definition

CREATE PROCEDURE p_auditlog (
/*Version=11.11.19*/
@TableName NVARCHAR(128) = NULL,
@AuditOperation NVARCHAR(2) = NULL, /* CC=Create Config tables DC=Drop config tables CA=Create Context Audit Tables DA=Drop context Audit Tables*/
@TriggerType NVARCHAR(3) = NULL, /* I=INSERT, U=UPDATE, D=DELETE */
@Compression int = 0
)
AS
DECLARE @CurrentSchema NVARCHAR(30) = schema_name(),
		@Owner NVARCHAR(30),
		@SQLCommand NVARCHAR(MAX),
		@ObjectName NVARCHAR(128),
		@InsertOn BIT,
		@UpdateOn BIT,
		@DeleteOn BIT
SET NOCOUNT ON
SET @SqlCommand = 'SELECT @Owner = AUDITSCHEMANAME FROM ' + @CurrentSchema + '.ZAUDITCONFIG'
EXECUTE sp_executesql @SqlCommand, N'@Owner sysname out', @Owner out
/* Insere o registro da tabela a ser auditada se a mesma não existir, caso exista faz o update das triggers qye devem existir */
IF NOT EXISTS (SELECT 1 FROM ZAUDITITEMS WHERE OBJECTNAME = @TableName) AND @TableName IS NOT NULL AND EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME = @TableName) AND @AuditOperation NOT IN ('D','DT') AND NOT EXISTS (SELECT 1 FROM GDIC WHERE TABELA = @TableName AND PESSOAL > 0) AND @TableName NOT IN ('GDIC','GDICDATAPROTECTION') AND @AuditOperation NOT IN ('L')
BEGIN
  SET @SQLCommand = 'INSERT INTO ZAUDITITEMS(OBJECTNAME, STATUS, UPD_ON, INS_ON, DEL_ON, ISSYSTEM, RECCREATEDBY, RECCREATEDON)' + ChAr(10)
  SET @SQLCommand += 'VALUES(''' + @TableName + ''', 1, CASE WHEN CHARINDEX(''U'', Upper(''' + @TriggerType + ''')) > 0 THEN 1 ELSE 0 END, CASE WHEN CHARINDEX(''I'', Upper(''' + @TriggerType + ''')) > 0 THEN 1 ELSE 0 END,CASE WHEN CHARINDEX(''D'', Upper(''' + @TriggerType +''')) > 0 THEN 1 ELSE 0 END, 0, ''SYSTEM'', GETDATE())'
  EXECUTE sp_executesql @SqlCommand
END
ELSE IF EXISTS (SELECT 1 FROM ZAUDITITEMS WHERE OBJECTNAME = @TableName) AND @TableName IS NOT NULL AND @AuditOperation NOT IN ('RC','CD','CC','L')
BEGIN
  SET @SQLCommand = 'UPDATE ZAUDITITEMS ' + ChAr(10)
  SET @SQLCommand += 'SET STATUS = 1,  UPD_ON = CASE WHEN CHARINDEX(''U'', Upper(''' + @TriggerType + ''')) > 0 THEN 1 ELSE 0 END, INS_ON = CASE WHEN CHARINDEX(''I'', Upper(''' + @TriggerType + ''')) > 0 THEN 1 ELSE 0 END, DEL_ON = CASE WHEN CHARINDEX(''D'', Upper(''' + @TriggerType + ''')) > 0 THEN 1 ELSE 0 END' + ChAr(10)
  SET @SQLCommand += 'WHERE OBJECTNAME = ''' + @TableName  + ''''
  EXECUTE sp_executesql @SQLCommand
END
/* Habilita o LOG de auditoria para todas as tabelas caso não seja passado o nome da tabela, senão define o status da tabela para ativa. */
IF @AuditOperation IN ('C', 'CT') AND @TableName IS NULL
BEGIN
  SET @SQLCommand = 'UPDATE ZAUDITITEMS ' + ChAr(10)
  SET @SQLCommand += 'SET STATUS = 1'
  EXECUTE sp_executesql @SQLCommand
  SET @SQLCommand = 'UPDATE ZAUDITCONFIG ' + ChAr(10)
  SET @SQLCommand += 'SET STATUS = 1'
  EXECUTE sp_executesql @SQLCommand
END
ELSE IF @AuditOperation IN ('C', 'CT') AND @TableName IS NOT NULL
BEGIN
  SET @SQLCommand = 'UPDATE ZAUDITITEMS ' + ChAr(10)
  SET @SQLCommand += 'SET STATUS = 1 WHERE OBJECTNAME = ''' + @TableName + ''''
  EXECUTE sp_executesql @SQLCommand
END
/* Desabilita o Log de Auditoria */
IF @AuditOperation IN ('DT', 'D') AND @TableName IS NULL
BEGIN
  SET @SQLCommand = 'UPDATE ZAUDITCONFIG ' + ChAr(10)
  SET @SQLCommand += 'SET STATUS = 0'
  EXECUTE sp_executesql @SQLCommand
END
BEGIN
  /*define a cursor to get the columns of the target table. order by COLUMN_ID is important*/
  DECLARE [triggers] CURSOR READ_ONLY FOR
  SELECT OBJECTNAME, UPD_ON, INS_ON, DEL_ON FROM ZAUDITITEMS WHERE OBJECTNAME LIKE CASE WHEN @TableName IS NULL THEN '%' ELSE Upper(@TableName) END
  UNION
  SELECT DISTINCT TABELA, 1, 1, 1 FROM GDIC WHERE PESSOAL > 0 /* AND Upper(@AuditOperation) NOT IN ('D','DT') */ AND TABELA LIKE CASE WHEN @TableName IS NULL THEN '%' ELSE Upper(@TableName) END
  UNION
  SELECT DISTINCT TABELA, 1, 1, 1 FROM GDIC WHERE @TABLENAME IS NULL AND TABELA IN ('GDIC','GDICDATAPROTECTION')
  UNION
  SELECT DISTINCT TABELA, 1, 1, 1 FROM GDIC WHERE TABELA = @TableName AND UPPER(@AuditOperation) = 'L'
  ORDER BY 1
  OPEN [triggers]
  FETCH NEXT FROM [triggers]
  INTO @ObjectName,@UpdateOn,@InsertOn,@DeleteOn
  WHILE @@FETCH_STATUS = 0
  BEGIN
	/* Cria as Tabelas de auditoria e triggers*/
    IF Upper(@AuditOperation) IN ('C', 'CT', 'RC')
    BEGIN
    SET @SQLCommand = 'EXEC p_TableTriggerGenerator''' + @ObjectName + ''', ''C'',' + CAST(@Compression as CHAR(1))
	/*PRINT (@SQLCommand)*/
    EXECUTE sp_executesql @SQLCommand
    SET @SQLCommand = 'EXEC p_TriggerGenerator''' + @ObjectName + ''', ''' + CASE WHEN @InsertOn = 1 THEN 'I' ELSE '' END + CASE WHEN @UpdateOn = 1 THEN 'U' ELSE '' END + CASE WHEN @DeleteOn = 1 THEN 'D' ELSE '' END + ''''
    /*PRINT (@SQLCommand)*/
    EXECUTE sp_executesql @SQLCommand
	PRINT 'Criando Triggers de auditoria para a tabela ' + @ObjectName
    END
	/* Deleta as tabelas de auditoria e as triggers */
    IF Upper(@AuditOperation) = 'D'
    BEGIN
	SET @SQLCommand = 'EXEC p_TableTriggerGenerator''' + @ObjectName + ''', ''D'''
    /*PRINT (@SQLCommand)*/
    EXECUTE sp_executesql @SQLCommand
    SET @SQLCommand = 'EXEC p_TriggerGenerator''' + @ObjectName + ''', '''''
    /*PRINT (@SQLCommand)*/
    EXECUTE sp_executesql @SQLCommand
	SET @SQLCommand = 'DELETE FROM ZAUDITITEMS WHERE OBJECTNAME = ''' + @ObjectName + ''''
    EXECUTE (@SQLCommand)
	PRINT 'Deletando Triggers e tabela de auditoria da tabela ' + @ObjectName
    END
	/* Deleta as triggers de auditoria */
    IF Upper(@AuditOperation) = 'DT'
    BEGIN
    SET @SQLCommand = 'EXEC p_TriggerGenerator''' + @ObjectName + ''', '''''
    /*PRINT (@SQLCommand)*/
    EXECUTE sp_executesql @SQLCommand
	IF EXISTS (SELECT 1 FROM ZAUDITCONFIG WHERE STATUS = 0)
	BEGIN
	SET @SQLCommand = 'UPDATE ZAUDITITEMS SET STATUS = 0 WHERE OBJECTNAME = ''' + @ObjectName + ''''
    EXECUTE sp_executesql @SQLCommand
	END
	ELSE
	BEGIN
	SET @SQLCommand = 'UPDATE ZAUDITITEMS SET STATUS = 0, UPD_ON = 0, INS_ON = 0, DEL_ON = 0 WHERE OBJECTNAME = ''' + @ObjectName + ''''
	EXECUTE sp_executesql @SQLCommand
	END
	PRINT 'Deletando Triggers de auditoria da tabela ' + @ObjectName
	END
	/* Remove e Recria triggers no conversor */
	IF Upper(@AuditOperation) = 'CD'
    BEGIN
    SET @SQLCommand = 'EXEC p_TriggerGenerator''' + @ObjectName + ''', '''''
    /*PRINT (@SQLCommand)*/
    EXECUTE sp_executesql @SQLCommand
	END
	IF Upper(@AuditOperation) = 'CC' AND EXISTS (SELECT 1 FROM ZAUDITCONFIG WHERE STATUS = 1)
    BEGIN
    SET @SQLCommand = 'EXEC p_TableTriggerGenerator''' + @ObjectName + ''', ''C'',' + CAST(@Compression as CHAR(1))
	/*PRINT (@SQLCommand)*/
    EXECUTE sp_executesql @SQLCommand
    SET @SQLCommand = 'EXEC p_TriggerGenerator''' + @ObjectName + ''', ''' + CASE WHEN @InsertOn = 1 THEN 'I' ELSE '' END + CASE WHEN @UpdateOn = 1 THEN 'U' ELSE '' END + CASE WHEN @DeleteOn = 1 THEN 'D' ELSE '' END + ''''
    /*PRINT (@SQLCommand)*/
    EXECUTE sp_executesql @SQLCommand
	END
	/* Adiciona novos campos na tabela de auditoria */
    IF Upper(@AuditOperation) = 'A'
    BEGIN
	SET @SQLCommand = 'EXEC p_TableTriggerGenerator''' + @ObjectName + ''', ''A'''
    /*PRINT (@SQLCommand)*/
    EXECUTE sp_executesql @SQLCommand
	PRINT 'Adicionando campos novos na tabela de auditoria ' + @ObjectName
    END
	/* Remove campos antigos da tabela de auditoria */
    IF Upper(@AuditOperation) = 'R'
    BEGIN
	SET @SQLCommand = 'EXEC p_TableTriggerGenerator''' + @ObjectName + ''', ''R'''
    /*PRINT (@SQLCommand)*/
    EXECUTE sp_executesql @SQLCommand
	PRINT 'Removendo campos antigos da tabela de auditoria ' + @ObjectName
    END
	/* Modifica o tipo de dados da tabela de auditoria */
    IF Upper(@AuditOperation) = 'M'
    BEGIN
	SET @SQLCommand = 'EXEC p_TableTriggerGenerator''' + @ObjectName + ''', ''M'''
    /*PRINT (@SQLCommand)*/
    EXECUTE sp_executesql @SQLCommand
	PRINT 'Alterando tipos de dados da tabela de auditoria ' + @ObjectName
    END
    IF Upper(@AuditOperation) = 'L' AND @ObjectName IS NOT NULL
    BEGIN
	IF EXISTS (SELECT 1 FROM GDIC WHERE TABELA = @ObjectName AND PESSOAL > 0) AND NOT EXISTS (SELECT 1 FROM SYS.TRIGGERS WHERE OBJECT_NAME(PARENT_ID) = @ObjectName AND NAME LIKE 'LOG%')
	BEGIN
		SET @SQLCommand = 'EXEC p_TableTriggerGenerator''' + @ObjectName + ''', ''C'',' + CAST(@Compression as CHAR(1))
		/*PRINT (@SQLCommand)*/
		EXECUTE sp_executesql @SQLCommand
		SET @SQLCommand = 'EXEC p_TriggerGenerator''' + @ObjectName + ''', ''' + CASE WHEN @InsertOn = 1 THEN 'I' ELSE '' END + CASE WHEN @UpdateOn = 1 THEN 'U' ELSE '' END + CASE WHEN @DeleteOn = 1 THEN 'D' ELSE '' END + ''''
		/*PRINT (@SQLCommand)*/
		EXECUTE sp_executesql @SQLCommand
		PRINT 'Ativando as triggers LGPD da tabela ' + @ObjectName
	END
	ELSE IF NOT EXISTS (SELECT 1 FROM GDIC WHERE TABELA = @ObjectName AND PESSOAL > 0) AND EXISTS (SELECT 1 FROM SYS.TRIGGERS WHERE OBJECT_NAME(PARENT_ID) = @ObjectName AND NAME LIKE 'LOG%') AND NOT EXISTS (SELECT 1 FROM ZAUDITITEMS WHERE OBJECTNAME = @ObjectName)
	BEGIN
		SET @SQLCommand = 'EXEC p_TriggerGenerator''' + @ObjectName + ''', '''''
		--PRINT (@SQLCommand)
		EXECUTE sp_executesql @SQLCommand
		PRINT 'Desativando as triggers LGPD da tabela ' + @ObjectName
	END
	END
    FETCH NEXT FROM [triggers]
	INTO @ObjectName,@UpdateOn,@InsertOn,@DeleteOn
  END
    CLOSE [triggers]
	DEALLOCATE [triggers]
END