PC SOFT

FORUMS PROFESSIONNELS
WINDEVWEBDEV et WINDEV Mobile

Accueil → WINDEV 25 → WX - Criar tabela de Auditoria e Insert \ Update \ Delete disparadores para uma determinada tabela
WX - Criar tabela de Auditoria e Insert \ Update \ Delete disparadores para uma determinada tabela
Débuté par BOLLER, 22 fév. 2017 19:57 - 4 réponses
Membre enregistré
3 651 messages
Popularité : +175 (223 votes)
Posté le 22 février 2017 - 19:57
Criar tabela de Auditoria e Insert \ Update \ Delete disparadores para uma determinada tabela
----------------------------------------------------------------------------------------------------------------------------------

Auditoria é sempre um tipo de exigência padrão em todo o sistema de trasactional. Para auditar uma tabela em particular, precisamos criar uma tabela de auditoria estrutura semelhante e criar INSERT, UPDATE e Excluir gatilhos para acompanhar as mudanças na tabela. Este procedimento é de utilidade.

Auditoria é sempre um tipo de exigência padrão em todo o sistema de trasactional. Para auditar uma tabela em particular, precisamos criar uma tabela de auditoria estrutura semelhante e criar INSERT, UPDATE e Excluir gatilhos para acompanhar as mudanças na tabela. Este procedimento é de utilidade vai criar abaixo objectos na base de dados da tabela passada.

1. Cria uma tabela de auditoria com o nome <Tablename> _audit. Ele tem alguns campos adicionais para capturar as alterações na tabela

AuditDataState - armazena o estado de dados se "Novo" ou "Velho"

AuditDMLAction - armazena o Ação DML como "Inserir", "Update", "Delete"

AuditUser - Armazena o usuário executou a ação na mesa

AuditDateTime - armazena o tempo data em que a ação aconteceu.

2. Cria um disparador de inserção com o nome <TableName> _insert

3. Cria um disparador de exclusão com o nome <TableName> _Delete

4. Cria um disparador de atualização com o nome <TableName> _Update

Este procedimento tem uma opção para gerar apenas os scripts e não para gerar os objetos fisicamente no banco de dados.


parâmetros

@Schemaname - SchemaName ao qual a tabela pertence. O valor padrão 'dbo'.

@TableName - TableName para o qual necessita dos procs a ser gerado.

@GenerateScriptOnly - Quando passado 1, isso vai gerar os scripts sozinho.

Quando passado 0, isso vai criar as tabelas de auditoria e triggers no banco de dados atual.

O valor padrão é 1

@ForceDropAuditTable - Quando passado 1, vai cair a tabela de auditoria e recreate.When passado 0, irá gerar o valor scripts.Default alter é 0

@IgnoreExistingColumnMismatch - Quando passado 1, não vai parar com o erro sobre a incompatibilidade de coluna existente e criará o gatilho. Quando passado 0, vai parar com o erro sobre a incompatibilidade de coluna existente. O valor padrão é 0

@DontAuditforUsers - Passe nome do usuário como separados por vírgula para os quais não é necessária a auditoria.
Valor padrão é '' que vai fazer auditoria para todos os usuários.

Uso

Para gerar os scripts sozinho

EXEC GenerateTriggers @Schemaname = 'dbo', @ Tablename = 'TableName', @GenerateScriptOnly = 1

Para criar os objetos no banco de dados

EXEC GenerateTriggers @Schemaname = 'dbo', @ Tablename = 'TableName', @GenerateScriptOnly = 0

Para forçar a queda e recriar a tabela de auditoria

EXEC GenerateTriggers @Schemaname = 'dbo', @ Tablename = 'TableName', @GenerateScriptOnly = 0, @ ForceDropAuditTable = 1

Para ignorar e criar gatilhos ou gerar roteiro

EXEC GenerateTriggers @Schemaname = 'dbo', @ Tablename = 'TableName', @GenerateScriptOnly = 0, @ IgnoreExistingColumnMismatch = 1

Para evitar alguns usuários não devem ser auditados

