IF EXISTS (SELECT * FROM SYS.TRIGGERS WHERE NAME = 'TRG_EST_PROD_EST_SNGPC_LOTE_CPL_AUDIT')		
BEGIN		
	DROP TRIGGER TRG_EST_PROD_EST_SNGPC_LOTE_CPL_AUDIT		
END		
GO

CREATE TRIGGER [dbo].[TRG_EST_PROD_EST_SNGPC_LOTE_CPL_AUDIT] ON 
[dbo].[EST_PROD_EST_SNGPC_LOTE_CPL]
WITH EXECUTE AS CALLER
FOR INSERT, UPDATE
AS
BEGIN
-----------------------ENVIROMENT DIVISION--------------------------------------
	SET NOCOUNT ON;
	DECLARE @CD_LOTE INT
	DECLARE @CD_EMP INT
	DECLARE @CD_FILIAL INT
	DECLARE @QT_OLD INT
	DECLARE @QT_LOTE INT
	DECLARE @HOST_NAME VARCHAR(60)
	DECLARE @IP_CLIENTE VARCHAR(60)
	DECLARE @PROGRAM_NAME VARCHAR(100) 
      
	SET @PROGRAM_NAME = (SELECT APP_NAME ())
	IF (SELECT HOST_NAME ()) = '' 
		BEGIN 
			SET @IP_CLIENTE =convert (varchar,( SELECT CONNECTIONPROPERTY('client_net_address') AS client_net_address ))
			SET @HOST_NAME=  @IP_CLIENTE ;     
		END     
	ELSE
		BEGIN
			SET @HOST_NAME =(SELECT HOST_NAME ())
		END
	IF @PROGRAM_NAME ='' SET @PROGRAM_NAME ='[NAO INDENTIFICADO]'
      
   
-----------------------PROCEDURE DIVISION-------------------------------------- 
   DECLARE AUDIT_CURSOR CURSOR FAST_FORWARD    
   FOR SELECT 
       I.CD_EMP,
       I.CD_FILIAL,
       I.CD_LOTE,
       I.QT_LOTE,
       D.QT_LOTE AS OLDVALUE 
   FROM
      INSERTED I
   INNER JOIN DELETED D ON
     D.CD_EMP  =I.CD_EMP
     AND D.CD_FILIAL =I.CD_FILIAL
     AND D.CD_LOTE  = I.CD_LOTE
   
   OPEN AUDIT_CURSOR
   FETCH NEXT FROM AUDIT_CURSOR INTO 
       @CD_EMP,
       @CD_FILIAL,
       @CD_LOTE,
       @QT_LOTE,
       @QT_OLD       
   WHILE @@FETCH_STATUS = 0 
   BEGIN 
     INSERT INTO EST_PROD_EST_SNGPC_LOT_CPL_AUDIT(
        CD_EMP,
        CD_FILIAL,
        CD_LOTE,
        QT_OLD,
        QT_NEW,
        DT_MOV,
        HOST_NAME,
        PROGRAM_NAME
        )
        VALUES
        (
        @CD_EMP,
        @CD_FILIAL, 
        @CD_LOTE,
        @QT_OLD,
        @QT_LOTE,
        GETDATE(),
        @HOST_NAME,
        @PROGRAM_NAME
        
        ) 
     FETCH NEXT FROM AUDIT_CURSOR INTO
        @CD_EMP,
         @CD_FILIAL,
         @CD_LOTE,
         @QT_LOTE,
         @QT_OLD
   END
  CLOSE AUDIT_CURSOR
  DEALLOCATE AUDIT_CURSOR
END

GO

ALTER TABLE [dbo].[EST_PROD_EST_SNGPC_LOTE_CPL] ENABLE TRIGGER [TRG_EST_PROD_EST_SNGPC_LOTE_CPL_AUDIT]
GO