Procedure para deletar registros de mais de uma tabela

Question

Boa tarde,

pessoal.

 

Como posso deletar registros de mais de uma tabela através de uma procedure?

Eu vi em alguns tópicos que poderia utilizar cursor, mas consome muitos recursos do MSSQL, correto? Uma outra solução seria utilizar um laço, pelo que li e então fiz a procedure abaixo, mas não estou sabendo como poderia percorrer através do laço para pegar um ID por vez, alguém saberia me dizer:

 

Code Snippet

CREATE PROCEDURE dbo.STP_ExcluirAparelho

(

      @CodAparelho int

)

AS

      SET NOCOUNT ON

     

      DECLARE @CodHistorico int

      DECLARE @Contador int

 

      BEGIN TRY

      BEGIN TRAN

          SET @Contador = (SELECT COUNT(CodHistorico) FROM TB_Historico WHERE CodAparelho = @CodAparelho)

 

          SET @CodHistorico = (SELECT CodHistorico FROM TB_Historico WHERE CodAparelho = @CodAparelho)

 

          WHILE (@Contador > 0)

          BEGIN

                DELETE FROM TB_Leitura WHERE CodHistorico = @CodHistorico

                DELETE FROM TB_Manutencao WHERE CodHistorico = @CodHistorico

                DELETE FROM TB_TempoConsumivel WHERE CodHistorico = @CodHistorico

                -- próximo CodHistorico???

                SET @Contador = @Contador - 1

          END

         

          DELETE FROM TB_Historico WHERE CodAparelho = @CodAparelho

          DELETE FROM TB_Aparelho WHERE CodAparelho = @CodAparelho

      COMMIT TRAN

      END TRY

      BEGIN CATCH

      ROLLBACK TRAN

           SELECT -ERROR_NUMBER()

      END CATCH

 

 

Acabei fazendo 2 Select na procedure, um para o laço e outra seria para ficar pegando o ID. Sei que vai dar errado a procedure acima, coloquei o 2º Select apenas para mostrar o que eu estou querendo fazer.

Se alguém souber outra forma de fazer também, eu agradeço, estou aberto a sugestões.

Answer 1

cara, faz um cursor e testa a performance... eu costumo usar cursores e não me dão problemas na maioria das vezes... a menos que seja um grande volume de dados...

Answer 2

Bem, vou testar então.

Mas como ficaria a chamada do cursor no lugar do while nesta minha procedure?

Answer 3

cara, pelo que vi da tua procedure, ficaria assim... 

REATE PROCEDURE dbo.STP_ExcluirAparelho

(

      @CodAparelho int

)

AS

      SET NOCOUNT ON

     

      DECLARE @CodHistorico int

      DECLARE @Contador int

 

      BEGIN TRY

      BEGIN TRAN

DECLARE Cur CURSOR FOR SELECT CodHistorico FROM TB_Historico WHERE CodAparelho = @CodAparelho

FETCH NEXT FROM Cur INTO @CodHistorico

WHILE @@FETCH_STATUS = 0

BEGIN

OPEN Cur

encao WHERE CodHistorico = @CodHistorico DELETE FROM TB_T

DELETE FROM TB_Leitura WHERE CodHistorico = @CodHistorico DELETE FROM TB_Manu

END

CLOSE Cur

DEALLOCATE Cur

tempoConsumivel WHERE CodHistorico = @CodHistorico FETCH NEXT FROM Cur INTO @CodHistorico

DELETE FROM TB_Historico WHERE CodAparelho = @CodAparelho

DELETE FROM TB_Aparelho WHERE CodAparelho = @CodAparelho

      COMMIT TRAN

      END TRY

      BEGIN CATCH

      ROLLBACK TRAN

           SELECT -ERROR_NUMBER()

      END CATCH

desculpe a formatação... rs

Answer 4

Ok, sem problemas. Obrigado.

 

Bem, vou testar aqui por enquanto e esperar para ver se alguém posta também uma sugestão com laço. Assim já testo para ver se vai ter alguma diferença.

 

 

Answer 5

Boa Noite Marcelo,

 

Não sei se entendi sua necessidade direito. Seria passar um código (@CodAparelho), recuperar os históricos e com bases neles fazer as exclusões em TB_Leitura, TB_Manutencao e TB_TempoConsumivel ?

 

Se for isso, até é possível fazer com laços ou cursores, mas creio que seriam alternativas bem ineficientes já que estariam operando linha a linha e a operação em conjunto da SQL é na esmagadora maioria das vezes mais eficiente. Será que os códigos abaixo não seriam suficientes ?

 

Code Snippet

DECLARE @CodAparelho int

SET @CodAparelho = 1

 

DELETE FROM TB_Leitura

FROM TB_Leitura AS L

