none
Ajuda com Trigger para fazer uma mascara de entrada RRS feed

  • Pergunta

  • Olá Pessoal,

    Preciso de uma ajuda.

    Seguinte:

    Tenho um ERP que alimenta a tabela abaixo

    Tabela -> DA5010

    Campo -> DA5_COD varchar(6)

    Quero fazer uma trigger que se o usuario digitar "1" a trigger formata esse valor digitado de "1" para "000001"

    Fiz a trigger abaixo e funcionou, mas nao sei se tá correto o que eu fiz, pois a trigger dispara outro insert

    CREATE TRIGGER TR_INC_DA5
       ON DA5010
       INSTEAD OF INSERT
    AS

    DECLARE @CodDigitado varchar(max)
    DECLARE @DA5_DESC VARCHAR(MAX)
    DECLARE @CodFormatado varchar(max)
    DECLARE @ProxCod varchar(max)
    BEGIN
      Select @CodDigitado = DA5_COD FROM inserted
      Select @DA5_DESC = DA5_DESC FROM inserted
     
      SET @CodFormatado = REPLICATE('0',6-LEN(@CodDigitado)) + LTRIM(RTRIM(@CodDigitado))
      IF (@CodDigitado <> @CodFormatado)
      BEGIN
        SELECT @ProxCod = COUNT(DA5_COD) + 1 FROM DA5010
        INSERT INTO DA5010 (DA5_COD, DA5_DESC, R_E_C_N_O_) VALUES(@CodFormatado, @DA5_DESC, @ProxCod)
      END
    END
    GO


    Zagatti, Angelo Itápolis-SP

    quinta-feira, 19 de dezembro de 2013 13:00

Respostas

  • Angelo,

         Vamos por partes, iniciando do final:

    "O que o Fúlvio está querendo te dizer é que você pode fazer isso através de uma consulta SQL (select....) ou de uma função que formata o campo para você, ao invés de fazer na base, gravando os dados formatados, que é ruim. Se for o caso, faça um select assim"

         - A sugestão do Fulvio, apesar de ser a melhor neste seu caso, não é aplicável, porque você não tem acesso ao código do ERP, ou seja, não tem como mudar isso na aplicação. O uso da função ou do select já formatando não pode ser aplicado. Então... Esqueça esta opção, você não poderá usar ela. Mas, ela é a melhor opção tecnicamente.

    "Note que por conta de que o seu campo tem o tipo errado (varchar) você vai ter que ficar "ajeitando" os dados para que não tenha problemas de ordenação... "

    Porque o o campo tem o tipo errado (varchar) ?

    O campo tem o tipo errado porque normalmente campos do tipo código, principalmente se for chave primária, na maioria das vezes deve ser numérico.

    Veja um exemplo:

    Coloque rapidamente a sequencia abaixo em ordem crescente:

    4, 6, 5, 7, 8, 1, 3, 2, 0, 9

    Agora, coloque tente fazer o mesmo com a sequencia abaixo:

    a12, a00, a42, a32, b54, b, 9,

    A segunda sequencia é bem mais difícil, não??!?!?!?!?! Além disso, utilizando um varchar, o SQL precisa de muito mais espaço e para armazenar o dado que está contido neste campo.

    Pense de outra forma, um campo chamado Salário varchar(10) está correto? Não né! Salário é um número, e como tal, deve ser tratado como um número! um texto é muito mais difícil de comparar, incluindo operadores, como =, >, <, etc... Um exemplo utilizando um campo varchar, o que é maior 2 ou 10? Num teste simples 2 é maior que 10! Faça o teste!

    declare @v1 varchar(10)
    declare @v2 varchar(10)
    set @v1 = '2'
    set @v2 = '10'

    if @v1 > @v2
        print 'maior'
    else print 'menor'

       Por isso é que o seu campo está errado! porque o campo deve ter o tipo do dado que ele vai conter, não outro. é por isso que existem tantos tipos de dados como os de datas, money, int. Senão bastaria que os bancos de dados tivessem apenas um tipo de dado varchar e seria suficiente!

    Eu tive a ideia de formatar a gravação do campo, justamente para não ter problema de ordenação.

    Ex '1' diferente de '01' que é diferente de '001' e assim por diante.E também para o usuário no momento da inclusão de um novo registro não ter que ficar contando os zeros para digitar.

        Esse é o motivo que prova que o campo tem o tipo errado, porque voce tem que formatar para não ter problema de ordenação. Se o campo fosse do tipo int, você não teria esse problema, a ordenação aconteceria normalmente. Neste caso, 2 seria avaliado pelo SQL como menor que 10, como ele é de fato!

    Não entendi a falha que voce (Roberto) fala que eu tenho no IF.

    A sua trigger é do tipo INSTEAD OF, ou seja, o insert original, aquele que você manda pro SQL executar é desprezado e é executado apenas o que está dentro da trigger, por isso você tem 2 inserts (um que voce escreveu e o outro dentro da trigger). Acontece que o segundo insert só vai acontecer se a variável @CodDigitado for diferente de @CodFormatado. Imagine o insert assim:

    INSERT INTO DA5010 (DA5_COD, DA5_DESCR) VALUES ('1', 'TESTE')

    A sua trigger vai formatar o campo DA5_COD de '1' para '000001'. Neste caso, @CodDigitado é igual a '1' e @CodFormatado é igual a '000001'. São diferentes! Vai atender a condição do seu if e o insert vai funcionar corretamente.

    Agora, se o seu insert for:

    INSERT INTO DA5010 (DA5_COD, DA5_DESCR) VALUES ('000001', 'TESTE')

    A sua trigger não precisará formatar o '000001' porque ele já está formatado, mas ele vai setar a variável com o valor '000001'. Aí no IF, quando ele testar, @CodDigitado será igual a '000001' e @CodFormatado também! O SQL não entrará no IF e o seu insert não será executado. O usuário vai ficar tentando inserir e o SQL não vai inserir.

      IF (@CodDigitado <> @CodFormatado)
      BEGIN
        SELECT @ProxCod = COUNT(DA5_COD) + 1 FROM DA5010
        INSERT INTO DA5010 (DA5_COD, DA5_DESC, R_E_C_N_O_) VALUES(@CodFormatado, @DA5_DESC, @ProxCod)
      END


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008



    sexta-feira, 20 de dezembro de 2013 12:34
    Moderador
  • Angelo,

         Não vai entrar em loop, não se preocupe com isso. O insert vai disparar a trigger que vai executar apenas o segundo insert (porque é uma trigger de INSTEAD OF), sem disparar novamente a trigger.


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008


    sexta-feira, 20 de dezembro de 2013 13:18
    Moderador

