ALTER TABLE PEDIDOEXOL ADD USR_NRPEDIDO VARCHAR(10)
GO
IF EXISTS (SELECT * FROM SYSOBJECTS O, SYSUSERS U
           WHERE O.NAME = 'SEQUENCIAL_PEDIDO' AND
                 TYPE = 'TR' 		       AND
	              U.UID = O.UID            AND
	              U.NAME = USER)
DROP TRIGGER SEQUENCIAL_PEDIDO
GO
CREATE TRIGGER SEQUENCIAL_PEDIDO
ON PEDIDOEXOL
AFTER INSERT
AS
   DECLARE @V_NEW_V_PRXPEDIDOOL VARCHAR(20);
   DECLARE @V_CDFILDEST VARCHAR(4); 
   DECLARE @V_CDFILLANC VARCHAR(4); 
   DECLARE @V_NRPEDEXOL VARCHAR(20); 
   DECLARE @V_DTPEDEXOL VARCHAR(30);
   DECLARE @V_IDPEDEXOLEXTR VARCHAR(1); 
   DECLARE @V_NRORG VARCHAR(20); 
   DECLARE @V_DTINCLUSAO DATE; 
   DECLARE @V_CDOPERINCLUSAO VARCHAR(20);
   DECLARE @V_IDATIVO VARCHAR(1);
BEGIN
   DECLARE C_CURSOR CURSOR FOR
      SELECT INS.CDFILDEST, INS.CDFILLANC, INS.NRPEDEXOL, CONVERT(VARCHAR, INS.DTPEDEXOL) AS DTPEDEXOL,
             INS.IDPEDEXOLEXTR
      FROM INSERTED INS
      WHERE (CDFILDEST+CDFILLANC+NRPEDEXOL+DTPEDEXOL+IDPEDEXOLEXTR)
        NOT IN (SELECT CDFILDEST+CDFILLANC+NRPEDEXOL+DTPEDEXOL+IDPEDEXOLEXTR 
                  FROM DELETED)
   OPEN C_CURSOR;
   FETCH NEXT FROM C_CURSOR INTO    
         @V_CDFILDEST, @V_CDFILLANC, @V_NRPEDEXOL, @V_DTPEDEXOL,
         @V_IDPEDEXOLEXTR;
   WHILE @@FETCH_STATUS = 0 BEGIN
      EXEC NOVO_CODIGO 'PEDIDOOL', @P_SEQUENCIAL = @V_NEW_V_PRXPEDIDOOL OUTPUT;
      SET @V_NEW_V_PRXPEDIDOOL  = SUBSTRING(@V_NEW_V_PRXPEDIDOOL, 11, 10);
      --SET USR_NRPEDIDO = @V_NEW_V_PRXPEDIDOOL
      UPDATE PEDIDOEXOL
      SET USR_NRPEDIDO = @V_NEW_V_PRXPEDIDOOL
      WHERE 
           (CDFILDEST+CDFILLANC+NRPEDEXOL+CONVERT(VARCHAR, DTPEDEXOL)+IDPEDEXOLEXTR)
         = (@V_CDFILDEST+@V_CDFILLANC+@V_NRPEDEXOL+@V_DTPEDEXOL+@V_IDPEDEXOLEXTR);
      FETCH NEXT FROM C_CURSOR INTO    
            @V_CDFILDEST, @V_CDFILLANC, @V_NRPEDEXOL, @V_DTPEDEXOL,
            @V_IDPEDEXOLEXTR;
   END;
   CLOSE C_CURSOR;
   DEALLOCATE C_CURSOR;
END;
GO
-- VERSAO --
EXEC ATUVEROBJ 'SEQUENCIAL_PEDIDO','5.05.002';
GO

