ALTER TABLE ITEMSOLI
   DROP CONSTRAINT FKCMP00255ITEMSOLI;
GO

	DECLARE	@V_CDFILSOLI_OLD		VARCHAR(4);
	DECLARE	@V_CDFILLAN_OLD			VARCHAR(4);
	DECLARE	@NRSOLICMP_OLD	 		VARCHAR(10);
	DECLARE	@V_CDPRODUTO_OLD 	VARCHAR(10);
	DECLARE	@V_DTPROGENTR_OLD	DATETIME;
BEGIN
  	DECLARE C_ITEMSOLI CURSOR FOR
  		  SELECT CDFILSOLI, CDFILLAN, NRSOLICMP,
             CDPRODUTO, DTPROGENTR
  		  	 FROM ITEMSOLI
       WHERE (CDFILLANCONS IS NOT NULL
          OR CDFILSOLCONS IS NOT NULL
          OR NRSOLCMPCONS IS NOT NULL
          OR CDPRODUTCONS IS NOT NULL
          OR DTPROGENCONS IS NOT NULL)
         AND NOT EXISTS(SELECT 1
                          FROM ITEMSOLI I
                         WHERE I.CDFILLAN   = ITEMSOLI.CDFILLANCONS
                           AND I.CDFILSOLI  = ITEMSOLI.CDFILSOLCONS
                           AND I.NRSOLICMP  = ITEMSOLI.NRSOLCMPCONS
                           AND I.CDPRODUTO  = ITEMSOLI.CDPRODUTCONS
                           AND I.DTPROGENTR = ITEMSOLI.DTPROGENCONS);
  	OPEN C_ITEMSOLI;
  	FETCH NEXT FROM C_ITEMSOLI INTO
  		   @V_CDFILSOLI_OLD, @V_CDFILLAN_OLD, @NRSOLICMP_OLD,
	      @V_CDPRODUTO_OLD, @V_DTPROGENTR_OLD;
  	WHILE (@@FETCH_STATUS = 0) BEGIN
      UPDATE ITEMSOLI
         SET CDFILLANCONS = NULL, CDFILSOLCONS = NULL, NRSOLCMPCONS = NULL, CDPRODUTCONS = NULL, DTPROGENCONS = NULL
       WHERE CDFILSOLI  = @V_CDFILSOLI_OLD
         AND CDFILLAN   = @V_CDFILLAN_OLD
         AND NRSOLICMP  = @NRSOLICMP_OLD
         AND CDPRODUTO  = @V_CDPRODUTO_OLD
         AND DTPROGENTR = @V_DTPROGENTR_OLD;

      FETCH NEXT FROM C_ITEMSOLI INTO
  	   	   @V_CDFILSOLI_OLD, @V_CDFILLAN_OLD, @NRSOLICMP_OLD,
	         @V_CDPRODUTO_OLD, @V_DTPROGENTR_OLD;
  	END;
  	CLOSE C_ITEMSOLI;
  	DEALLOCATE C_ITEMSOLI;
END;
GO

ALTER TABLE ITEMSOLI
   ADD CONSTRAINT FKCMP00255ITEMSOLI
   FOREIGN KEY (CDFILSOLCONS,CDFILLANCONS,NRSOLCMPCONS,CDPRODUTCONS,DTPROGENCONS)
   REFERENCES ITEMSOLI (CDFILSOLI,CDFILLAN,NRSOLICMP,CDPRODUTO,DTPROGENTR);
GO

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

CREATE TRIGGER SET_NULL_ITEMSOLI
ON ITEMSOLI
FOR DELETE
AS
	DECLARE	@V_CDFILSOLI_OLD		VARCHAR(4);
	DECLARE	@V_CDFILLAN_OLD			VARCHAR(4);
	DECLARE	@NRSOLICMP_OLD	 		VARCHAR(10);
	DECLARE	@V_CDPRODUTO_OLD 	VARCHAR(10);
	DECLARE	@V_DTPROGENTR_OLD	DATETIME;

BEGIN

  	DECLARE C_ITEMSOLI CURSOR FOR
  		  SELECT CDFILSOLI, CDFILLAN, NRSOLICMP,
             CDPRODUTO, DTPROGENTR
  		  	 FROM DELETED
       WHERE 0 = (SELECT COUNT(*) FROM INSERTED);
  	OPEN C_ITEMSOLI;
  	FETCH NEXT FROM C_ITEMSOLI INTO
  		   @V_CDFILSOLI_OLD, @V_CDFILLAN_OLD, @NRSOLICMP_OLD,
	      @V_CDPRODUTO_OLD, @V_DTPROGENTR_OLD;
  	WHILE (@@FETCH_STATUS = 0) BEGIN
      UPDATE ITEMSOLI
         SET CDFILLANCONS = NULL, CDFILSOLCONS = NULL, NRSOLCMPCONS = NULL, CDPRODUTCONS = NULL, DTPROGENCONS = NULL
       WHERE CDFILSOLCONS  = @V_CDFILSOLI_OLD
         AND CDFILLANCONS  = @V_CDFILLAN_OLD
         AND NRSOLCMPCONS  = @NRSOLICMP_OLD
         AND CDPRODUTCONS  = @V_CDPRODUTO_OLD
         AND DTPROGENCONS  = @V_DTPROGENTR_OLD;

      FETCH NEXT FROM C_ITEMSOLI INTO
  	   	   @V_CDFILSOLI_OLD, @V_CDFILLAN_OLD, @NRSOLICMP_OLD,
	         @V_CDPRODUTO_OLD, @V_DTPROGENTR_OLD;
  	END;
  	CLOSE C_ITEMSOLI;
  	DEALLOCATE C_ITEMSOLI;
END;
GO

-- VERSAO --
EXEC ATUVEROBJ '0000_CMPTMPO','0.00.000';
GO
