none
Como fazer uma função no SQL para atualizar valores do produto RRS feed

  • Pergunta

  • Pessoa, 

    devido a pouca experiencia, preciso da ajuda de vocês e opinião de como fazer se possível um script para a seguinte situação no próprio Sql.

    Tentarei explicar da melhor maneira.

    de acordo com a imagem abaixo:

    Tenho duas tabelas (CadProduto e ProdutoComposição) que contem um relacionamento nos campos Codigo PK e CodigoProduto FK.

    Na tabela CadProduto cadastro todos os produtos. Na tabela ProdutoComposição incluo os produtos que fazem parte da composição de algum produto, ou seja, para entender melhor usarei a imagem como exemplo.

    Na tabela ProdutoComposição consta todos os produtos que fazem parte da composição ex: Produto de codigo KIT e PRODUTO.

    A questão é que ao incluir ou excluir composição de um produto na tabela ProdutoComposição eu preciso que atualize o campo ValorCusto da tabela CadProduto. 

    Ex: Se eu excluir da tabela CadProduto o Codigo KIT IV automaticamente tenho que excluir também da tabela ProdutoComposição. Ai tenho que alterar o ValorCusto de 91,00 para 51,00 do codigo KIT e alterar o ValorCusto de 70,00 para 30,00 do codigo PRODUTO da tabela CadProduto.

    Qual seria a melhor maneira de fazer essa função, pois não tenho a minima ideia de como fazer isso.

    Espero que eu tenha conseguido explicar de uma maneira que vocês entendam.

    Obrigado


    segunda-feira, 5 de janeiro de 2015 14:39

Respostas

  • Olá Thyago,

    Talvez essa não seja a melhor forma de fazer existe outras formas usando while, mas acredito que te ajude na sua linha de raciocínio.

    Criei a procedure abaixo crie no seu banco de dados de teste e execute passado o item 

    AtualizacaoPreco 'KIT IV'

    OBS:Se funcionar do jeito que você quer melhore a procedure , colocando uma transação , set nocount on set nocount off etc... E testar bem para ver se não há falhas nos relacionamentos o que pode excluir itens indevidos

    Create procedure AtualizacaoPreco
    @Itemaserapagado as varchar(50)--passar como parametro o 'KIT IV'
    as

    --Prepara a seleção do item a ser excluido para atualização do valor
    Select ValorCusto,CodigoProduto into #TMP  from CadProduto Inner 
    JOin ProdutoComposicao on Codigo=CodigoComposicao 
    Where CodigoComposicao =@Itemaserapagado

    --Efetua atualização 
    UPDATE
        CadProduto
    SET
        CadProduto.ValorCusto = CadProduto.ValorCusto - #TMP.ValorCusto  
    FROM
        CadProduto
    INNER JOIN
        #TMP
    ON
        CadProduto.Codigo = #TMP.CodigoProduto

    --Apaga o item da tabela produto
    DELETE from CadProduto WHERE Codigo=@Itemaserapagado
    DELETE from ProdutoComposicao WHERE CodigoComposicao=@Itemaserapagado
    --Apaga a tabela temporaria criada
    DROP TABLE #TMP  

    GO

    • Sugerido como Resposta AlineBC terça-feira, 6 de janeiro de 2015 15:51
    • Marcado como Resposta Thyago Gonçalves terça-feira, 6 de janeiro de 2015 17:17
    segunda-feira, 5 de janeiro de 2015 17:36
  • Thyago, o comando que sugeri pode ser executado tanto em uma procedure como em uma trigger; não acredito que haverá diferença de performance.


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

    terça-feira, 6 de janeiro de 2015 19:12
  • Thyago,

    Tecnicamente dizendo, depende:

    - Depende da forma que a Stored Procedure for executada; e

    - Depende de como o Trigger esta sendo processado.

    Vale ressaltar que ambos são recursos que fazem uso do Plano de Execução durante sua execução, mas a Procedure você pode indicar o momento da execução, ao contrário do Trigger.

    Um ponto importante que o Trigger é tratado como uma transação independente da tabela que esta vinculado, mas caso este trigger apresente algum tipo de problema poderá impactar diretamente no uso da tabela.

    Eu particularmente prefiro fazer uso de Stored Procedure ao invês do Trigger, por questões da forma de execução.



    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | SoroCódigos] @JuniorGalvaoMVP | pedrogalvaojunior.wordpress.com

    quarta-feira, 7 de janeiro de 2015 12:02

