EXEMPLO_1212310 Database

Parameters

Name	Type	Mode
@TableName	nvarchar	IN
@AuditOperation	nvarchar	IN

Definition

CREATE PROCEDURE p_TriggerGenerator(
/*Version=11.11.19*/
@TableName sysname = null,
@AuditOperation nvarchar(3) = NULL) /* I=Insert U=Update D=Delete */
AS
DECLARE
	@CurrentSchema varchar(64) = schema_name(),
	@Owner sysname,
	@TriggerName varchar(128),
	@ParentObjectId INT,
	@ColumnName varchar(128),
	@ColumnType varchar(128),
	@ColumnLength int,
	@ColumnNullable int,
	@ColumnCollation varchar(128),
	@ColumnPrecision tinyint,
	@ColumnScale tinyint,
	@ListOfFields nvarchar(max) = '',
	@ListOfFieldsNotExcept nvarchar(max) = '',
	@ListOfFieldsExcept nvarchar(max) = '',
	@ListOfDataServerExcept nvarchar(max) = '',
	@SQLCommand nvarchar(max),
	@TriggerHeader nvarchar(max),
	@FolhaException VARCHAR(100),
	@SplitException NVARCHAR(MAX);
		SET @SqlCommand = 'SELECT @Owner = AUDITSCHEMANAME FROM ' + @CurrentSchema + '.ZAUDITCONFIG'
		EXECUTE sp_executesql @SqlCommand, N'@Owner sysname out', @Owner out
		SET @SqlCommand = 'SELECT @ParentObjectId = OBJECT_ID FROM SYS.TABLES WHERE NAME = ''' + @TableName + '''' + ' AND SCHEMA_ID = SCHEMA_ID(' + '''' + @CurrentSchema + '''' + ')';
		EXECUTE sp_executesql @SqlCommand, N'@ParentObjectId INT out', @ParentObjectId out
		SET @TriggerName = 'LOG_' + CAST(UPPER(@ParentObjectId) AS VARCHAR);
		SELECT @SplitException = DESCRIPTION FROM ZAUDITEXCEPTION WHERE DOMAIN = @TableName AND TYPE='F' AND STATUS = 1
		/* Declare cursor para colunas da tabela */
		DECLARE TableColumns CURSOR READ_ONLY FOR
			select col.name, typ.name as typename, col.max_length, col.is_nullable, col.collation_name, col.precision, col.scale
			from sys.tables tbl(nolock)
					inner join sys.columns col(nolock)
						inner join sys.types typ(nolock)
							on	col.user_type_id = typ.user_type_id
								and col.name <> 'sysname'
					on tbl.[object_id] = col.[object_id]
			where	OBJECT_NAME(tbl.[object_id]) = @TableName
					and tbl.schema_id = schema_id(@CurrentSchema)
					and col.is_rowguidcol = 0
					and col.is_computed = 0
					and objectproperty(tbl.[object_id], N'IsUserTable') = 1
					and typ.system_type_id not in (select system_type_id from sys.types where system_type_id = user_type_id and name in ('TEXT','NTEXT','IMAGE','TIMESTAMP'))
					and col.name not in ('RECCREATEDBY', 'RECCREATEDON', 'RECMODIFIEDBY', 'RECMODIFIEDON')
			order by col.column_id
		OPEN TableColumns
			FETCH NEXT FROM TableColumns
			INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @ListOfFields += ',[' + @ColumnName + ']'
				IF NOT EXISTS(SELECT VALUE FROM DBO.SPLIT(';',@SplitException) WHERE VALUE = @ColumnName AND VALUE NOT IN (SELECT COLUNA FROM GDIC WHERE Upper(TABELA) = Upper(@TableName) AND COLUNA != '#' AND PESSOAL > 0 OR (TABELA = 'GDIC' AND COLUNA = 'PESSOAL')))
				BEGIN
				IF @ListOfFieldsNotExcept = ''
				SET @ListOfFieldsNotExcept += 'UPDATE([' + @ColumnName + ']) '
				ELSE
				SET @ListOfFieldsNotExcept += 'OR UPDATE([' + @ColumnName + ']) '
				END
			FETCH Next FROM TableColumns
			INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
			END
		CLOSE TableColumns
		DEALLOCATE TableColumns
		/* Declare cursor para execoes de colunas da tabela */
		DECLARE ColumnsExceptions CURSOR READ_ONLY FOR
			SELECT VALUE FROM DBO.SPLIT(';',@SplitException) WHERE VALUE NOT IN (SELECT COLUNA FROM GDIC WHERE Upper(TABELA) = Upper(@TableName) AND COLUNA != '#' AND PESSOAL > 0 OR (TABELA = 'GDIC' AND COLUNA = 'PESSOAL'))
		OPEN ColumnsExceptions
			FETCH NEXT FROM ColumnsExceptions
			INTO @ColumnName
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @ListOfFieldsExcept += ' OR UPDATE([' + @ColumnName + '])'
			FETCH Next FROM ColumnsExceptions
			INTO @ColumnName
			END
		CLOSE ColumnsExceptions
		DEALLOCATE ColumnsExceptions
		/* Declare cursor para execoes de dominios */
		DECLARE ColumnsExceptions CURSOR READ_ONLY FOR
			SELECT DESCRIPTION FROM ZAUDITEXCEPTION WHERE TYPE='A' AND STATUS = 1
		OPEN ColumnsExceptions
			FETCH NEXT FROM ColumnsExceptions
			INTO @ColumnName
			WHILE @@FETCH_STATUS = 0
			BEGIN
					IF @ColumnName = 'RM.FOP.SERVER.FOPCALCULOSERVER'
					SET @FolhaException = '[' + @ColumnName + ']'
					ELSE
					IF @ListOfDataServerExcept = ''
					SET @ListOfDataServerExcept += '''' + '[' + @ColumnName + ']' + ''''
					ELSE
					SET @ListOfDataServerExcept += ',''' + '[' + @ColumnName + ']' + ''''
			FETCH Next FROM ColumnsExceptions
			INTO @ColumnName
			END
		CLOSE ColumnsExceptions
		DEALLOCATE ColumnsExceptions
		IF @ListOfDataServerExcept = ''
		SET @ListOfDataServerExcept = 'NULL'
		/*Create the new trigger*/
		set @TriggerHeader =  'CREATE TRIGGER ' + @TriggerName + '__OPERATION_ ON [' + 'dbo' + '].[' + @TableName + ']' + CHAR(10)
		set @TriggerHeader += 'FOR _OPERATION_ NOT FOR REPLICATION' + CHAR(10)
		set @TriggerHeader += 'AS' + CHAR(10)
		set @TriggerHeader += 'declare @rowsaffected int = @@ROWCOUNT, @eventData xml = eventdata()' + CHAR(10)
		set @TriggerHeader += 'SET CONCAT_NULL_YIELDS_NULL ON' + CHAR(10)
		set @TriggerHeader += 'SET NOCOUNT ON SET ANSI_PADDING ON SET ARITHABORT ON' + CHAR(10)
		set @TriggerHeader += 'declare @scope_log bigint, @logApp varchar(128), @logUser varchar(128), @TableName NVARCHAR(30) = ''' + @TableName + '''' + CHAR(10)
		set @TriggerHeader += 'select @logApp = APPNAME, @logUser = username from fn_GetContext()' + CHAR(10)
		set @TriggerHeader += 'IF NOT EXISTS (SELECT 1 FROM dbo.fn_GetContext() WHERE PROCESSNAME IN (' + @ListOfDataServerExcept + ')'
		/* TRATA EXCEÇÃO DA MAQUINA DE CALCULO */
		IF @FolhaException IS NOT NULL
		set @TriggerHeader += ' OR PROCESSNAME LIKE ''FOPCALCULOSERVER%'')' + CHAR(10)
		ELSE
		set @TriggerHeader += ')' + CHAR(10)
		set @TriggerHeader += 'begin'
		/* Cria as triggers de insert */
		IF CHARINDEX('I', @AuditOperation) <> 0
		BEGIN
			/*Drop the existing trigger*/
			set @SQLCommand =  'IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE name = ''' + @TriggerName + '_INSERT'')' + char(10)
			set @SQLCommand += 'DROP TRIGGER dbo.' + @TriggerName + '_INSERT'
			exec sp_executesql @SQLCommand
			/*Create the new Trigger*/
			set @SQLCommand =  REPLACE(@TriggerHeader,'_OPERATION_','INSERT') + CHAR(10)
			set @SQLCommand += 'INSERT INTO [TotvsAudit].[ZAUDITCHANGES] (AUDITDATE, USERNAME, LOGINNAME, HOSTNAME, APPLICATIONNAME, APPLICATIONMODULE, MACHINEIP, SESSIONID, AUDITACTION, AUDITOBJECTNAME, RECORDSAFFECTED, DMLTEXT)' + CHAR(10)
			set @SQLCommand += 'SELECT getdate(), C.username, c.netuser, C.hostname, app_name(), C.processname, C.ipaddress, C.sessionId, ''I'', @TableName, @rowsaffected, CONVERT(varchar(max), @eventData.query(''data(/EVENT_INSTANCE/TSQLCommand/CommandText)'')) from dbo.fn_GetContext() c' + CHAR(10)
			set @SQLCommand += 'set @scope_log = SCOPE_IDENTITY(); /* Armazena o identity corrente da tabela ZAUDITCHANGES */' + CHAR(10)
			set @SQLCommand += 'INSERT INTO [' + @Owner + '].' + @TableName + ' (AuditId, AuditAction, LogApp, LogUser' +  @ListOfFields + ')' + CHAR(10)
			set @SQLCommand += 'SELECT @scope_log, ''I'', @logApp, @logUser' + @ListOfFields + ' FROM Inserted' + CHAR(10)
			set @SQLCommand += 'END'
			/*PRINT (@SQLCommand)*/
			EXEC (@SQLCommand)
		END
		ELSE
		BEGIN
			set @SQLCommand =  'IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE name = ''' + @TriggerName + '_INSERT'')' + char(10)
			set @SQLCommand += 'DROP TRIGGER dbo.' + @TriggerName + '_INSERT'
			exec (@SQLCommand)
		END
		/* Cria as triggers de update */
		IF CHARINDEX('U', @AuditOperation) <> 0
		BEGIN
			/*Drop the existing trigger*/
			set @SQLCommand =  'IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE name = ''' + @TriggerName + '_UPDATE'')' + char(10)
			set @SQLCommand += 'DROP TRIGGER dbo.' + @TriggerName + '_UPDATE'
			exec sp_executesql @SQLCommand
			/*Create the new trigger*/
			set @SQLCommand =  REPLACE(@TriggerHeader,'_OPERATION_','UPDATE') + CHAR(10)
			set @SQLCommand += 'if not (UPDATE(RECCREATEDBY) OR UPDATE(RECCREATEDON) OR UPDATE(RECMODIFIEDBY) OR UPDATE(RECMODIFIEDON)' + @ListOfFieldsExcept + ')' + CHAR(10)
			set @SQLCommand += 'OR (' + @ListOfFieldsNotExcept + ')' + CHAR(10)
			set @SQLCommand += 'BEGIN' + CHAR(10)
			set @SQLCommand += 'INSERT INTO [TotvsAudit].[ZAUDITCHANGES] (AUDITDATE, USERNAME, LOGINNAME, HOSTNAME, APPLICATIONNAME, APPLICATIONMODULE, MACHINEIP, SESSIONID, AUDITACTION, AUDITOBJECTNAME, RECORDSAFFECTED, DMLTEXT)' + CHAR(10)
			set @SQLCommand += 'SELECT getdate(), C.username, c.netuser, C.hostname, app_name(), C.processname, C.ipaddress, C.sessionId, ''U'', @TableName, @rowsaffected, CONVERT(varchar(max), @eventData.query(''data(/EVENT_INSTANCE/TSQLCommand/CommandText)'')) from dbo.fn_GetContext() c' + CHAR(10)
			set @SQLCommand += 'set @scope_log = SCOPE_IDENTITY(); /* Armazena o identity corrente da tabela ZAUDITCHANGES */' + CHAR(10)
			set @SQLCommand += 'INSERT INTO [' + @Owner + '].' + @TableName + ' (AuditId, AuditAction, LogApp, LogUser' +  @ListOfFields + ')' + CHAR(10)
			set @SQLCommand += 'SELECT @scope_log, ''U'', @logApp, @logUser' + @ListOfFields + ' FROM Inserted' + CHAR(10)
			set @SQLCommand += 'INSERT INTO [' + @Owner + '].' + @TableName + ' (AuditId, ParentLogId, AuditAction, LogApp, LogUser ' +  @ListOfFields + ')' + CHAR(10)
			set @SQLCommand += 'SELECT @scope_log, @@IDENTITY - (@@ROWCOUNT - ROW_NUMBER() OVER (order by @@ROWCOUNT)), ''O'', @logApp, @logUser' + @ListOfFields + ' FROM Deleted' + CHAR(10)
			set @SQLCommand += 'END' + CHAR(10)
			set @SQLCommand += 'END'
			/*PRINT (@SQLCommand)*/
			exec (@SQLCommand)
		END
		ELSE
		BEGIN
			/*Drop the existing trigger*/
			set @SQLCommand =  'IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE name = ''' + @TriggerName + '_UPDATE'')' + char(10)
			set @SQLCommand += 'DROP TRIGGER dbo.' + @TriggerName + '_UPDATE'
			exec sp_executesql @SQLCommand
		END
		/* Cria as triggers de delete */
		IF CHARINDEX('D', @AuditOperation) <> 0
		BEGIN
			/*Drop the existing trigger*/
			set @SQLCommand =  'IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE name = ''' + @TriggerName + '_DELETE'')' + char(10)
			set @SQLCommand += 'DROP TRIGGER dbo.' + @TriggerName + '_DELETE'
			exec sp_executesql @SQLCommand
			/*Create the new trigger*/
			set @SQLCommand =  REPLACE(@TriggerHeader,'_OPERATION_','DELETE') + CHAR(10)
			set @SQLCommand += 'INSERT INTO [TotvsAudit].[ZAUDITCHANGES] (AUDITDATE, USERNAME, LOGINNAME, HOSTNAME, APPLICATIONNAME, APPLICATIONMODULE, MACHINEIP, SESSIONID, AUDITACTION, AUDITOBJECTNAME, RECORDSAFFECTED, DMLTEXT)' + CHAR(10)
			set @SQLCommand += 'SELECT getdate(), C.username, c.netuser, C.hostname, app_name(), C.processname, C.ipaddress, C.sessionid, ''D'', @TableName, @rowsaffected, CONVERT(varchar(max), @eventData.query(''data(/EVENT_INSTANCE/TSQLCommand/CommandText)'')) from dbo.fn_GetContext() c' + CHAR(10)
			set @SQLCommand += 'set @scope_log = SCOPE_IDENTITY(); /* Armazena o identity corrente da tabela ZAUDITCHANGES */' + CHAR(10)
			set @SQLCommand += 'INSERT INTO [' + @Owner + '].' + @TableName + ' (AuditId, AuditAction, LogApp, LogUser' +  @ListOfFields + ')' + CHAR(10)
			set @SQLCommand += 'SELECT @scope_log, ''D'', @logApp, @logUser' + @ListOfFields + ' FROM Deleted' + CHAR(10)
			set @SQLCommand += 'END'
			/*PRINT (@SQLCommand)*/
			EXEC (@SQLCommand)
		END
		ELSE
		BEGIN
			/*Drop the existing trigger*/
			set @SQLCommand =  'IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE name = ''' + @TriggerName + '_DELETE'')' + char(10)
			set @SQLCommand += 'DROP TRIGGER dbo.' + @TriggerName + '_DELETE'
			exec sp_executesql @SQLCommand
		END