Todas as Respostas

  • Se tem que montar é uma função ...!!!! que irá formatar o seu dado, sendo isso na hora do insert/update ou então ( que na minha visão mais recomendavel ) na hora de fazer uma Pesquisa SQL

    Cria por esse menu ...

    E ai não vai dar insert vai servir como um apoio em suas SQLs


    Fulvio Cezar Canducci Dias

    quinta-feira, 19 de dezembro de 2013 13:15
  • Fulvio, nao sei se entendi bem o que voce quis dizer (criar uma funcao ou na consulta sql).

    Eu nao tenho acesso ao fonte do ERP.

    Preciso fazer isso a nivel de banco de dados.

    Como poderia fazer isso com uma function do banco no momento do insert ?

    Somente tenho acesso quando uma trigger é disparada pelo comando insert do ERP.

    Agradeço sua ajuda.


    Zagatti, Angelo Itápolis-SP

    quinta-feira, 19 de dezembro de 2013 13:38
  • Bom dia,

    Angelo, sobre a trigger, é necessário executar o Insert dentro dela para que os dados sejam inseridos na tabela.

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br

    quinta-feira, 19 de dezembro de 2013 13:42
  • O que eu disse é nivel de banco mesmo!

    mas, lógico deve ter fatores que você não relatou!!!


    Fulvio Cezar Canducci Dias

    quinta-feira, 19 de dezembro de 2013 14:23
  • Fulvio, como fazer a nivel de banco ?

    "Se tem que montar é uma função ...!!!! que irá formatar o seu dado, sendo isso na hora do insert/update ou então ( que na minha visão mais recomendavel ) na hora de fazer uma Pesquisa SQL"

    Voce poderia dar algum exemplo ?


    Zagatti, Angelo Itápolis-SP

    quinta-feira, 19 de dezembro de 2013 17:13
  • CREATE TRIGGER TR_INC_DA5 
        ON DA5010 
        INSTEAD OF INSERT
     AS
    
     BEGIN
      
       INSERT INTO DA5010 (DA5_COD, DA5_DESC, R_E_C_N_O) 
       SELECT REPLICATE('0',6-LEN(DA5_COD)) + LTRIM(RTRIM(DA5_COD)), da5_desc, (SELECT COUNT(DA5_COD) + 1 FROM DA5010)
              FROM INSERTED
    
    END

    Angelo....

         Normalização!!!! É o melhor que eu posso te dizer agora... Note que por conta de que o seu campo tem o tipo errado (varchar) você vai ter que ficar "ajeitando" os dados para que não tenha problemas de ordenação...

         Olhando melhor para o nome da sua tabela, vejo que é um ERP Totvs... Eu modifiquei um pouco a sua trigger para que ela funcione da mesma forma mas de um jeito mais simples e não tenha a falha do IF que você tem, já que se tiver um @CodDigitado igual ao @CodFormatado você não fará o insert... Note que a sua trigger é do tipo INSTEAD OF, ou seja, o seu insert "original" não fará nada, somente o insert dentro da trigger é que realmente irá adicionar dados à tabela.

         Boa sorte aí com o seu Totvs!!!

     


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008



    quinta-feira, 19 de dezembro de 2013 17:19
    Moderador
  • Fulvio, como fazer a nivel de banco ?

    "Se tem que montar é uma função ...!!!! que irá formatar o seu dado, sendo isso na hora do insert/update ou então ( que na minha visão mais recomendavel ) na hora de fazer uma Pesquisa SQL"

    Voce poderia dar algum exemplo ?


    Zagatti, Angelo Itápolis-SP

    Fazer isso no banco neh! a rotina!

    Fulvio Cezar Canducci Dias

    quinta-feira, 19 de dezembro de 2013 18:21
  • Angelo,

       O que o Fúlvio está querendo te dizer é que você pode fazer isso através de uma consulta SQL (select....) ou de uma função que formata o campo para você, ao invés de fazer na base, gravando os dados formatados, que é ruim. Se for o caso, faça um select assim

    SELECT REPLICATE('0',6-LEN(DA5_COD)) + LTRIM(RTRIM(DA5_COD)), da5_desc,.... outros campos... from DA5010 where CAMPO = Codigo


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008

    quinta-feira, 19 de dezembro de 2013 21:38
    Moderador
  • Angelo,

       O que o Fúlvio está querendo te dizer é que você pode fazer isso através de uma consulta SQL (select....) ou de uma função que formata o campo para você, ao invés de fazer na base, gravando os dados formatados, que é ruim. Se for o caso, faça um select assim

    SELECT REPLICATE('0',6-LEN(DA5_COD)) + LTRIM(RTRIM(DA5_COD)), da5_desc,.... outros campos... from DA5010 where CAMPO = Codigo


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008

    Isso mesmo, prefiro guarda simples e mostrar com formatação, valeu Roberto F Fonseca !!!

    Fulvio Cezar Canducci Dias

    sexta-feira, 20 de dezembro de 2013 00:45
  • Fúlvio,

    Eu concordo totalmente com você. porém estamos falando de um ERP que é uma caixa preta!!!


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008


    sexta-feira, 20 de dezembro de 2013 02:29
    Moderador
  • Fúlvio,

    Eu concordo totalmente com você. porém estamos falando de um ERP que é uma caixa preta!!!


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008


    Sim Roberto eu sei ... !!! é complicado para gente afirmar qualquer coisa!

    Fulvio Cezar Canducci Dias

    sexta-feira, 20 de dezembro de 2013 03:03
  • Roberto e Fulvio,

    Obrigado pelas opnioes, mas não entendi algumas coisas.

    "Note que por conta de que o seu campo tem o tipo errado (varchar) você vai ter que ficar "ajeitando" os dados para que não tenha problemas de ordenação... "

    Porque o o campo tem o tipo errado (varchar) ?

    Eu tive a ideia de formatar a gravação do campo, justamente para não ter problema de ordenação.

    Ex '1' diferente de '01' que é diferente de '001' e assim por diante.E também para o usuário no momento da inclusão de um novo registro não ter que ficar contando os zeros para digitar.

    Não entendi a falha que voce (Roberto) fala que eu tenho no IF.

    Outra coisa que eu não entendi, é como voces estao sujerindo.

    "O que o Fúlvio está querendo te dizer é que você pode fazer isso através de uma consulta SQL (select....) ou de uma função que formata o campo para você, ao invés de fazer na base, gravando os dados formatados, que é ruim. Se for o caso, faça um select assim"

    Quem me apresenta os dados, é o ERP. Não estou entendendo como fazer num select ou numa função.

    Como eu iria disparar um select ou uma função ?

    Grato pela colaboração de voces.


    Zagatti, Angelo Itápolis-SP

    sexta-feira, 20 de dezembro de 2013 09:47
  • Resumindo, 

    Qual nível de acesso você tem nesse ERP e por acaso ele pode ser alterado ?

    Qual nível de acesso ao Banco e qual é o Banco?

    Eu acredito que tudo que falarmos como o Roberto, bem me alertou, talvez seja inutil pelo fato de ser um ERP !!!


    Fulvio Cezar Canducci Dias


    sexta-feira, 20 de dezembro de 2013 12:27
  • Angelo,

         Vamos por partes, iniciando do final:

    "O que o Fúlvio está querendo te dizer é que você pode fazer isso através de uma consulta SQL (select....) ou de uma função que formata o campo para você, ao invés de fazer na base, gravando os dados formatados, que é ruim. Se for o caso, faça um select assim"

         - A sugestão do Fulvio, apesar de ser a melhor neste seu caso, não é aplicável, porque você não tem acesso ao código do ERP, ou seja, não tem como mudar isso na aplicação. O uso da função ou do select já formatando não pode ser aplicado. Então... Esqueça esta opção, você não poderá usar ela. Mas, ela é a melhor opção tecnicamente.

    "Note que por conta de que o seu campo tem o tipo errado (varchar) você vai ter que ficar "ajeitando" os dados para que não tenha problemas de ordenação... "

    Porque o o campo tem o tipo errado (varchar) ?

    O campo tem o tipo errado porque normalmente campos do tipo código, principalmente se for chave primária, na maioria das vezes deve ser numérico.

    Veja um exemplo:

    Coloque rapidamente a sequencia abaixo em ordem crescente:

    4, 6, 5, 7, 8, 1, 3, 2, 0, 9

    Agora, coloque tente fazer o mesmo com a sequencia abaixo:

    a12, a00, a42, a32, b54, b, 9,

    A segunda sequencia é bem mais difícil, não??!?!?!?!?! Além disso, utilizando um varchar, o SQL precisa de muito mais espaço e para armazenar o dado que está contido neste campo.

    Pense de outra forma, um campo chamado Salário varchar(10) está correto? Não né! Salário é um número, e como tal, deve ser tratado como um número! um texto é muito mais difícil de comparar, incluindo operadores, como =, >, <, etc... Um exemplo utilizando um campo varchar, o que é maior 2 ou 10? Num teste simples 2 é maior que 10! Faça o teste!

    declare @v1 varchar(10)
    declare @v2 varchar(10)
    set @v1 = '2'
    set @v2 = '10'

    if @v1 > @v2
        print 'maior'
    else print 'menor'

       Por isso é que o seu campo está errado! porque o campo deve ter o tipo do dado que ele vai conter, não outro. é por isso que existem tantos tipos de dados como os de datas, money, int. Senão bastaria que os bancos de dados tivessem apenas um tipo de dado varchar e seria suficiente!

    Eu tive a ideia de formatar a gravação do campo, justamente para não ter problema de ordenação.

    Ex '1' diferente de '01' que é diferente de '001' e assim por diante.E também para o usuário no momento da inclusão de um novo registro não ter que ficar contando os zeros para digitar.

        Esse é o motivo que prova que o campo tem o tipo errado, porque voce tem que formatar para não ter problema de ordenação. Se o campo fosse do tipo int, você não teria esse problema, a ordenação aconteceria normalmente. Neste caso, 2 seria avaliado pelo SQL como menor que 10, como ele é de fato!

    Não entendi a falha que voce (Roberto) fala que eu tenho no IF.

    A sua trigger é do tipo INSTEAD OF, ou seja, o insert original, aquele que você manda pro SQL executar é desprezado e é executado apenas o que está dentro da trigger, por isso você tem 2 inserts (um que voce escreveu e o outro dentro da trigger). Acontece que o segundo insert só vai acontecer se a variável @CodDigitado for diferente de @CodFormatado. Imagine o insert assim:

    INSERT INTO DA5010 (DA5_COD, DA5_DESCR) VALUES ('1', 'TESTE')

    A sua trigger vai formatar o campo DA5_COD de '1' para '000001'. Neste caso, @CodDigitado é igual a '1' e @CodFormatado é igual a '000001'. São diferentes! Vai atender a condição do seu if e o insert vai funcionar corretamente.

    Agora, se o seu insert for:

    INSERT INTO DA5010 (DA5_COD, DA5_DESCR) VALUES ('000001', 'TESTE')

    A sua trigger não precisará formatar o '000001' porque ele já está formatado, mas ele vai setar a variável com o valor '000001'. Aí no IF, quando ele testar, @CodDigitado será igual a '000001' e @CodFormatado também! O SQL não entrará no IF e o seu insert não será executado. O usuário vai ficar tentando inserir e o SQL não vai inserir.

      IF (@CodDigitado <> @CodFormatado)
      BEGIN
        SELECT @ProxCod = COUNT(DA5_COD) + 1 FROM DA5010
        INSERT INTO DA5010 (DA5_COD, DA5_DESC, R_E_C_N_O_) VALUES(@CodFormatado, @DA5_DESC, @ProxCod)
      END


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008



    sexta-feira, 20 de dezembro de 2013 12:34
    Moderador
  • Fulvio,

    "Qual nível de acesso você tem nesse ERP e por acaso ele pode ser alterado ?"

    O ERP é um .exe como qualquer sistema. Tenho como customiza-lo, porem cada atualização que tiver da matriz, tenho que recompilar minhas customizações e é isso que eu não queria fazer. Por isso achei mais fácil fazer no banco.   

    "Qual nível de acesso ao Banco e qual é o Banco?"

    O Banco é SQL Server, e tenho acesso total a ele.

    A minha duvida é com relação a executar uma trigger INSTEAD OF que foi acionada por um insert, e dentro desta mesma trigger disparar outro insert. Nao sei se isso poderia entrar num loop. Por isso que coloquei o IF na trigger.

    Entao, a duvida é com relação a trigger INSTEAD OF disparar outro insert na mesma tabela.

    Mais uma vez, obrigado pelas sugestões.  


    Zagatti, Angelo Itápolis-SP

    sexta-feira, 20 de dezembro de 2013 12:46
  • Angelo,

         Não vai entrar em loop, não se preocupe com isso. O insert vai disparar a trigger que vai executar apenas o segundo insert (porque é uma trigger de INSTEAD OF), sem disparar novamente a trigger.


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008


    sexta-feira, 20 de dezembro de 2013 13:18
    Moderador
  • Roberto,

    Com relação ao campo do tipo errado, na verdade eu achei que voce estava falando da(s) minhas variáveis declaradas na trigger.

    Como os campos da tabela do ERP (que não são meus) sao varchar isso não tava em questão.

    Para campos que armazenam "codigo" com certeza deveriam ser numericos(int).

    O que eu não sabia, é que uma trigger instead of iria desprezar o insert "original" e executar apenas o insert da trigger.

    Eu achei que poderia entrar num loop infinito. Por isso eu coloquei aquele IF.

    Valeu, foi uma bela aula que voce deu.

    Obrigado. 


    Zagatti, Angelo Itápolis-SP

    sexta-feira, 20 de dezembro de 2013 15:46
  • Roberto,

    Era exatamente essa minha preocupação. Entrar num loop infinito.

    Eu nao sabia se eu estava fazendo uma "gambiarra" executando uma trigger e dentro dela outro comando insert na mesma tabela.

    Valeu pela aula.

    Obrigado.


    Zagatti, Angelo Itápolis-SP

    sexta-feira, 20 de dezembro de 2013 15:50