IF EXISTS(SELECT *
          FROM SYSOBJECTS O, SYSUSERS U
          WHERE O.NAME = 'USR_ALT_SOLICITA' AND
                TYPE   = 'TR'               AND
                U.UID  = O.UID              AND
                U.NAME = USER)

DROP TRIGGER USR_ALT_SOLICITA
GO


CREATE TRIGGER USR_ALT_SOLICITA
ON ITEMSOLI
FOR UPDATE, INSERT, DELETE
AS
   DECLARE @V_TIPO              VARCHAR(1);
   DECLARE @V_CDFILLAN_NEW      VARCHAR(4);
   DECLARE @V_CDFILSOLI_NEW     VARCHAR(4);
   DECLARE @V_NRSOLICMP_NEW     VARCHAR(10);

   DECLARE @V_CDFILLAN_OLD      VARCHAR(4);
   DECLARE @V_CDFILSOLI_OLD     VARCHAR(4);
   DECLARE @V_NRSOLICMP_OLD     VARCHAR(10);
BEGIN
   SET @V_TIPO = '0';

   DECLARE C_ITEMSOLI CURSOR FOR
      SELECT 'D' COMANDO,
             NULL, NULL, NULL,
             CDFILLAN, CDFILSOLI, NRSOLICMP
        FROM DELETED
       WHERE 0 = (SELECT COUNT(*) FROM INSERTED)
       UNION ALL
      SELECT 'U' COMANDO,
             INSERTED.CDFILLAN, INSERTED.CDFILSOLI, INSERTED.NRSOLICMP,
             DELETED.CDFILLAN, DELETED.CDFILSOLI, DELETED.NRSOLICMP
        FROM INSERTED, DELETED
       WHERE INSERTED.CDFILLAN   = DELETED.CDFILLAN
         AND INSERTED.CDFILSOLI  = DELETED.CDFILSOLI
         AND INSERTED.NRSOLICMP  = DELETED.NRSOLICMP
         AND INSERTED.CDPRODUTO  = DELETED.CDPRODUTO
         AND INSERTED.DTPROGENTR = DELETED.DTPROGENTR
       UNION ALL
      SELECT 'I' COMANDO,
             CDFILLAN, CDFILSOLI, NRSOLICMP,
             NULL, NULL, NULL
        FROM INSERTED
       WHERE 0 = (SELECT COUNT(*) FROM DELETED)

   OPEN C_ITEMSOLI;
   FETCH NEXT FROM C_ITEMSOLI INTO @V_TIPO, @V_CDFILLAN_NEW, @V_CDFILSOLI_NEW, @V_NRSOLICMP_NEW,
                                   @V_CDFILLAN_OLD, @V_CDFILSOLI_OLD, @V_NRSOLICMP_OLD;

   WHILE (@@FETCH_STATUS = 0) BEGIN

      IF @V_TIPO = 'D' BEGIN
         UPDATE SOLICITA
            SET USR_DTEXPORTA = NULL
          WHERE CDFILLANCA = @V_CDFILLAN_OLD
            AND CDFILSOLI  = @V_CDFILSOLI_OLD
            AND NRSOLICMP  = @V_NRSOLICMP_OLD;
      END;

      IF @V_TIPO = 'U' BEGIN
         UPDATE SOLICITA
            SET USR_DTEXPORTA = NULL
          WHERE CDFILLANCA = @V_CDFILLAN_NEW
            AND CDFILSOLI  = @V_CDFILSOLI_NEW
            AND NRSOLICMP  = @V_NRSOLICMP_NEW;
      END;

      IF @V_TIPO = 'I' BEGIN
         UPDATE SOLICITA
            SET USR_DTEXPORTA = NULL
          WHERE CDFILLANCA = @V_CDFILLAN_NEW
            AND CDFILSOLI  = @V_CDFILSOLI_NEW
            AND NRSOLICMP  = @V_NRSOLICMP_NEW;
      END;


      FETCH NEXT FROM C_ITEMSOLI INTO @V_TIPO, @V_CDFILLAN_NEW, @V_CDFILSOLI_NEW, @V_NRSOLICMP_NEW,
                                      @V_CDFILLAN_OLD, @V_CDFILSOLI_OLD, @V_NRSOLICMP_OLD;
   END;
   CLOSE C_ITEMSOLI;
   DEALLOCATE C_ITEMSOLI;
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'USR_ALT_SOLICITA','5.65.XXX';
GO
