Last active
October 23, 2018 13:53
-
-
Save hesenger/a0ad966656b879a59b7cb69752b5c134 to your computer and use it in GitHub Desktop.
Sql Server - Cria/atualiza uma tabela de log e sua trigger no schema especificado. O a tabela insere um registro sempre que há alteração/deleção na original.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- _LOGATUALIZARESTRUTURA 'dbo', NULL | |
ALTER PROCEDURE _LOGATUALIZARESTRUTURA(@SCHEMA VARCHAR(20), @TABLE VARCHAR(20)) | |
AS | |
BEGIN | |
/* | |
tipos a usar no sql server | |
- varchar (texto) | |
- int (máx 2.147.483.647) 4bytes | |
- tinyint (campos de tipo, máx 255) 1byte | |
- decimal(9,2) (moeda 2 dígitos de centavo max 9.999.999,99) 5bytes | |
- decimal(19,2) (decimal maior que 9,2) 9bytes (9 bytes se maior que 9,2 não compensa diminuir) | |
- date (campos data sem hora) 3bytes | |
- datetime2(0) (campos data e hora, recomendado ISO sql) 6bytes | |
*/ | |
SET NOCOUNT ON; | |
-- alterar para schema (deve funcionar com outro banco: 'bancolog.dbo', DESLIGAR CREATE SCHEMA) | |
DECLARE @SCHEMALOG VARCHAR(5) = 'log'; | |
DECLARE @SQL NVARCHAR(MAX); | |
-- CRIA O SCHEMA DE LOG SE NAO EXISTE | |
IF (NOT EXISTS(SELECT 1 FROM SYS.SCHEMAS S WHERE S.NAME = @SCHEMALOG)) | |
BEGIN | |
SET @SQL = CONCAT('CREATE SCHEMA ', @SCHEMALOG, ';'); | |
EXEC sys.sp_executesql @SQL; | |
END | |
CREATE TABLE #TABELAS ( | |
TABELAID INT NOT NULL IDENTITY PRIMARY KEY, | |
-- o nome da tabela não está seguro com [] (ESPAÇO EM NOME DE TABELA???) | |
NOME VARCHAR(100) NOT NULL, | |
NOMELOG VARCHAR(100) NOT NULL, | |
EXISTE BIT NOT NULL, | |
CAMPOS VARCHAR(MAX), | |
CAMPOSTIPOS VARCHAR(MAX), | |
CAMPOSADD VARCHAR(MAX), | |
CAMPOSALTER VARCHAR(MAX), | |
SQLCREATE VARCHAR(MAX), | |
SQLADD VARCHAR(MAX), | |
SQLALTER VARCHAR(MAX), | |
SQLTRIGGER VARCHAR(MAX) | |
); | |
INSERT INTO #TABELAS (NOME, NOMELOG, EXISTE, SQLCREATE, SQLADD, SQLALTER, SQLTRIGGER) | |
SELECT CONCAT(S.NAME, '.', T.NAME), | |
CONCAT(@SCHEMALOG, '.', T.NAME), | |
IIF(OBJECT_ID(CONCAT(@SCHEMALOG, '.', T.NAME)) IS NULL, 0, 1), | |
-- 0=TABELA DE LOG; 1=CAMPOS COM TIPOS | |
CONCAT('CREATE TABLE {0} ({1}, ', CHAR(13), | |
'LogId int NOT NULL IDENTITY(1,1) PRIMARY KEY, ', CHAR(13), | |
'LogData datetime2(0) NOT NULL);'), | |
-- 0=TABELA DE LOG; 1=CAMPOS ADD | |
'ALTER TABLE {0} ADD {1};', | |
-- 0=TABELA DE LOG; 1=CAMPOS ALTER | |
'ALTER TABLE {0} ALTER COLUMN {1};', | |
-- 0=TABELA ORIGINAL; 1=TABELA DE LOG; 2=CAMPOS | |
CONCAT('CREATE OR ALTER TRIGGER {0}LogT ON {0} AFTER UPDATE, DELETE AS', CHAR(13), | |
'SET NOCOUNT ON;', CHAR(13), | |
'INSERT INTO {1} ({2}, LogData)', CHAR(13), | |
'SELECT {2}, GETDATE() FROM DELETED;') | |
FROM SYS.TABLES T | |
JOIN SYS.SCHEMAS S ON S.SCHEMA_ID = T.SCHEMA_ID | |
WHERE (S.NAME = @SCHEMA) | |
AND (@TABLE IS NULL OR T.OBJECT_ID = OBJECT_ID(@TABLE)) | |
ORDER BY T.NAME; | |
-- CARREGA CAMPOS DAS TABELAS | |
UPDATE TT | |
SET CAMPOS = (SELECT CONCAT('[', C.NAME, '], ') | |
FROM SYS.COLUMNS C | |
JOIN SYS.TYPES T ON T.USER_TYPE_ID = C.USER_TYPE_ID | |
WHERE C.OBJECT_ID = OBJECT_ID(TT.NOME) | |
FOR XML PATH('')), | |
CAMPOSTIPOS = (SELECT CONCAT('[', C.NAME, '] ', | |
T.NAME, | |
CASE | |
WHEN T.NAME IN ('varchar', 'char', 'varbinary', 'binary', 'text') | |
THEN CONCAT('(', IIF(C.MAX_LENGTH = -1, 'MAX', CAST(C.MAX_LENGTH AS VARCHAR(5))), ')') | |
WHEN T.NAME IN ('nvarchar', 'nchar', 'ntext') | |
THEN CONCAT('(', IIF(C.MAX_LENGTH = -1, 'MAX', CAST(C.MAX_LENGTH / 2 AS VARCHAR(5))), ')') | |
WHEN T.NAME IN ('datetime2', 'time2', 'datetimeoffset') | |
THEN CONCAT('(', C.SCALE, ')') | |
WHEN T.NAME IN ('decimal', 'float') | |
THEN CONCAT('(', C.PRECISION, ',', C.SCALE, ')') | |
ELSE '' | |
END, | |
', ') | |
FROM SYS.COLUMNS C | |
JOIN SYS.TYPES T ON T.USER_TYPE_ID = C.USER_TYPE_ID | |
WHERE C.OBJECT_ID = OBJECT_ID(TT.NOME) | |
FOR XML PATH('')), | |
CAMPOSADD = (SELECT CONCAT('[', C.NAME, '] ', | |
T.NAME, | |
CASE | |
WHEN T.NAME IN ('varchar', 'char', 'varbinary', 'binary', 'text') | |
THEN CONCAT('(', IIF(C.MAX_LENGTH = -1, 'MAX', CAST(C.MAX_LENGTH AS VARCHAR(5))), ')') | |
WHEN T.NAME IN ('nvarchar', 'nchar', 'ntext') | |
THEN CONCAT('(', IIF(C.MAX_LENGTH = -1, 'MAX', CAST(C.MAX_LENGTH / 2 AS VARCHAR(5))), ')') | |
WHEN T.NAME IN ('datetime2', 'time2', 'datetimeoffset') | |
THEN CONCAT('(', C.SCALE, ')') | |
WHEN T.NAME IN ('decimal', 'float') | |
THEN CONCAT('(', C.PRECISION, ',', C.SCALE, ')') | |
ELSE '' | |
END, | |
', ') | |
FROM SYS.COLUMNS C | |
JOIN SYS.TYPES T ON T.USER_TYPE_ID = C.USER_TYPE_ID | |
LEFT JOIN SYS.COLUMNS JA ON JA.NAME = C.NAME AND JA.OBJECT_ID = OBJECT_ID(TT.NOMELOG) | |
WHERE C.OBJECT_ID = OBJECT_ID(TT.NOME) | |
AND (JA.COLUMN_ID IS NULL) | |
FOR XML PATH('')), | |
CAMPOSALTER = (SELECT CONCAT('[', C.NAME, '] ', | |
T.NAME, | |
C.MAX_LENGTH, | |
CASE | |
WHEN T.NAME IN ('varchar', 'char', 'varbinary', 'binary', 'text') | |
THEN CONCAT('(', IIF(C.MAX_LENGTH = -1, 'MAX', CAST(C.MAX_LENGTH AS VARCHAR(5))), ')') | |
WHEN T.NAME IN ('nvarchar', 'nchar', 'ntext') | |
THEN CONCAT('(', IIF(C.MAX_LENGTH = -1, 'MAX', CAST(C.MAX_LENGTH / 2 AS VARCHAR(5))), ')') | |
WHEN T.NAME IN ('datetime2', 'time2', 'datetimeoffset') | |
THEN CONCAT('(', C.SCALE, ')') | |
WHEN T.NAME IN ('decimal', 'float') | |
THEN CONCAT('(', C.PRECISION, ',', C.SCALE, ')') | |
ELSE '' | |
END, | |
', ') | |
FROM SYS.COLUMNS C | |
JOIN SYS.TYPES T ON T.USER_TYPE_ID = C.USER_TYPE_ID | |
LEFT JOIN SYS.COLUMNS JA ON JA.NAME = C.NAME AND JA.OBJECT_ID = OBJECT_ID(TT.NOMELOG) | |
WHERE C.OBJECT_ID = OBJECT_ID(TT.NOME) | |
AND (JA.COLUMN_ID IS NOT NULL AND C.MAX_LENGTH > JA.MAX_LENGTH) | |
FOR XML PATH('')) | |
FROM #TABELAS TT; | |
-- TIRA A ULTIMA VIRGULA DOS CAMPOS | |
UPDATE #TABELAS | |
SET CAMPOS = SUBSTRING(CAMPOS, 1, LEN(CAMPOS) - 1), | |
CAMPOSADD = SUBSTRING(CAMPOSADD, 1, LEN(CAMPOSADD) - 1), | |
CAMPOSALTER = SUBSTRING(CAMPOSALTER, 1, LEN(CAMPOSALTER) - 1), | |
CAMPOSTIPOS = SUBSTRING(CAMPOSTIPOS, 1, LEN(CAMPOSTIPOS) - 1); | |
-- SQLCREATE: 0=TABELA DE LOG; 1=CAMPOS COM TIPOS | |
-- SQLADD: 0=TABELA DE LOG; 1=CAMPOS ADD | |
-- SQLTRIGGER: 0=TABELA ORIGINAL; 1=TABELA DE LOG; 2=CAMPOS | |
UPDATE #TABELAS | |
SET SQLCREATE = REPLACE(SQLCREATE, '{0}', NOMELOG), | |
SQLADD = REPLACE(SQLADD, '{0}', NOMELOG), | |
SQLALTER = REPLACE(SQLALTER, '{0}', NOMELOG), | |
SQLTRIGGER = REPLACE(SQLTRIGGER, '{0}', NOME); | |
UPDATE #TABELAS | |
SET SQLCREATE = REPLACE(SQLCREATE, '{1}', CAMPOSTIPOS), | |
SQLADD = REPLACE(SQLADD, '{1}', CAMPOSADD), | |
SQLALTER = REPLACE(SQLALTER, '{1}', CAMPOSALTER), | |
SQLTRIGGER = REPLACE(SQLTRIGGER, '{1}', NOMELOG); | |
UPDATE #TABELAS | |
SET SQLTRIGGER = REPLACE(SQLTRIGGER, '{2}', CAMPOS); | |
UPDATE #TABELAS | |
SET SQLCREATE = IIF(EXISTE = 1, NULL, SQLCREATE), | |
SQLADD = IIF(EXISTE = 0, NULL, SQLADD), | |
SQLALTER = IIF(EXISTE = 0, NULL, SQLALTER), | |
SQLTRIGGER = IIF(EXISTE = 1 AND CAMPOSADD IS NULL, NULL, SQLTRIGGER); | |
-- SELECT * | |
-- FROM #TABELAS; | |
-- RETURN; | |
CREATE TABLE #SAIDA ( | |
ID INT NOT NULL IDENTITY PRIMARY KEY, | |
TABELA VARCHAR(100), | |
ERRO VARCHAR(MAX), | |
DATAHORA DATETIME2(0) | |
); | |
DECLARE @NOMETABELA VARCHAR(50), | |
@SQLCREATE NVARCHAR(MAX), | |
@SQLADD NVARCHAR(MAX), | |
@SQLALTER NVARCHAR(MAX), | |
@SQLTRIGGER NVARCHAR(MAX); | |
DECLARE dbcursor CURSOR FOR | |
SELECT T.NOME, T.SQLCREATE, T.SQLADD, T.SQLALTER, T.SQLTRIGGER | |
FROM #TABELAS T; | |
OPEN dbcursor | |
FETCH NEXT FROM dbcursor INTO @NOMETABELA, @SQLCREATE, @SQLADD, @SQLALTER, @SQLTRIGGER; | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
BEGIN TRY | |
SET @SQL = @SQLCREATE; | |
IF (@SQL IS NOT NULL) EXEC sys.sp_executesql @SQL; | |
SET @SQL = @SQLADD; | |
IF (@SQL IS NOT NULL) EXEC sys.sp_executesql @SQL; | |
SET @SQL = @SQLALTER; | |
IF (@SQL IS NOT NULL) EXEC sys.sp_executesql @SQL; | |
SET @SQL = @SQLTRIGGER; | |
IF (@SQL IS NOT NULL) EXEC sys.sp_executesql @SQL; | |
IF (@SQLCREATE IS NOT NULL OR @SQLADD IS NOT NULL | |
OR @SQLALTER IS NOT NULL OR @SQLTRIGGER IS NOT NULL) | |
BEGIN | |
INSERT INTO #SAIDA (TABELA, DATAHORA) VALUES (@NOMETABELA, GETDATE()); | |
END | |
END TRY | |
BEGIN CATCH | |
INSERT INTO #SAIDA (TABELA, ERRO, DATAHORA) | |
VALUES (@NOMETABELA, CONCAT(ERROR_NUMBER(), ' - ', ERROR_MESSAGE(), CHAR(13), @SQL), GETDATE()); | |
END CATCH | |
FETCH NEXT FROM dbcursor INTO @NOMETABELA, @SQLCREATE, @SQLADD, @SQLALTER, @SQLTRIGGER; | |
END | |
CLOSE dbcursor; | |
DEALLOCATE dbcursor; | |
SELECT TABELA, ERRO, DATAHORA FROM #SAIDA; | |
END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Esta solução de log/auditoria de tabelas foi criada como paliativa mas tende a funcionar bem em
ambientes de produção já que provavelmente terá pouco impacto na performance.
A procedure cria uma tabela com os mesmos campos da original, e adiciona campos novos
quando executada e a tabela já existir. Campos que tiverem seu tamanho aumentado também
são alterados.
Além da tabela, é criada uma trigger na tabela original que insere um registro na tabela de log
a cada update/delete com o estado dos campos antes da alteração mais um campo de data/hora atual.
Uma recomendação é ter um campo com referência ao usuário na tabela original, que a aplicação
atualize em toda inserção/alteração, para que seja possível rastrear.