EXEMPLO_1212310 Database

Parameters

Name	Type	Mode

Definition

CREATE PROCEDURE p_ABATFUNLOGNEW
/*Version=07.08.19*/
AS
DECLARE @SQLCommand NVARCHAR(MAX)
SET @SQLCommand = 'IF EXISTS (SELECT 1 FROM sys.triggers WHERE NAME = ''TR_INS_ABATFUNLOG'')' + CHAR(10)
SET @SQLCommand += 'DROP TRIGGER TR_INS_ABATFUNLOG'
EXEC  SP_EXECUTESQL @SQLCommand
SET @SQLCommand =  'CREATE TRIGGER TR_INS_ABATFUNLOG ON ABATFUN' + CHAR(10)
SET @SQLCommand += 'FOR INSERT' + CHAR(10)
SET @SQLCommand += 'AS' + CHAR(10)
SET @SQLCommand += 'declare @usrlog varchar(20)' + CHAR(10)
SET @SQLCommand += 'declare @processolog varchar(100)' + CHAR(10)
SET @SQLCommand += 'select @usrlog = NETUSER, @processolog = PROCESSNAME from fn_GetContext()' + CHAR(10) + CHAR(10)
SET @SQLCommand += 'INSERT INTO ABATFUNLOG (OPERACAO, DATA_LOG, USUARIO, USUARIODB, PROCESSOLOG, CODCOLIGADA, CHAPA, DATA, BATIDA, STATUS)' + CHAR(10)
SET @SQLCommand += 'SELECT ''I'',GETDATE(),@usrlog, SYSTEM_USER,@processolog,I.CODCOLIGADA,I.CHAPA,I.DATA,I.BATIDA,I.STATUS' + CHAR(10)
SET @SQLCommand += 'FROM INSERTED I'
EXEC  SP_EXECUTESQL @SQLCommand
SET @SQLCommand =  'IF EXISTS (SELECT 1 FROM sys.triggers WHERE NAME = ''TR_UPD_ABATFUNLOG'')' + CHAR(10)
SET @SQLCommand += 'DROP TRIGGER TR_UPD_ABATFUNLOG'
EXEC  SP_EXECUTESQL @SQLCommand
SET @SQLCommand =  'CREATE TRIGGER TR_UPD_ABATFUNLOG ON ABATFUN' + CHAR(10)
SET @SQLCommand += 'FOR UPDATE' + CHAR(10)
SET @SQLCommand += 'AS' + CHAR(10)
SET @SQLCommand += 'DECLARE @USRLOG VARCHAR(20)' + CHAR(10)
SET @SQLCommand += 'DECLARE @PROCESSOLOG VARCHAR(100)' + CHAR(10)
SET @SQLCommand += 'select @usrlog = NETUSER, @processolog = PROCESSNAME from fn_GetContext()' + CHAR(10) + CHAR(10)
SET @SQLCommand += 'INSERT INTO ABATFUNLOG (OPERACAO, DATA_LOG, USUARIO, USUARIODB, PROCESSOLOG, CODCOLIGADA, CHAPA, DATA, BATIDA, STATUS)' + CHAR(10)
SET @SQLCommand += 'SELECT ''U'',GETDATE(),@USRLOG, CAST(SYSTEM_USER AS VARCHAR(20)),@PROCESSOLOG,I.CODCOLIGADA,I.CHAPA,I.DATA,I.BATIDA,I.STATUS' + CHAR(10)
SET @SQLCommand += 'FROM (SELECT ROW_NUMBER() OVER(ORDER BY CODCOLIGADA) AS ID, * FROM INSERTED) I' + CHAR(10)
SET @SQLCommand += 'JOIN (SELECT ROW_NUMBER() OVER(ORDER BY CODCOLIGADA) AS ID, * FROM DELETED) D ON' + CHAR(10)
SET @SQLCommand += '    I.ID = D.ID' + CHAR(10)
SET @SQLCommand += 'WHERE (I.CODCOLIGADA <> D.CODCOLIGADA) OR (I.CHAPA <> D.CHAPA) OR (I.DATA <> D.DATA) OR' + CHAR(10)
SET @SQLCommand += '(I.BATIDA <> D.BATIDA) OR (I.STATUS <> D.STATUS) OR (I.IDAAFDT <> D.IDAAFDT)'
EXEC  SP_EXECUTESQL @SQLCommand
SET @SQLCommand =  'IF EXISTS (SELECT 1 FROM sys.triggers WHERE NAME = ''TR_DEL_ABATFUNLOG'')' + CHAR(10)
SET @SQLCommand += 'DROP TRIGGER TR_DEL_ABATFUNLOG'
EXEC  SP_EXECUTESQL @SQLCommand
SET @SQLCommand =   'CREATE TRIGGER TR_DEL_ABATFUNLOG ON ABATFUN' + CHAR(10)
SET @SQLCommand +=  'FOR DELETE' + CHAR(10)
SET @SQLCommand +=  'AS' + CHAR(10)
SET @SQLCommand +=  'declare @usrlog varchar(20)' + CHAR(10)
SET @SQLCommand +=  'declare @processolog varchar(100)' + CHAR(10)
SET @SQLCommand +=  'select @usrlog = NETUSER, @processolog = PROCESSNAME from fn_GetContext()' + CHAR(10) + CHAR(10)
SET @SQLCommand +=  'INSERT INTO ABATFUNLOG (OPERACAO, DATA_LOG, USUARIO, USUARIODB, PROCESSOLOG, CODCOLIGADA, CHAPA, DATA, BATIDA, STATUS)' + CHAR(10)
SET @SQLCommand +=  'SELECT ''D'',GETDATE(),@usrlog, SYSTEM_USER,@processolog,D.CODCOLIGADA,D.CHAPA,D.DATA,D.BATIDA,D.STATUS' + CHAR(10)
SET @SQLCommand +=  'FROM DELETED D'
EXEC  SP_EXECUTESQL @SQLCommand