Tigger loop infinito

Question

Criei uma tigger para fazer a validação de CPF, mas ao testar fazendo um insert, a query nunca termina, aparentando um loop infinito.

Já verifiquei o código diversas vezes mas não estou encontrando o erro.

CREATE TRIGGER [dbo].[Trigger_Clients_CpfValidation]
    ON [dbo].[Clients]
	INSTEAD OF INSERT, UPDATE
	AS
    BEGIN
        SET NoCount ON
		
		DECLARE @cpfCleaned varchar(11),
				@cpfValidated varchar(9),
				@multiplicator tinyint,
				@digitsCpfCounter tinyint,
				@sumMultplications smallint

		SET @cpfCleaned = REPLACE(REPLACE((SELECT cpf FROM inserted), '.', ''), '-', '');

		IF @cpfCleaned = '12345678909'
			THROW 50001, 'Cpf Invalid', 1;

		SET @cpfValidated = SUBSTRING(@cpfCleaned, 1, 9);

		WHILE(LEN(@cpfValidated) < 11)
			BEGIN
				
				SET @digitsCpfCounter = LEN(@cpfValidated);
				SET @multiplicator = 2;
				SET @sumMultplications = 0;

				WHILE(@digitsCpfCounter >= 1)
					BEGIN
						SET @sumMultplications += CONVERT(tinyint, SUBSTRING(@cpfValidated, @digitsCpfCounter, 1)) * @multiplicator;
						SET @digitsCpfCounter -= 1;
						SET @multiplicator += 1;
					END

				IF @sumMultplications % 11 >= 2
					SET @cpfValidated += CONVERT(varchar, 11 - (@sumMultplications % 11));
				ELSE
					SET @cpfValidated += CONVERT(varchar, 0);
				
			END

		IF @cpfCleaned != @cpfValidated
			THROW 50001, 'Cpf Invalid', 1;
    END

Answer 1

MAtheus, a lógica do loop está estranho, porque WHILE(LEN(@cpfValidated) < 11) ? Pela lógica da sua rotina, o LEN(@cpfValidated) sempre será 9 e nunca sairá do loop.

Answer 2

Aliás, porque usar uma trigger? Como é só para validação, porque não criar uma check constraint com function na tabela?

Exemplo:

IF EXISTS (SELECT *
             FROM [sys].[objects]
            WHERE [name] = 'fnValidaCPF') 
    BEGIN
        DROP FUNCTION [fnValidaCPF];
    END;

GO

CREATE FUNCTION [fnValidaCPF]
(@CPF VARCHAR(11)
)
RETURNS BIT
AS
 BEGIN
     DECLARE @Indice [INT], @Soma [INT], @Dig1 [INT], @Dig2 [INT], @CPF_temp VARCHAR(11), @Digitos_Iguais CHAR(1), @Resultado CHAR(1);

     SET @Resultado = 'N';

     SET @CPF_temp = SUBSTRING(@CPF, 1, 1);

     SET @Indice = 1;
     SET @Digitos_Iguais = 'S';

     WHILE(@Indice <= 11)
         BEGIN
             IF SUBSTRING(@CPF, @Indice, 1) <> @CPF_temp
                 SET @Digitos_Iguais = 'N';
             SET @Indice = @Indice + 1;
         END;

     IF @Digitos_Iguais = 'N'
         BEGIN
             SET @Soma = 0;
             SET @Indice = 1;
             WHILE(@Indice <= 9)
                 BEGIN
                     SET @Soma = @Soma + CONVERT([INT], SUBSTRING(@CPF, @Indice, 1)) * (11 - @Indice);
                     SET @Indice = @Indice + 1;
                 END;

             SET @Dig1 = 11 - (@Soma % 11);

             IF @Dig1 > 9
                 SET @Dig1 = 0;

             SET @Soma = 0;
             SET @Indice = 1;
             WHILE(@Indice <= 10)
                 BEGIN
                     SET @Soma = @Soma + CONVERT([INT], SUBSTRING(@CPF, @Indice, 1)) * (12 - @Indice);
                     SET @Indice = @Indice + 1;
                 END;

             SET @Dig2 = 11 - (@Soma % 11);

             IF @Dig2 > 9
                 SET @Dig2 = 0;

             IF(@Dig1 = SUBSTRING(@CPF, LEN(@CPF)-1, 1))
               AND (@Dig2 = SUBSTRING(@CPF, LEN(@CPF), 1))
                 SET @Resultado = 1;
             ELSE
             SET @Resultado = 0;
         END;
     RETURN @Resultado;
 END;

/**************************************************************************************************************/

