Resposta do Desafio T-SQL #2 – MERGE e OUTPUT

BIRD

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  ;


-- 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

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s