INNER JOIN TB_Historico AS H ON L.CodAparelho = H.CodAparelho

WHERE H.CodAparelho = @CodAparelho

 

DELETE FROM TB_Manutencao

FROM TB_Manutencao AS M

INNER JOIN TB_Historico AS H ON M.CodAparelho = H.CodAparelho

WHERE H.CodAparelho = @CodAparelho

 

DELETE FROM TB_TempoConsumivel

FROM TB_Leitura AS T

INNER JOIN TB_Historico AS H ON T.CodAparelho = H.CodAparelho

WHERE H.CodAparelho = @CodAparelho

 

[ ]s,

 

Gustavo

 

Answer 6

Bom dia,

Gustavo.

 

Teria que ser assim então:

Code Snippet

DELETE FROM TB_Leitura

FROM TB_Leitura AS L

INNER JOIN TB_Historico AS H ON L.CodHistorico = H.CodHistorico

WHERE H.CodAparelho = @CodAparelho

 

 

Pois eu não tenho CodAparelho, nas tabelas Manutenção, Leitura e TempoConsumivel. Assim é possível?

E esse 2º FROM que você coloca, seria um SELECT * FROM TB_Leitura AS L ou é assim mesmo a sintaxe?

 

Answer 7

Olá Marcelo,

 

Sim de fato, você não possui o código do aparelho nas tabelas de manutenção, leitura e tempo consumível. Em todo caso, você o possui na tabela de aparelho e se você consegue determinar os registros de um determinado aparelho você consegue determinar o histórico e consegue recuperar os registros das tabelas de manutenção, leitura e Tempo Consumível.

 

O que o DELETE faz é deletar os registros que estejam nas tabelas de manutenção, leitura e tempoconsumivel cujo o histórico esteja na tabela de históricos cujo os registros da tabela de histórico sejam identificados através da variável @codaparelho.

 

Se esse é o entendimento, o DELETE irá funcionar. Se esse não é o entendimento, por favor corrija-me.

 

[ ]s,

 

Gustavo

Answer 8

 

Fiz o teste aqui, mas eu retirei a comparação do CodAparelho e coloquei o CodHistorico, pois o sql server nem deixa salvar com o CodAparelho, já que as tabelas leituras, manutenção, etc, não tem o campo CodAparelho.

Minha modelagem está da seguinte maneira:

Tabela Aparelho(CodAparelho) 

 1 ---> n  Tabela Historico(CodAparelho, CodHistorico)

             1 ---> n Tabela Leitura(CodHistorico), Tabela Manutenção(CodHistorico)...

 

Mas eu fiz a procedure da seguinte maneira:

Code Snippet

DELETE FROM TB_Leitura

FROM TB_Leitura AS L

INNER JOIN TB_Historico AS H ON L.CodHistorico = H.CodHistorico

WHERE H.CodAparelho = @CodAparelho

 

DELETE FROM TB_Manutencao

FROM TB_Manutencao AS M

INNER JOIN TB_Historico AS H ON M.CodHistorico = H.CodHistorico

WHERE H.CodAparelho = @CodAparelho

 

DELETE FROM TB_Historico WHERE CodAparelho = @CodAparelho

DELETE FROM TB_Aparelho WHERE CodAparelho = @CodAparelho

 

A princípio parace que funcionou, mas eu só tinha um equipamento para excluir, tenho que testar com mais de um.

 

E quanto ao DELETE, minha dúvida é porque eu nunca tinha visto essa sintaxe com dois FROM:

DELETE "FROM" TB_Manutencao

"FROM" TB_Manutencao AS M

 

Eu imaginava que você tinha digitado errado. Não sabia que podia fazer assim.

 

Answer 9

Olá Marcelo,

 

A princípio o comando é esse mesmo. Uma vez que você tenha escrito tudo em um único comando, a exclusão será feita mais rapidamente do que fazer um loop para excluir registro a registro.

 

Essa sintaxe não é muito comum mesmo. Na verdade ela é proprietária do SQL Server, mas normalmente você também poderia utilizar uma subquery. Ex:

Code Snippet

DELETE FROM TB_Leitura

WHERE CodHistorico IN

(SELECT CodHistorico FROM TB_Historico

WHERE CodAparelho = @CodAparelho)

 

DELETE FROM TB_Manutencao

WHERE CodHistorico IN

(SELECT CodHistorico FROM TB_Historico

WHERE CodAparelho = @CodAparelho)

 

DELETE FROM TB_Historico WHERE CodAparelho = @CodAparelho

DELETE FROM TB_Aparelho WHERE CodAparelho = @CodAparelho

 

[ ]s,

 

Gustavo

Answer 10

Bem, no fim aprendi mais uma sintaxe!

Muito obrigado pela ajuda!