IF EXISTS(SELECT * FROM sysobjects O, sysusers U
          WHERE
            o.name = 'ALTERA_EFECLIFAT'  and
            type = 'TR'                 and
            u.uid = o.uid               and
            u.name = USER)
  DROP TRIGGER ALTERA_EFECLIFAT
  GO

CREATE TRIGGER ALTERA_EFECLIFAT
ON EFETCLIE
FOR INSERT, UPDATE, DELETE
AS

   DECLARE @V_EXISTE           INTEGER;
   DECLARE @V_IDCOPALTEFEPLA   VARCHAR(01);
   DECLARE @V_NEW_CDFILIAL     VARCHAR(04);
   DECLARE @V_NEW_CDSERVFILI   VARCHAR(05);
   DECLARE @V_NEW_CDCLIENTE    VARCHAR(21);
   DECLARE @V_NEW_DTEFETCLIE   DATETIME;
   DECLARE @V_NEW_QTCOMEFETCLI NUMERIC(6,2);
   DECLARE @V_NEW_CDFILIFATU   VARCHAR(04);
   DECLARE @V_NEW_NRAPURACFAT  VARCHAR(10);
   DECLARE @V_OLD_CDFILIAL     VARCHAR(04);
   DECLARE @V_OLD_CDSERVFILI   VARCHAR(05);
   DECLARE @V_OLD_CDCLIENTE    VARCHAR(21);
   DECLARE @V_OLD_DTEFETCLIE   DATETIME;
   DECLARE @V_OLD_QTCOMEFETCLI NUMERIC(6,2);
   DECLARE @V_OLD_CDFILIFATU   VARCHAR(04);
   DECLARE @V_OLD_NRAPURACFAT  VARCHAR(10);
   DECLARE @V_ID               VARCHAR(01);