Todas as Respostas

  • Boa tarde,

    Experimente mais ou menos dessa forma:

    update u
    set u.ValorCusto = 
            (select sum(p.ValorCusto) 
             from ProdutoComposicao as c
             inner join CadProduto as p
                 on p.Codigo = c.CodigoComposicao
             where
                 c.CodigoProduto = u.Codigo)
    from CadProduto as u
    where
        u.Codigo = 'KIT'

    Espero que ajude.


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


    segunda-feira, 5 de janeiro de 2015 16:45
  • Olá Thyago,

    Talvez essa não seja a melhor forma de fazer existe outras formas usando while, mas acredito que te ajude na sua linha de raciocínio.

    Criei a procedure abaixo crie no seu banco de dados de teste e execute passado o item 

    AtualizacaoPreco 'KIT IV'

    OBS:Se funcionar do jeito que você quer melhore a procedure , colocando uma transação , set nocount on set nocount off etc... E testar bem para ver se não há falhas nos relacionamentos o que pode excluir itens indevidos

    Create procedure AtualizacaoPreco
    @Itemaserapagado as varchar(50)--passar como parametro o 'KIT IV'
    as

    --Prepara a seleção do item a ser excluido para atualização do valor
    Select ValorCusto,CodigoProduto into #TMP  from CadProduto Inner 
    JOin ProdutoComposicao on Codigo=CodigoComposicao 
    Where CodigoComposicao =@Itemaserapagado

    --Efetua atualização 
    UPDATE
        CadProduto
    SET
        CadProduto.ValorCusto = CadProduto.ValorCusto - #TMP.ValorCusto  
    FROM
        CadProduto
    INNER JOIN
        #TMP
    ON
        CadProduto.Codigo = #TMP.CodigoProduto

    --Apaga o item da tabela produto
    DELETE from CadProduto WHERE Codigo=@Itemaserapagado
    DELETE from ProdutoComposicao WHERE CodigoComposicao=@Itemaserapagado
    --Apaga a tabela temporaria criada
    DROP TABLE #TMP  

    GO

    • Sugerido como Resposta AlineBC terça-feira, 6 de janeiro de 2015 15:51
    • Marcado como Resposta Thyago Gonçalves terça-feira, 6 de janeiro de 2015 17:17
    segunda-feira, 5 de janeiro de 2015 17:36
  • Aline, obrigado pela ajuda funcionou como eu queria, agora outra pergunta como seria com transação conforme você mencionou acima?

    Att.

    terça-feira, 6 de janeiro de 2015 17:16
  • Gapimex, dessa forma seria com uma procedure ou poderia ser com Trigger? qual seria melhor em performance?

    Att.

    terça-feira, 6 de janeiro de 2015 17:24
  • Thyago, o comando que sugeri pode ser executado tanto em uma procedure como em uma trigger; não acredito que haverá diferença de performance.


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

    terça-feira, 6 de janeiro de 2015 19:12
  • Thyago,

    Tecnicamente dizendo, depende:

    - Depende da forma que a Stored Procedure for executada; e

    - Depende de como o Trigger esta sendo processado.

    Vale ressaltar que ambos são recursos que fazem uso do Plano de Execução durante sua execução, mas a Procedure você pode indicar o momento da execução, ao contrário do Trigger.

    Um ponto importante que o Trigger é tratado como uma transação independente da tabela que esta vinculado, mas caso este trigger apresente algum tipo de problema poderá impactar diretamente no uso da tabela.

    Eu particularmente prefiro fazer uso de Stored Procedure ao invês do Trigger, por questões da forma de execução.



    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | SoroCódigos] @JuniorGalvaoMVP | pedrogalvaojunior.wordpress.com

    quarta-feira, 7 de janeiro de 2015 12:02
  • Pedro,

    obrigado pela explicação, foi pensando nisso que resolvi usar uma Stored Procedure

    Valeu

    quinta-feira, 8 de janeiro de 2015 19:29
  • Olá Thyago,

    Com relação a transação serve para garantir que todo comando foi executado com sucesso e caso ocorra alguma falha ao executar a procedure o sql desfaça tudo que fez desde o inicio da transação.

       EXEMPLO: Teste as 2 situações de sucesso e falha

    ALTER procedure AtualizacaoPreco
    @Itemaserapagado as varchar(50)--passar como parametro o 'KIT IV'
    as
    SET NOCOUNT ON; --serve para não te retornar o numero de linhas afetadas 
    BEGIN TRY
        BEGIN TRAN
    --Prepara a seleção do item a ser excluido para atualização do valor
    Select ValorCusto,CodigoProduto into #TMP  from CadProduto Inner 
    JOin ProdutoComposicao on Codigo=CodigoComposicao 
    Where CodigoComposicao =@Itemaserapagado

    --Efetua atualização 
    UPDATE
        CadProduto
    SET
        CadProduto.ValorCusto = CadProduto.ValorCusto - #TMP.ValorCusto  
    FROM
        CadProduto
    INNER JOIN
        #TMP
    ON
        CadProduto.Codigo = #TMP.CodigoProduto

    --Apaga o item da tabela produto
    DELETE from CadProduto WHERE Codigo=@Itemaserapagado
    DELETE from ProdutoComposicao WHERE CodigoComposicao=@Itemaserapagado
    --Apaga a tabela temporaria criada
    DROP TABLE #TMP 

    COMMIT TRAN --Esse comando confirma a execução da transação, depois de 
    --executado a transação não poderá mais ser desfeita

     END TRY

    BEGIN CATCH -- Sobre o select abaixo se não precisar que te retorne o erro pode tirar e deixar só a parte ---do rollback

        SELECT ERROR_NUMBER() AS "ERROR_NUMBER",
                   ERROR_SEVERITY() AS "ERROR_SEVERITY",
                   ERROR_STATE() AS "ERROR_STATE",
                   ERROR_PROCEDURE() AS "ERROR_PROCEDURE",
                   ERROR_LINE() AS "ERROR_LINE",
                   ERROR_MESSAGE() AS "ERROR_MESSAGE"

        RAISERROR('Erro na transação', 14, 1)
     --Esse comando desfaz 
        ROLLBACK TRAN

    END CATCH
    SET NOCOUNT OFF;--serve para não te retornar o numero de linhas afetadas

    GO

    OBS: Particularmente não gosto de trabalhar da forma que está fazendo utilizando texto em campo de código,  pois terá que garantir que o campo código seja chave primária e não seja repetido. Se tiver um bilhão de produtos terá que dar nomes a cada um deles, porém dependendo do ramo que for trabalhar exemplo Luminárias e Parafusos existem diversos produtos similares com os nomes semelhantes para diferenciar terá que aumentar a descrição do código. Nesse caso prefiro com número, mas veja o que é melhor para você.




    • Editado AlineBC sexta-feira, 23 de janeiro de 2015 11:51
    sexta-feira, 23 de janeiro de 2015 11:47