EXEC GenerateTriggers @Schemaname = 'dbo', @ Tablename = 'TableName', @GenerateScriptOnly = 0, @ DontAuditforUsers = 'NT \ UserName1, NT \ UserName2'

Histórico de Revisão

22-Feb-2016 -> 1) Foi adicionada uma nova coluna na tabela de auditoria para obter as colunas atualizados.

2) Fixe para emissão coluna timestamp.

16-Aug-2016 -> modificado para gerar scripts ALTER TABLE em vez de droping e criar a tabela de auditoria

26-Sep-2016 -> Corrigido problemas com a geração do script com outro esquema de dbo. Para gota força tabela de auditoria acrescentou tabela de queda.

27-Sep-2016 -> Corrigido problemas com geração de script se ele tem uma coluna de identidade.

22-Dez-2016 -> Adicionado um novo parâmetro para excluir os usuários a serem auditadas. ou seja, uma linha não será inserido para aqueles usuários passaram em @DontAuditforUsers


--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/
Message modifié, 22 février 2017 - 19:58
Membre enregistré
3 651 messages
Popularité : +175 (223 votes)
Posté le 22 février 2017 - 19:59
--Auditoria MS SQL Server
IF OBJECT_ID('GenerateTriggers','P') IS NOT NULL
DROP PROC GenerateTriggers
GO

CREATE PROC GenerateTriggers
@Schemaname Sysname = 'dbo'
,@Tablename Sysname
,@GenerateScriptOnly bit = 1
,@ForceDropAuditTable bit = 0
,@IgnoreExistingColumnMismatch bit = 0
,@DontAuditforUsers NVARCHAR(4000) = ''
AS

SET NOCOUNT ON

/*
Parameters
@Schemaname - SchemaName to which the table belongs to. Default value 'dbo'.
@Tablename - TableName for which the procs needs to be generated.
@GenerateScriptOnly - When passed 1 , this will generate the scripts alone..
When passed 0 , this will create the audit tables and triggers in the current database.
Default value is 1
@ForceDropAuditTable - When passed 1 , will drop the audit table and recreate
When passed 0 , will generate the alter scripts
Default value is 0
@IgnoreExistingColumnMismatch - When passed 1 , will not stop with the error on the mismatch of existing column and will create the trigger.
When passed 0 , will stop with the error on the mismatch of existing column.
Default value is 0
@DontAuditforUsers - Pass the UserName as comma seperated for whom the audit is not required.
Default value is '' which will do audit for all the users.
*/

DECLARE @SQL VARCHAR(MAX)
DECLARE @SQLTrigger VARCHAR(MAX)
DECLARE @ErrMsg VARCHAR(MAX)


DECLARE @AuditTableName SYSNAME
DECLARE @QuotedSchemaName SYSNAME
DECLARE @QuotedTableName SYSNAME
DECLARE @QuotedAuditTableName SYSNAME
DECLARE @InsertTriggerName SYSNAME
DECLARE @UpdateTriggerName SYSNAME
DECLARE @DeleteTriggerName SYSNAME
DECLARE @QuotedInsertTriggerName SYSNAME
DECLARE @QuotedUpdateTriggerName SYSNAME
DECLARE @QuotedDeleteTriggerName SYSNAME
DECLARE @DontAuditforUsersTmp NVARCHAR(4000)

SELECT @AuditTableName = @Tablename + '_Audit'
SELECT @QuotedSchemaName = QUOTENAME(@Schemaname)
SELECT @QuotedTableName = QUOTENAME(@Tablename)
SELECT @QuotedAuditTableName = QUOTENAME(@AuditTableName)
SELECT @InsertTriggerName = @Tablename + '_Insert'
SELECT @UpdateTriggerName = @Tablename + '_Update'
SELECT @DeleteTriggerName = @Tablename + '_Delete'
SELECT @QuotedInsertTriggerName = QUOTENAME(@InsertTriggerName)
SELECT @QuotedUpdateTriggerName = QUOTENAME(@UpdateTriggerName)
SELECT @QuotedDeleteTriggerName = QUOTENAME(@DeleteTriggerName)

