IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TRG_EST_PROD_EST_SNGPC_LOTE_CPL_MS]'))
DROP TRIGGER [dbo].[TRG_EST_PROD_EST_SNGPC_LOTE_CPL_MS]
GO

CREATE TRIGGER [dbo].[TRG_EST_PROD_EST_SNGPC_LOTE_CPL_MS] ON [dbo].[EST_PROD_EST_SNGPC_LOTE_CPL]
WITH EXECUTE AS CALLER
FOR INSERT, UPDATE
AS
BEGIN
   DECLARE @CD_PROD INT;
   DECLARE @CD_LOTE INT;
   DECLARE @REGISTRO_MS VARCHAR(20);
   DECLARE @MSGERROR VARCHAR(MAX);
   DECLARE @TESTE_PROD INT;
   DECLARE INSERT_CURSOR CURSOR FAST_FORWARD    
   FOR SELECT 
       P.CD_PROD,
       I.REGISTRO_MS,
       I.CD_LOTE
   FROM
      INSERTED I
    INNER JOIN EST_PROD_EST_SNGPC_LOTE P        
         ON P.CD_LOTE = I.CD_LOTE 
   OPEN INSERT_CURSOR
   FETCH NEXT FROM INSERT_CURSOR INTO 
       @CD_PROD,
       @REGISTRO_MS,
       @CD_LOTE
   WHILE @@FETCH_STATUS = 0 
   BEGIN 
      IF UPDATE(REGISTRO_MS)
      BEGIN
        SET @TESTE_PROD  =(
        SELECT                       
          MAX( A.CD_PROD )                  
         FROM                         
           EST_PROD_EST_SNGPC  A 
           INNER JOIN EST_PROD P        
           ON P.CD_EMP  = A.CD_EMP 
           AND  P.CD_PROD = A.CD_PROD 
         WHERE                    
            A.CD_PROD  <> @CD_PROD
            AND A.REGISTRO_MS   = @REGISTRO_MS
            AND P.CONTROLE_SNGPC  = 1 );
         IF @TESTE_PROD > 0
         BEGIN 
           SET  @MSGERROR = 'O REGISTRO MS INFORMADO[' +CAST( @REGISTRO_MS AS VARCHAR(20)) + ' ] '+
            'PERTENCE AO PRODUTO '+CAST( @CD_PROD AS VARCHAR(30));
           RAISERROR(@MSGERROR , 16, 1)
         END; 
       END;     
     FETCH NEXT FROM INSERT_CURSOR INTO
       @CD_PROD,
       @REGISTRO_MS,
       @CD_LOTE
   END
  CLOSE INSERT_CURSOR
  DEALLOCATE INSERT_CURSOR
END
GO