Boa noite pessoal 🙂
1) Segue resposta do desafio anterior.
Eu coloquei o script com BEGIN TRAN, pra que possamos ver o resultado sem commit no banco, pra que testemos de novo o script sem necessidade de rodar tudo de novo.
/* Vamos testar em uma transação */ BEGIN TRAN MERGE dbo.Empregado AS TARGET USING dbo.ListaMudancasRH AS SOURCE ON (SOURCE.nome) = (TARGET.nome) --> #1 WHEN MATCHED AND SOURCE.ativo = 1 THEN UPDATE SET TARGET.cargo = SOURCE.cargo --> #2 WHEN MATCHED AND SOURCE.ativo = 0 THEN DELETE --> #3 WHEN NOT MATCHED BY TARGET THEN INSERT (nome,cargo,ativo) VALUES (SOURCE.nome,SOURCE.cargo, SOURCE.ativo); --> Confirmando os resultados! SELECT * FROM dbo.Empregado /* ROLLBACK */ -- Ao terminar execute apenas este comando!
Vamos primeiro traduzir alguns termos no contexto de um comando MERGE:
– TARGET: A tabela alvo, que você tem por objetivo atualizar. Eu prefiro traduzir TARGET como destino pra assimilar com mais facilidade;
– SOURCE: A tabela fonte de onde vem a informação necessária para se atualizar a tabela alvo. Eu prefiro traduzir SOURCE neste caso como origem pra assimilar com mais facilidade;
Sobre o algoritmo: Quero usar MERGE na tabela Empregado, que é o destino da atualização. Quero pra isso utilizar a tabela ListaMudancasRH, que é a fonte que irei utilizar para atualizar minha tabela Empregado. Eu quero associar as duas utilizando o nome do funcionário. Tendo isso em mente, é fácil entender qual a utilidade do MATCH…
#1 – WHEN MATCHED AND SOURCE.ativo = 1 THEN UPDATE…
Significa: Quando o nome do funcionário coincidir nas duas tabelas (ou seja, fizer o MATCH sucessivamente) e na tabela origem com as novas informações, tiver o ativo =1, significa que o funcionário ainda está empregado. Logo, devo atualizar o cargo na tabela destino utilizando a origem como base
#2 WHEN MATCHED AND SOURCE.ativo = 0 THEN DELETE…
Significa : Quando o nome do funcionário coincidir nas duas tabelas (ou seja, fizer o MATCH sucessivamente) e na tabela origem com as novas informações. tiver o ativo =0, significa que o funcionário está/será desempregado. Logo, devo DELETAR ele na tabela de destino utilizando a origem como base (É importante ressaltar que isso é um exemplo apenas. Em casos reais, dependendo da modelagem dos sistemas que apontam para o banco, registros de pessoas comumente não são deletados, e sim atualizados para algo que indique desuso, que nesse caso poderia ser simplesmente a flag ativo da destino (tabela Empregados) atualizada para 0.
#3 – WHEN NOT MATCHED BY TARGET THEN INSERT…
Significa: Quando o nome do funcionário não coincidir na TARGET, ou seja, na tabela destino, se faz necessária a inserção de um funcionário que não existia antes na tabela de destino. Veja só, aqui inserimos os dados da tabela origem na tabela destino, a de Empregados. —
2) A segunda questão pede que façamos uma tabela de auditoria. Lembram que recomendei o uso da cláusula output? Não foi por acaso. Bem, existem várias formas de se realizar auditoria de um registro, e uma delas é utilizando as famosas “tabelas especiais” deleted e inserted, que são duas tabelas utilizadas pelo SQL Server na execução de tarefas DML (que manipulam dados).
Esse post não pretende cobrir assunto , mas você pode vê-lo aqui e recomendo que o faça caso precise aprender novas possibilidade pra debugging e auditoria… Mas enfim… Cada vez que o código faz um:
DELETE: Internamente, no SQL Server, é salva uma cópia do registro apagado na tabela DELETED.
INSERT: Internamente, no SQL Server é salva uma cópia do registro recém-inserido na tabela INSERTED
UPDATE: Internamente, no SQL Server, é salva o registro na tabela DELETED e insere o novo no INSERTED.
Em resumo, combinando com a cláusula OUTPUT (maiores informações aqui), que a grosso modo é um “SELECT pra registros que estão sendo manipulados em expressões DML”, é possível capturar os registros que o SQL Server joga nas tabelas INSERTED e DELETED e assim sendo, persistir estes dados com o uso de uma cláusula INSERT INTO!
Para praticar, e já considerando que a cláusula MERGE soa mais familiar pra você, leitor, mostro uma nova possibilidade: Realizar o MERGE e manipulando os registros modificados (apagados, atualizados e inseridos).
-- Essa será a nossa tabela de auditoria CREATE TABLE dbo.AuditaEmpregado( Empregado varchar(50) null, dataAlteracao DATETIME DEFAULT GETDATE(), antes varchar(50) null, depois varchar(50) null, nomeEvento VARCHAR(12) ); -- Atribuindo o apelido de T (referência à TARGET) para a tabela dbo.Empregado que é a tabela/set que será "alvo" das mudanças. -- Atribuindo o apelido de S (referência à SOURCE) para a tabela dbo.ListaMudancasRH que é a tabela/set na qual será fonte das mudanças. MERGE dbo.Empregado t USING dbo.ListaMudancasRH as s -- ON (s.nome) = (t.nome) WHEN MATCHED AND s.ativo= 0 THEN DELETE WHEN MATCHED AND s.cargo = t.cargo THEN UPDATE SET cargo = s.cargo WHEN NOT MATCHED BY TARGET THEN INSERT (nome,cargo,ativo) VALUES (s.nome, s.cargo,s.ativo) -- Gera uma saída que será inserida na tabela de auditoria OUTPUT COALESCE(deleted.nome,inserted.nome) as Empregado, GETDATE() AS dataAlteracao, deleted.cargo as antes, inserted.cargo as depois, $ACTION AS nomeEvento -- Tabela já criada anteriormente INTO dbo.AuditaEmpregado ; [code language ="sql"] -- Conferindo os resultados select * from dbo.Empregado select * from dbo.AuditaEmpregado
Informações adicionais… COALESCE compara dois nomes e traz o primeiro resultado não nulo que encontrar. Inevitavelmente o nome do funcionário se encontrará NULO se o registro sofreu operações de DELETE na inserted e encontrará NULO também na deleted se foi feita uma operação de inserção.
No caso de um UPDATE, os dois campos estarão preenchidos, pois uma atualização consiste, internamente para o SQL Server, de uma deleção + inserção. A variável $ACTION guarda internamente qual operação foi realizada naquele registro, e que aqui foi mantida em inglês por motivos de praticidade. Você pode tratar isso com um CASE caso queira registrar um outro nome diferente do retornado ao servidor.
Essa foi a resolução básica! Tem várias outras formas de se resolver o problema, sendo esta apenas uma delas.
Espero que tenha passado alguma informação útil. Caso tenha alguma crítica, correção, ou opinião, sinta-se à vontade para comentar!
[]’s