IF LTRIM(RTRIM(@DontAuditforUsers)) <> ''
BEGIN

IF RIGHT(@DontAuditforUsers,1) = ','
BEGIN
SELECT @DontAuditforUsersTmp = LEFT(@DontAuditforUsers,LEN(@DontAuditforUsers) -1)
END
ELSE
BEGIN
SELECT @DontAuditforUsersTmp = @DontAuditforUsers
END

SELECT @DontAuditforUsersTmp = REPLACE(@DontAuditforUsersTmp,',',''',''')

END

IF NOT EXISTS (SELECT 1
FROM sys.objects
WHERE Name= @TableName
AND Schema_id=Schema_id(@Schemaname)
AND Type = 'U')
BEGIN
SELECT @ErrMsg = @QuotedSchemaName + '.' + @QuotedTableName + ' Table Not Found '
RAISERROR(@ErrMsg ,16,1)
RETURN
END


----------------------------------------------------------------------------------------------------------------------
-- Audit Create OR Alter table
----------------------------------------------------------------------------------------------------------------------

DECLARE @ColList VARCHAR(MAX)
DECLARE @InsertColList VARCHAR(MAX)
DECLARE @UpdateCheck VARCHAR(MAX)

DECLARE @NewAddedCols TABLE
(
ColumnName SYSNAME
,DataType SYSNAME
,CharLength INT
,Collation SYSNAME NULL
,ChangeType VARCHAR(20) NULL
,MainTableColumnName SYSNAME NULL
,MainTableDataType SYSNAME NULL
,MainTableCharLength INT NULL
,MainTableCollation SYSNAME NULL
,AuditTableColumnName SYSNAME NULL
,AuditTableDataType SYSNAME NULL
,AuditTableCharLength INT NULL
,AuditTableCollation SYSNAME NULL
)


SELECT @ColList = ''
SELECT @UpdateCheck = ' '
SELECT @SQL = ''
SELECT @InsertColList = ''