IF EXISTS (SELECT *
             FROM [sys].[tables]
            WHERE [name] = 'tb_RegistroCPF') 
    BEGIN
        DROP TABLE [dbo].[tb_RegistroCPF];
    END;
GO

CREATE TABLE [dbo].[tb_RegistroCPF]
([cod_Tabela] [INT] NOT NULL,
 [dth_Hora]   VARCHAR(5),
 [num_CPF]    VARCHAR(11),
 CONSTRAINT [PK_tb_Registro] PRIMARY KEY NONCLUSTERED([cod_Tabela] ASC)
 WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY];

GO

ALTER TABLE [dbo].[tb_RegistroCPF] WITH NOCHECK ADD CONSTRAINT [chk_tb_RegistroCPF_CPF] CHECK([dbo].[fnValidaCPF]([num_CPF]) = 1);
GO

/**************************************************************************************************************/

INSERT INTO [tb_RegistroCPF] VALUES (1, GETDATE(), '78690263870');
INSERT INTO [tb_RegistroCPF] VALUES (2, GETDATE(), '78690263871');

Answer 3

MAtheus, a lógica do loop está estranho, porque WHILE(LEN(@cpfValidated) < 11) ? Pela lógica da sua rotina, o LEN(@cpfValidated) sempre será 9 e nunca sairá do loop.

Se eu não estou passando batido em alguma coisa:

    IF @sumMultplications % 11 >= 2
     SET @cpfValidated += CONVERT(varchar, 11 - (@sumMultplications % 11));
    ELSE
     SET @cpfValidated += CONVERT(varchar, 0);

Deveria garantir que o loop termine, ou seja, na primeira execução, LEN(@cpfValidated) valerá 9, ai os IFs acima acrescentarão mais um caractere à @cpfValidated, então LEN(@cpfValidated) passará a ser 10.
Na próxima execução LEN(@cpfValidated) passará a ser 11, assim o loop deveria encerrar.

Answer 4

Matheus, eu testei aqui, somente o trecho do código e sempre retorna 9:

DECLARE @cpfCleaned varchar(11),
				@cpfValidated varchar(9),
				@multiplicator tinyint,
				@digitsCpfCounter tinyint,
				@sumMultplications smallint

		SET @cpfCleaned = REPLACE(REPLACE(('786.902.638-70'), '.', ''), '-', '');

		SET @cpfValidated = SUBSTRING(@cpfCleaned, 1, 9);

		WHILE(LEN(@cpfValidated) < 11)
			BEGIN
				
				SET @digitsCpfCounter = LEN(@cpfValidated);
				SET @multiplicator = 2;
				SET @sumMultplications = 0;

				WHILE(@digitsCpfCounter >= 1)
					BEGIN
						SET @sumMultplications += CONVERT(tinyint, SUBSTRING(@cpfValidated, @digitsCpfCounter, 1)) * @multiplicator;
						SET @digitsCpfCounter -= 1;
						SET @multiplicator += 1;
					END

				IF @sumMultplications % 11 >= 2
					SET @cpfValidated += CONVERT(varchar, 11 - (@sumMultplications % 11));
				ELSE
					SET @cpfValidated += CONVERT(varchar, 0);
				
				Print LEN(@cpfValidated)
			END

---

Fausto Fiorese Branco Database Specialist http://br.linkedin.com/in/faustobranco/ http://www.dbinternals.com.br

Answer 5

Realmente, e já descobri o problema. Porém ainda não sei a solução.

O problema é que a concatenação não está acontecendo. Tentei usar a função CONCAT mas o problema continua.

				IF @sumMultplications % 11 >= 2
					BEGIN
					SET @cpfValidated = CONCAT(@cpfValidated, CONVERT(varchar, 11 - (@sumMultplications % 11)));
					SELECT @cpfValidated;
					END
				ELSE
					BEGIN
					SET @cpfValidated = CONCAT(@cpfValidated, CONVERT(varchar, 0));
					SELECT @cpfValidated;
					END
	

Answer 6

Matheus, se é esse o problema, altere o tamanho da variavel @cpfValidated para VarChar(11), como varchar(9) não vai dar certo nunca...

---

Fausto Fiorese Branco Database Specialist http://br.linkedin.com/in/faustobranco/ http://www.dbinternals.com.br

Answer 7

Matheus, se é esse o problema, altere o tamanho da variavel @cpfValidated para VarChar(11), como varchar(9) não vai dar certo nunca...

---

Fausto Fiorese Branco Database Specialist http://br.linkedin.com/in/faustobranco/ http://www.dbinternals.com.br

Verdade....

Passei batido....