BEGIN
   DECLARE C_TRG_EFETCLIE CURSOR FOR
   SELECT INSERTED.CDFILIAL AS NEW_CDFILIAL,
          INSERTED.CDSERVFILI AS NEW_CDSERVFILI,
          INSERTED.CDCLIENTE AS NEW_CDCLIENTE,
          INSERTED.DTEFETCLIE AS NEW_DTEFETCLIE,
          INSERTED.QTCOMEFETCLI AS NEW_QTCOMEFETCLI,
          INSERTED.CDFILIFATU AS NEW_CDFILIFATU,
          INSERTED.NRAPURACFAT AS NEW_NRAPURACFAT,
          NULL AS OLD_CDFILIAL,
          NULL AS OLD_CDSERVFILI,
          NULL AS OLD_CDCLIENTE,
          NULL AS OLD_DTEFETCLIE,
          NULL AS OLD_QTCOMEFETCLI,
          NULL AS OLD_CDFILIFATU,
          NULL AS OLD_NRAPURACFAT,
          'I' AS ID
   FROM INSERTED
   WHERE 0 = (SELECT COUNT(*)
              FROM DELETED)
   UNION
   SELECT NULL AS NEW_CDFILIAL,
          NULL AS NEW_CDSERVFILI,
          NULL AS NEW_CDCLIENTE,
          NULL AS NEW_DTEFETCLIE,
          NULL AS NEW_QTCOMEFETCLI,
          NULL AS NEW_CDFILIFATU,
          NULL AS NEW_NRAPURACFAT,
          DELETED.CDFILIAL AS OLD_CDFILIAL,
          DELETED.CDSERVFILI AS OLD_CDSERVFILI,
          DELETED.CDCLIENTE AS OLD_CDCLIENTE,
          DELETED.DTEFETCLIE AS OLD_DTEFETCLIE,
          DELETED.QTCOMEFETCLI AS OLD_QTCOMEFETCLI,
          DELETED.CDFILIFATU AS OLD_CDFILIFATU,
          DELETED.NRAPURACFAT AS OLD_NRAPURACFAT,
          'D' AS ID
   FROM DELETED
   WHERE 0 = (SELECT COUNT(*)
              FROM DELETED)
   UNION
   SELECT INSERTED.CDFILIAL AS NEW_CDFILIAL,
          INSERTED.CDSERVFILI AS NEW_CDSERVFILI,
          INSERTED.CDCLIENTE AS NEW_CDCLIENTE,
          INSERTED.DTEFETCLIE AS NEW_DTEFETCLIE,
          INSERTED.QTCOMEFETCLI AS NEW_QTCOMEFETCLI,
          INSERTED.CDFILIFATU AS NEW_CDFILIFATU,
          INSERTED.NRAPURACFAT AS NEW_NRAPURACFAT,
          NULL AS OLD_CDFILIAL,
          NULL AS OLD_CDSERVFILI,
          NULL AS OLD_CDCLIENTE,
          NULL AS OLD_DTEFETCLIE,
          NULL AS OLD_QTCOMEFETCLI,
          NULL AS OLD_CDFILIFATU,
          NULL AS OLD_NRAPURACFAT,
          'U' AS ID
     FROM INSERTED, DELETED
   WHERE INSERTED.CDFILIAL = DELETED.CDFILIAL AND
         INSERTED.CDSERVFILI = DELETED.CDSERVFILI AND
         INSERTED.CDCLIENTE  = DELETED.CDCLIENTE AND
         INSERTED.DTEFETCLIE = DELETED.DTEFETCLIE AND
         (ISNULL(INSERTED.QTCOMEFETCLI, 0 ) <> ISNULL(DELETED.QTCOMEFETCLI, 0 ) OR
          ISNULL(INSERTED.CDFILIFATU , '' ) <> ISNULL(DELETED.CDFILIFATU , '' ) OR
          ISNULL(INSERTED.NRAPURACFAT, '') <> ISNULL(DELETED.NRAPURACFAT, ''));

   OPEN C_TRG_EFETCLIE;
   FETCH NEXT FROM C_TRG_EFETCLIE INTO @V_NEW_CDFILIAL,
                                       @V_NEW_CDSERVFILI,
                                       @V_NEW_CDCLIENTE,
                                       @V_NEW_DTEFETCLIE,
                                       @V_NEW_QTCOMEFETCLI,
                                       @V_NEW_CDFILIFATU,
                                       @V_NEW_NRAPURACFAT,
                                       @V_OLD_CDFILIAL,
                                       @V_OLD_CDSERVFILI,
                                       @V_OLD_CDCLIENTE,
                                       @V_OLD_DTEFETCLIE,
                                       @V_OLD_QTCOMEFETCLI,
                                       @V_OLD_CDFILIFATU,
                                       @V_OLD_NRAPURACFAT,
                                       @V_ID;

   WHILE (@@FETCH_STATUS = 0)
   BEGIN
      IF @V_ID = 'D' BEGIN
         SELECT @V_IDCOPALTEFEPLA = ISNULL(MAX(IDCOPALTEFEPLA), 'N')
           FROM PARAMFILIAL
          WHERE PARAMFILIAL.CDFILIAL = @V_OLD_CDFILIAL;
      END
      ELSE BEGIN
         SELECT @V_IDCOPALTEFEPLA = ISNULL(MAX(IDCOPALTEFEPLA), 'N')
           FROM PARAMFILIAL
          WHERE PARAMFILIAL.CDFILIAL = @V_NEW_CDFILIAL;
      END;

      IF (@V_IDCOPALTEFEPLA = 'S') BEGIN
         IF (@V_ID = 'D') BEGIN
            DELETE FROM EFECLIFAT
             WHERE CDFILIAL = @V_OLD_CDFILIAL
               AND CDSERVFILI = @V_OLD_CDSERVFILI
               AND CDCLIENTE = @V_OLD_CDCLIENTE
               AND DTFATURAM = @V_OLD_DTEFETCLIE;
         END
         ELSE BEGIN
            SELECT @V_EXISTE = COUNT(*)
              FROM EFECLIFAT
             WHERE CDFILIAL = @V_NEW_CDFILIAL
               AND CDSERVFILI = @V_NEW_CDSERVFILI
               AND CDCLIENTE = @V_NEW_CDCLIENTE
               AND DTFATURAM = @V_NEW_DTEFETCLIE;

             IF (@V_EXISTE = 0) BEGIN
                INSERT INTO EFECLIFAT
                  (CDFILIAL, CDSERVFILI, CDCLIENTE, DTFATURAM, QTCOMEFATU, CDFILIFATU, NRAPURACFAT)
               VALUES
                  (@V_NEW_CDFILIAL, @V_NEW_CDSERVFILI, @V_NEW_CDCLIENTE, @V_NEW_DTEFETCLIE, @V_NEW_QTCOMEFETCLI, @V_NEW_CDFILIFATU, @V_NEW_NRAPURACFAT);
             END
             ELSE BEGIN
                UPDATE EFECLIFAT
                   SET QTCOMEFATU = @V_NEW_QTCOMEFETCLI,
                       CDFILIFATU = @V_NEW_CDFILIFATU,
                       NRAPURACFAT = @V_NEW_NRAPURACFAT
                 WHERE CDFILIAL = @V_NEW_CDFILIAL
                   AND CDSERVFILI = @V_NEW_CDSERVFILI
                   AND CDCLIENTE = @V_NEW_CDCLIENTE
                   AND DTFATURAM = @V_NEW_DTEFETCLIE;
             END;
         END;
      END;

      FETCH NEXT FROM C_TRG_EFETCLIE INTO @V_NEW_CDFILIAL,
                                          @V_NEW_CDSERVFILI,
                                          @V_NEW_CDCLIENTE,
                                          @V_NEW_DTEFETCLIE,
                                          @V_NEW_QTCOMEFETCLI,
                                          @V_NEW_CDFILIFATU,
                                          @V_NEW_NRAPURACFAT,
                                          @V_OLD_CDFILIAL,
                                          @V_OLD_CDSERVFILI,
                                          @V_OLD_CDCLIENTE,
                                          @V_OLD_DTEFETCLIE,
                                          @V_OLD_QTCOMEFETCLI,
                                          @V_OLD_CDFILIFATU,
                                          @V_OLD_NRAPURACFAT,
                                          @V_ID;
   END;
   CLOSE C_TRG_EFETCLIE;
   DEALLOCATE C_TRG_EFETCLIE;
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'ALTERA_EFECLIFAT','5.05.025';
GO
