Articles tagged with: INSERTED

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

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

Desafio #2 T-SQL – MERGE e OUTPUT

Merge

MERGE é uma cláusula sensacional que foi implementada no SQL Server 2008. Pouca gente usa, e eu imagino que seja pela complexidade de declaração se comparando com outros comandos DML (Como por exemplo, DELETE, UPDATE e INSERT).

Leia mais sobre o comando aqui

Devo lembrar que a prova 70-461 vai cobrar esse conhecimento e provavelmente em mais de uma questão (assim como aconteceu na 70-433) e é bastante interessante que o conhecimento da mesma seja algo natural e intuitivo, além de ser um ótimo modo de se pensar/programar DML. Qual a forma que encontrei falar sobre a cláusula? Bem, claro, com um desafio! Vamos ao Desafio T-SQL #2 РMERGE e OUTPUT

Case

A empresa Trupe123 resolve, depois de anos sem dar aumento para seus funcionários, tomar uma atitude. Muito foi discutido sobre plano de crescimento, meritocracia, etc e depois de muita discussão, polêmica e briga. Como várias pessoas também saíram da empresa em um curto período de tempo, o RH aproveitou o momento para realizar mudanças no quadro de funcionários (acrescentando e/ou retirando profissionais) ou alterando cargos.O responsável pelo RH, chamado Pedro Bial entregou a você, o DBA, uma lista de mudanças que devem ser refletidas na tabela de empregados.

Com o material necess√°rio pronto para fazer o seu trabalho, algumas instru√ß√Ķes lhe s√£o impostas: O pedido do RH foi bastante claro: associar pelo nome que j√° existe no sistema.

1) Quem estiver com ativo = 0, foi desligado. Logo, deve-se apagar da tabela de Empregados quem foi desligado.

2) O Profissional que estiver na lista de Mudanças do RH mas não estiver na tabela de Empregados deve ser incluído.

3) Pode ter havido alguma alteração de cargo para quem não foi desligado. O valor que deve permanecer é o que vem da lista de Mudanças do RH, desde que o funcionário exista tanto na tabela como na Lista do RH.

4) O profissional que estiver na tabela de Empregados mas não estiver na Lista de Mudanças deve ser mantido como está.

Agora, como dizia Bane: LET THE GAMES BEGIN! O desafio, composto de duas etapas:

1) Construa uma instrução MERGE que esteja dentro do pedido do Pedro Bial, do RH;

2) Construa uma tabela de auditoria tempor√°ria que ir√° receber:

a) O nome do funcionário envolvido na atualização;

b) O nome do evento (INCLUSÃO, EXCLUSÃO OU ATUALIZAÇÃO) seguido do valor antigo e do valor novo, se necessário;

c) Um campo de DATA que informe DATA E HOR√ĀRIO de quando o registro foi inclu√≠do na tabela de auditoria; PS: Para o desafio de no.2, sinta-se a vontade para recriar toda a estrutura e adapte a sua estrutura MERGE utilizando a cl√°usula OUTPUT.

Outra coisa: como voc√™ provavelmente vai testar bastante tais comandos, vale a pena abrir algumas transa√ß√Ķes e ver o resultado por l√° mesmo. √Č isso. Na pr√≥xima postagem volto com o resultado. Provavelmente final de semana. Novamente, obrigado pela sua visita e sinta-se a vontade.