SELECT @ColList = @ColList + CASE SC.is_identity
WHEN 1 THEN 'CONVERT(' + ST.name + ',' + QUOTENAME(SC.name) + ') as ' + QUOTENAME(SC.name)
ELSE QUOTENAME(SC.name)
END + ','
, @InsertColList = @InsertColList + QUOTENAME(SC.name) + ','
, @UpdateCheck = @UpdateCheck + 'CASE WHEN UPDATE(' + QUOTENAME(SC.name) + ') THEN ''' + QUOTENAME(SC.name) + '-'' ELSE '''' END + ' + CHAR(10)
FROM SYS.COLUMNS SC
JOIN SYS.OBJECTS SO
ON SC.object_id = SO.object_id
JOIN SYS.schemas SCH
ON SCH.schema_id = SO.schema_id
JOIN SYS.types ST
ON ST.user_type_id = SC.user_type_id
AND ST.system_type_id = SC.system_type_id
WHERE SCH.Name = @Schemaname
AND SO.name = @Tablename
AND UPPER(ST.name) <> UPPER('timestamp')

SELECT @ColList = SUBSTRING(@ColList,1,LEN(@ColList)-1)
SELECT @UpdateCheck = SUBSTRING(@UpdateCheck,1,LEN(@UpdateCheck)-3)
SELECT @InsertColList = SUBSTRING(@InsertColList,1,LEN(@InsertColList)-1)

SELECT @InsertColList = @InsertColList + ',AuditDataState,AuditDMLAction,AuditUser,AuditDateTime,UpdateColumns'

IF EXISTS (SELECT 1
FROM sys.objects
WHERE Name= @AuditTableName
AND Schema_id=Schema_id(@Schemaname)
AND Type = 'U') AND @ForceDropAuditTable = 0
BEGIN


--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/
Membre enregistré
3 651 messages
Popularité : +175 (223 votes)
Posté le 22 février 2017 - 20:00
----------------------------------------------------------------------------------------------------------------------
-- Get the comparision metadata for Main and Audit Tables
----------------------------------------------------------------------------------------------------------------------

INSERT INTO @NewAddedCols
(ColumnName,DataType,CharLength,Collation,ChangeType,MainTableColumnName
,MainTableDataType,MainTableCharLength,MainTableCollation,AuditTableColumnName,AuditTableDataType,AuditTableCharLength,AuditTableCollation)
SELECT ISNULL(MainTable.ColumnName,AuditTable.ColumnName)
,ISNULL(MainTable.DataType,AuditTable.DataType)
,ISNULL(MainTable.CharLength,AuditTable.CharLength)
,ISNULL(MainTable.Collation,AuditTable.Collation)
,CASE
WHEN MainTable.ColumnName IS NULL THEN 'Deleted'
WHEN AuditTable.ColumnName IS NULL THEN 'Added'
ELSE NULL
END
,MainTable.ColumnName
,MainTable.DataType
,MainTable.CharLength
,MainTable.Collation
,AuditTable.ColumnName
,AuditTable.DataType
,AuditTable.CharLength
,AuditTable.Collation
FROM

(
SELECT SC.Name As ColumnName,ST.Name as DataType,SC.is_identity as isIdentity,SC.Max_length as CharLength,SC.Collation_Name as Collation
FROM SYS.COLUMNS SC
JOIN SYS.OBJECTS SO
ON SC.object_id = SO.object_id
JOIN SYS.schemas SCH
ON SCH.schema_id = SO.schema_id
JOIN SYS.types ST
ON ST.user_type_id = SC.user_type_id
AND ST.system_type_id = SC.system_type_id
WHERE SCH.Name = @Schemaname
AND SO.name = @Tablename
AND UPPER(ST.name) <> UPPER('timestamp')
) MainTable
FULL OUTER JOIN
(
SELECT SC.Name As ColumnName,ST.Name as DataType,SC.is_identity as isIdentity,SC.Max_length as CharLength,SC.Collation_Name as Collation
FROM SYS.COLUMNS SC
JOIN SYS.OBJECTS SO
ON SC.object_id = SO.object_id
JOIN SYS.schemas SCH
ON SCH.schema_id = SO.schema_id
JOIN SYS.types ST
ON ST.user_type_id = SC.user_type_id
AND ST.system_type_id = SC.system_type_id
WHERE SCH.Name = @Schemaname
AND SO.name = @AuditTableName
AND UPPER(ST.name) <> UPPER('timestamp')
AND SC.Name NOT IN ('AuditDataState','AuditDMLAction','AuditUser','AuditDateTime','UpdateColumns')
) AuditTable
ON MainTable.ColumnName = AuditTable.ColumnName

----------------------------------------------------------------------------------------------------------------------
-- Find data type changes between table
----------------------------------------------------------------------------------------------------------------------

IF EXISTS ( SELECT * FROM @NewAddedCols NC
WHERE NC.MainTableColumnName = NC.AuditTableColumnName
AND (
NC.MainTableDataType <> NC.AuditTableDataType
OR
NC.MainTableCharLength > NC.AuditTableCharLength
OR
NC.MainTableCollation <> NC.AuditTableCollation
)
)
BEGIN
SELECT CONVERT(VARCHAR(50),
CASE
WHEN NC.MainTableDataType <> NC.AuditTableDataType THEN 'DataType Mismatch'
WHEN NC.MainTableCharLength > NC.AuditTableCharLength THEN 'Length in maintable is greater than Audit Table'
WHEN NC.MainTableCollation <> NC.AuditTableCollation THEN 'Collation Difference'
END) AS Mismatch
,NC.MainTableColumnName
,NC.MainTableDataType
,NC.MainTableCharLength
,NC.MainTableCollation
,NC.AuditTableColumnName
,NC.AuditTableDataType
,NC.AuditTableCharLength
,NC.AuditTableCollation
FROM @NewAddedCols NC
WHERE NC.MainTableColumnName = NC.AuditTableColumnName
AND (
NC.MainTableDataType <> NC.AuditTableDataType
OR
NC.MainTableCharLength > NC.AuditTableCharLength
OR
NC.MainTableCollation <> NC.AuditTableCollation
)

RAISERROR('There are differences in Datatype or Lesser Length or Collation difference between the Main table and Audit Table. Please refer the output',16,1)
IF @IgnoreExistingColumnMismatch = 0
BEGIN
RETURN
END
END

----------------------------------------------------------------------------------------------------------------------
-- Find the new and deleted columns
----------------------------------------------------------------------------------------------------------------------

IF EXISTS(SELECT * FROM @NewAddedCols WHERE ChangeType IS NOT NULL)
BEGIN

SELECT @SQL = @SQL + 'ALTER TABLE ' + @QuotedSchemaName + '.' + @QuotedAuditTableName
+ CASE
WHEN NC.ChangeType ='Added' THEN ' ADD ' + QUOTENAME(NC.ColumnName) + ' ' + NC.DataType + ' '
+ CASE
WHEN NC.DataType IN ('char','varchar','nchar','nvarchar') AND NC.CharLength = -1 THEN '(max) COLLATE ' + NC.Collation + ' NULL '
WHEN NC.DataType IN ('char','varchar') THEN '(' + CONVERT(VARCHAR(5),NC.CharLength) + ') COLLATE ' + NC.Collation + ' NULL '
WHEN NC.DataType IN ('nchar','nvarchar') THEN '(' + CONVERT(VARCHAR(5),NC.CharLength/2) + ') COLLATE ' + NC.Collation + ' NULL '
ELSE ''
END
WHEN NC.ChangeType ='Deleted' THEN ' DROP COLUMN ' + QUOTENAME(NC.ColumnName)
END + CHAR(10)

FROM @NewAddedCols NC
WHERE NC.ChangeType IS NOT NULL
END


END
ELSE
BEGIN

SELECT @SQL = ' IF EXISTS (SELECT 1
FROM sys.objects
WHERE Name=''' + @AuditTableName + '''
AND Schema_id=Schema_id(''' + @Schemaname + ''')
AND Type = ''U'')
DROP TABLE ' + @QuotedSchemaName + '.' + @QuotedAuditTableName + '

SELECT ' + @ColList + '
,AuditDataState=CONVERT(VARCHAR(10),'''')
,AuditDMLAction=CONVERT(VARCHAR(10),'''')
,AuditUser =CONVERT(SYSNAME,'''')
,AuditDateTime=CONVERT(DATETIME,''01-JAN-1900'')
,UpdateColumns = CONVERT(VARCHAR(MAX),'''')
Into ' + @QuotedSchemaName + '.' + @QuotedAuditTableName + '
FROM ' + @QuotedSchemaName + '.' + @QuotedTableName +'
WHERE 1=2 '
END



IF @GenerateScriptOnly = 1
BEGIN
PRINT REPLICATE ('-',200)
PRINT '--Create \ Alter Script Audit table for ' + @QuotedSchemaName + '.' + @QuotedTableName
PRINT REPLICATE ('-',200)
PRINT @SQL
IF LTRIM(RTRIM(@SQL)) <> ''
BEGIN
PRINT 'GO'
END
ELSE
BEGIN
PRINT '-- No changes in table structure'
END
END
ELSE
BEGIN
IF RTRIM(LTRIM(@SQL)) = ''
BEGIN
PRINT 'No Table Changes Found'
END
ELSE
BEGIN
PRINT 'Creating \ Altered Audit table for ' + @QuotedSchemaName + '.' + @QuotedTableName
EXEC(@SQL)
PRINT 'Audit table ' + @QuotedSchemaName + '.' + @QuotedAuditTableName + ' Created \ Altered succesfully'
END
END


--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/
Membre enregistré
3 651 messages
Popularité : +175 (223 votes)
Posté le 22 février 2017 - 20:00
----------------------------------------------------------------------------------------------------------------------
-- Create Insert Trigger
----------------------------------------------------------------------------------------------------------------------


SELECT @SQL = '
IF EXISTS (SELECT 1
FROM sys.objects
WHERE Name=''' + @Tablename + '_Insert' + '''
AND Schema_id=Schema_id(''' + @Schemaname + ''')
AND Type = ''TR'')
DROP TRIGGER ' + @QuotedSchemaName + '.' + @QuotedInsertTriggerName
SELECT @SQLTrigger = '
CREATE TRIGGER ' + @QuotedSchemaName + '.' + @QuotedInsertTriggerName + '
ON ' + @QuotedSchemaName + '.' + @QuotedTableName + '
FOR INSERT
AS
'


IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN

SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' IF SUSER_NAME() NOT IN (''' + @DontAuditforUsersTmp + ''')'
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' BEGIN'

END




SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' INSERT INTO ' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CHAR(10) +
'(' + @InsertColList + ')' + CHAR(10) +
'SELECT ' + @ColList + ',''New'',''Insert'',SUSER_SNAME(),getdate(),'''' FROM INSERTED '

IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN

SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' END'

END


IF @GenerateScriptOnly = 1
BEGIN
PRINT REPLICATE ('-',200)
PRINT '--Create Script Insert Trigger for ' + @QuotedSchemaName + '.' + @QuotedTablename
PRINT REPLICATE ('-',200)
PRINT @SQL
PRINT 'GO'
PRINT @SQLTrigger
PRINT 'GO'
END
ELSE
BEGIN
PRINT 'Creating Insert Trigger ' + @QuotedInsertTriggerName + ' for ' + @QuotedSchemaName + '.' + @QuotedTablename
EXEC(@SQL)
EXEC(@SQLTrigger)
PRINT 'Trigger ' + @QuotedSchemaName + '.' + @QuotedInsertTriggerName + ' Created succesfully'
END


----------------------------------------------------------------------------------------------------------------------
-- Create Delete Trigger
----------------------------------------------------------------------------------------------------------------------


SELECT @SQL = '

IF EXISTS (SELECT 1
FROM sys.objects
WHERE Name=''' + @Tablename + '_Delete' + '''
AND Schema_id=Schema_id(''' + @Schemaname + ''')
AND Type = ''TR'')
DROP TRIGGER ' + @QuotedSchemaName + '.' + + @QuotedDeleteTriggerName + '
'

SELECT @SQLTrigger =
'
CREATE TRIGGER ' + @QuotedSchemaName + '.' + @QuotedDeleteTriggerName + '
ON '+ @QuotedSchemaName + '.' + @QuotedTableName + '
FOR DELETE
AS '

IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN

SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' IF SUSER_NAME() NOT IN (''' + @DontAuditforUsersTmp + ''')'
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' BEGIN'

END



SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' INSERT INTO ' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CHAR(10) +
'(' + @InsertColList + ')' + CHAR(10) +
'SELECT ' + @ColList + ',''Old'',''Delete'',SUSER_SNAME(),getdate(),'''' FROM DELETED'

IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN

SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' END'

END


IF @GenerateScriptOnly = 1
BEGIN
PRINT REPLICATE ('-',200)
PRINT '--Create Script Delete Trigger for ' + @QuotedSchemaName + '.' + @QuotedTableName
PRINT REPLICATE ('-',200)
PRINT @SQL
PRINT 'GO'
PRINT @SQLTrigger
PRINT 'GO'
END
ELSE
BEGIN
PRINT 'Creating Delete Trigger ' + @QuotedDeleteTriggerName + ' for ' + @QuotedSchemaName + '.' + @QuotedTableName
EXEC(@SQL)
EXEC(@SQLTrigger)
PRINT 'Trigger ' + @QuotedSchemaName + '.' + @QuotedDeleteTriggerName + ' Created succesfully'
END

----------------------------------------------------------------------------------------------------------------------
-- Create Update Trigger
----------------------------------------------------------------------------------------------------------------------


SELECT @SQL = '

IF EXISTS (SELECT 1
FROM sys.objects
WHERE Name=''' + @Tablename + '_Update' + '''
AND Schema_id=Schema_id(''' + @Schemaname + ''')
AND Type = ''TR'')
DROP TRIGGER ' + @QuotedSchemaName + '.' + @QuotedUpdateTriggerName + '
'

SELECT @SQLTrigger =
'
CREATE TRIGGER ' + @QuotedSchemaName + '.' + @QuotedUpdateTriggerName + '
ON '+ @QuotedSchemaName + '.' + @QuotedTableName + '
FOR UPDATE
AS '

IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN

SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' IF SUSER_NAME() NOT IN (''' + @DontAuditforUsersTmp + ''')'
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' BEGIN'

END



SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + '

INSERT INTO ' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CHAR(10) +
'(' + @InsertColList + ')' + CHAR(10) +
'SELECT ' + @ColList +',''New'',''Update'',SUSER_SNAME(),getdate(),' + @UpdateCheck + ' FROM INSERTED

INSERT INTO ' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CHAR(10) +
'(' + @InsertColList + ')' + CHAR(10) +
'SELECT ' + @ColList +',''Old'',''Update'',SUSER_SNAME(),getdate(),' + @UpdateCheck + ' FROM DELETED '

IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN

SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' END'

END



IF @GenerateScriptOnly = 1
BEGIN
PRINT REPLICATE ('-',200)
PRINT '--Create Script Update Trigger for ' + @QuotedSchemaName + '.' + @QuotedTableName
PRINT REPLICATE ('-',200)
PRINT @SQL
PRINT 'GO'
PRINT @SQLTrigger
PRINT 'GO'
END
ELSE
BEGIN
PRINT 'Creating Delete Trigger ' + @QuotedUpdateTriggerName + ' for ' + @QuotedSchemaName + '.' + @QuotedTableName
EXEC(@SQL)
EXEC(@SQLTrigger)
PRINT 'Trigger ' + @QuotedSchemaName + '.' + @QuotedUpdateTriggerName + ' Created succesfully'
END

SET NOCOUNT OFF

go


--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/
Membre enregistré
3 651 messages
Popularité : +175 (223 votes)
Posté le 22 février 2017 - 20:02
Este script foi testado nessas plataformas pelo autor. É muito provável que funcione nessas plataformas também. Se você tentar e ver que funciona em uma outra plataforma, adicione uma nota no script de discussão para que os demais saibam.

Suporte de colegas online

Para obter suporte de colegas online, ingresse no The Official Scripting Guys Forum! Para fornecer comentários ou relatar bugs em scripts de exemplo, inicie uma nova discussão na guia Discussões deste script.

Aviso de isenção de responsabilidade

Não existe suporte para os script de exemplo em nenhum programa ou serviço de suporte padrão da Microsoft.

Os scripts de exemplo são fornecidos COMO ESTÃO, sem garantias de qualquer tipo. A Microsoft também se isenta de todas as garantias implícitas, incluindo, sem limitação, quaisquer garantias implícitas de comercialização ou de adequação a uma finalidade específica. Todos os riscos resultantes do uso ou do desempenho dos scripts de exemplo e da documentação são de responsabilidade do usuário.

Em nenhuma circunstância, a Microsoft, seus autores ou qualquer outra pessoa envolvida na criação, produção ou entrega dos scripts será responsável por quaisquer danos (incluindo, mas sem se limitar a, danos ou perdas de lucros comerciais, interrupção dos negócios, perda de informações ou outras perdas financeiras) resultantes do uso dos scripts de exemplo ou da documentação, mesmo que a Microsoft tenha sido alertada da possibilidade de tais danos.

FONTE: https://gallery.technet.microsoft.com/scriptcenter/Create-Audit-Table-and-5cd69d5d

--
Adriano José Boller
______________________________________________
Consultor e Representante Oficial da
PcSoft no Brasil
+55 (41) 99949 1800
adrianoboller@gmail.com
skype: adrianoboller
http://wxinformatica.com.br/
Message modifié, 22 février 2017 - 20:03