IF EXISTS (SELECT * FROM SYS.triggers WHERE NAME = 'TRG_RC_CLI_CPF') 
BEGIN
	DROP TRIGGER TRG_RC_CLI_CPF
END 
GO 

/****** Object:  Trigger [dbo].[TRG_RC_CLI_CPF]    Script Date: 24/08/2017 11:24:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO 

CREATE TRIGGER [dbo].[TRG_RC_CLI_CPF] ON [dbo].[RC_CLI] FOR UPDATE, INSERT AS

declare @nao_permite_cadastro_cpf_zerado tinyint
declare @inserted_count tinyint, 
	   @deleted_count tinyint,
	   @cpf varchar(50), 
	   @cpf_atu varchar(50), 
	   @cd_cli int;


IF EXISTS(SELECT * FROM DELETED)
	SET @DELETED_COUNT	= 1;

IF EXISTS(SELECT * FROM INSERTED)
	SET @INSERTED_COUNT = 1;


SET @NAO_PERMITE_CADASTRO_CPF_ZERADO = ISNULL((SELECT  VALOR FROM PRC_EMP_CONFIG
													WHERE 	
														PRC_EMP_CONFIG.CD_CHAVE = 'FLAG_BLOQUEIA_CADASTRO_CLIENTE_CPF_ZERADO'),0);
IF @Nao_permite_cadastro_cpf_zerado = 1 
BEGIN

     IF @DELETED_COUNT=0
	BEGIN
	    IF EXISTS 
		    (SELECT i.CD_CLI
			    FROM         
				    INSERTED i 
			    WHERE				    
				    CGC_CPF != '00000000000'
				    AND dbo.VALIDA_CPF_CNPJ(dbo.F_LIMPA_NUMEROS(CGC_CPF)) = 'N')

		    Begin
			    RAISERROR ('TRG_RC_CLI_CPF - CPF ou CNPJ invalido.',16, 1); 
			    RETURN;
		    End
	   END

    IF @INSERTED_COUNT > 0
    BEGIN
	   IF EXISTS(SELECT * FROM  INSERTED I 
				WHERE ISNUMERIC(CGC_CPF) =0 )
	   BEGIN
		  RAISERROR ('TRG_RC_CLI_CPF - Cpf informado deve conter apenas numeros.',16, 1);  
			    RETURN;
	   END
    END    

	--SE  UPDATE
	IF(@INSERTED_COUNT > 0 OR @DELETED_COUNT > 0)
	BEGIN
		IF UPDATE(CGC_CPF)
		BEGIN
			IF EXISTS (SELECT *  FROM  INSERTED  
					   WHERE
						  LTRIM(RTRIM(CGC_CPF)) = '' or CAST(CGC_CPF AS NUMERIC) = 0)
			BEGIN
				RAISERROR ('TRG_RC_CLI_CPF - Cliente cpf informado no pode ser vazio ou zero.',16, 1); 
			    RETURN; 
			END
		END
	END
END

GO


