Query SQL Server Error

Question

I have the following SQL:

INSERT INTO SISCli (CdInscricao,DsEntidade,DsApelido,InInscricao,InCadastro,DsEndereco,NrCEP,NrInscricaoEstadual,NrCGCCPF,NrTelefone,DtCadastro,CdEmpresa,DsEMail,DsUSuarioInc,InClassificacaoFiscal,DsBairro)
SELECT
    case when LEN(ESP353_XML.DES_NrCGCCPF)=13 then '0'+ CAST(ESP353_XML.DES_NrCGCCPF AS VARCHAR)
         when LEN(ESP353_XML.DES_NrCGCCPF)=12 then '00'+ CAST(ESP353_XML.DES_NrCGCCPF AS VARCHAR)
         when LEN(ESP353_XML.DES_NrCGCCPF)=11 then '000'+ CAST(ESP353_XML.DES_NrCGCCPF AS VARCHAR)
         when LEN(ESP353_XML.DES_NrCGCCPF)=10 then '0000'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         when LEN(ESP353_XML.DES_NrCGCCPF)=9  then '00000'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         when LEN(ESP353_XML.DES_NrCGCCPF)=8  then '000000'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         when LEN(ESP353_XML.DES_NrCGCCPF)=7  then '0000000'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         when LEN(ESP353_XML.DES_NrCGCCPF)=6  then '00000000'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         else CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
    end AS [CdInscricao],
    ESP353_XML.DES_DsEntidade AS [DsEntidade],
    ESP353_XML.DES_DsApelido AS [DsApelido],
    case when LEN(ESP353_XML.DES_NrCGCCPF)>11 then 0 else 1 end AS [InInscricao],
    0 AS [InCadastro],
    ESP353_XML.DES_DsEndereco AS [DsEndereco],
    ESP353_XML.DES_NrCEP AS [NrCEP],
    'ISENTO' AS [NrInscricaoEstadual],
    case when LEN(ESP353_XML.DES_NrCGCCPF)=13 then '0'+ CAST(ESP353_XML.DES_NrCGCCPF AS VARCHAR)
         when LEN(ESP353_XML.DES_NrCGCCPF)=12 then '00'+ CAST(ESP353_XML.DES_NrCGCCPF AS VARCHAR)
         when LEN(ESP353_XML.DES_NrCGCCPF)=11 then ''+ CAST(ESP353_XML.DES_NrCGCCPF AS VARCHAR)
         when LEN(ESP353_XML.DES_NrCGCCPF)=10 then '0'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         when LEN(ESP353_XML.DES_NrCGCCPF)=9  then '00'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         when LEN(ESP353_XML.DES_NrCGCCPF)=8  then '000'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         when LEN(ESP353_XML.DES_NrCGCCPF)=7  then '0000'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         when LEN(ESP353_XML.DES_NrCGCCPF)=6  then '00000'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         else CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
    end AS [NrCGCCPF],
    ESP353_XML.DES_NrTelefone AS [NrTelefone],
    GETDATE() AS [DtCadastro],
    1 as [CdEmpresa],
    '[email protected]' as [DsEMail],
    'EdiXMLMI' AS [DsUSuarioInc],
    7 AS [InClassificacaoFiscal],
    substring(ISNULL(ESP353_XML.DES_DsBairro,0),1,15) as [DES_DsBairro]
FROM ESP353_XML
where NOT exists (select 1 from SISCli A WHERE cast(A.CdInscricao as numeric) = cast(ESP353_XML.DES_NrCGCCPF as numeric))

When running it returns the error below:

Mensagem 512, Nível 16, Estado 1, Procedimento TRG_BloqueioInsereRegInvalido, Linha 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

But if I put a top 1 after the SELECT and go running one by one works.

So far I have not figured out the error that returns below and why.

Answer 1

But if I put a top 1 after SELECT and go running one by one it works.

The error is not in the inclusion code but in the Trg_lock trigger procedure. Since the inclusion code works when one line is included at a time, probably the Trg_lock procedure is incorrectly programmed, prepared to receive only one line at each run. As stated in Case study of topics related to Rigger procedures, “This is the most common error in the programming of Rigger procedures, in T-SQL: program the procedure as if a single line was received in the virtual tables”.

A Trigger procedure in T-SQL should be programmed considering that the virtual tables INSERTED and DELETED may have no, one or more lines. If you have difficulty reprogramming the Trg_lock procedure being validated, I suggest you open another topic with information about the procedure.

Additional reading suggestion: Traps in trigger procedure programming.

---

You can simplify the inclusion code. Rate the suggestion below.

-- código #1
INSERT INTO SISCli (CdInscricao, DsEntidade, DsApelido, InInscricao, InCadastro, DsEndereco, NrCEP, NrInscricaoEstadual, NrCGCCPF, NrTelefone, DtCadastro, CdEmpresa, DsEMail, DsUSuarioInc, InClassificacaoFiscal, DsBairro)
     SELECT right( ('00000000' + cast(ESP353_XML.DES_NrCGCCPF as varchar)), 14) as [CdInscricao],
            ESP353_XML.DES_DsEntidade AS [DsEntidade],
            ESP353_XML.DES_DsApelido AS [DsApelido],
            case when LEN(ESP353_XML.DES_NrCGCCPF) > 11 then 0 else 1 end AS [InInscricao],
            0 AS [InCadastro],
            ESP353_XML.DES_DsEndereco AS [DsEndereco],
            ESP353_XML.DES_NrCEP AS [NrCEP],
            'ISENTO' AS [NrInscricaoEstadual],
            case when LEN(ESP353_XML.DES_NrCGCCPF) > 11
                 then right( ('00'+ cast(ESP353_XML.DES_NrCGCCPF as varchar)), 14) 
                 else right( ('00000'+ cast(ESP353_XML.DES_NrCGCCPF as varchar)), 11) end as [NrCGCCPF],
            ESP353_XML.DES_NrTelefone AS [NrTelefone],
            current_timestamp AS [DtCadastro],
            1 as [CdEmpresa],
            '[email protected]' as [DsEMail],
            'EdiXMLMI' AS [DsUSuarioInc],
            7 AS [InClassificacaoFiscal],
            substring(ISNULL(ESP353_XML.DES_DsBairro,0),1,15) as [DES_DsBairro]
       from ESP353_XML
       where not exists (select * from SISCli as A 
                         where cast(A.CdInscricao as numeric) = cast(ESP353_XML.DES_NrCGCCPF as numeric));

---