By: Tim Ford | Updated: 2010-12-13 | Comments (6) | Related: More > T-SQL

Free MSSQLTips Webinar: Development Best Practices for SQL Server

Attend this webinar to learn about development best practices for SQL Server. Andy Warren vai compartilhar seus muitos anos de experiência para dar algumas dicas sobre o que tem trabalhado melhor para ele e como você pode utilizar algum deste conhecimento.

Problema

muitas vezes eu me encontro em situações em que preciso inserir registros em uma tabela em um conjunto a operação com base no disposto dentro de uma transacção em que secundariamente, e dentro da mesma transação, eu spawn-off subsequentes inserções em tabelas relacionadas, onde eu preciso para passar-em valores de chave que foram o resultado da inicial comando INSERT. Graças a um realce Transact/SQL no servidor SQL, isso acabou de se tornar muito mais fácil e pode ser feito em uma única declaração… SEM UM GATILHO!

Solution

uma das melhorias Transact / SQL no servidor SQL da Microsoft é a sub-cláusula de saída da instrução inserir. Você pode agora capturar os registros inseridos através de uma declaração de inserção (pense também ser capaz de capturar os valores da coluna de identidade para as novas linhas) para uso subsequente em uma declaração de inserção adicional para uma tabela de filhos para persistir a integridade referencial sem a necessidade de um gatilho de inserção. porque não usar um gatilho? É uma construção viável e comprovada do servidor SQL, certo?

A resposta curta é ” sim, é.”No entanto, gatilhos são um daqueles pequenos segredos desagradáveis que a base de dados guarda. Eles não saltam para cima de TI e dizem: “aqui estou eu!”Tomemos, por exemplo, o processo de solução de problemas de travamentos ou ajuste de um mal-realizar consulta de um disparador sentado, ao fundo realizar como foi solicitado pode estar causando seus problemas, mas você vai passar por muitas iterações de busca de procedimentos armazenados, e ad-hoc T/SQL de código antes de você, provavelmente, ainda parar para considerar que há um gatilho disparando a modificação de dados linguagem de comandos (DML) – INSERÇÕES, ATUALIZAÇÕES ou EXCLUSÕES que são complemento para o que você está tentando diagnosticar. Associo o uso de gatilhos com o uso de código T/SQL ad-hoc usado na pilha de código de uma aplicação e passado para uma instância de servidor SQL para processamento-práticas para evitar.

é por isso que eu gosto do que eu vejo com a construção inserir-saída. Você tem os benefícios de ser capaz de capturar os valores inseridos que você pode então passar para um comando secundário – e você pode embrulhar tudo isso dentro de uma única transação para atomicidade. A sintaxe para esta construção é mostrada abaixo e difere apenas ligeiramente do comando básico inserir T / SQL:

A única diferença entre este e um padrão de instrução de INSERÇÃO é a inclusão na SAÍDA…Em declaração. Para tornar isso fácil pensar nele como simplesmente uma declaração de inserção secundária dentro da declaração de inserção original que captura os valores na tabela inserida virtualizada – a mesma tabela que um gatilho usaria – para processar uma inserção secundária para outra tabela. No exemplo abaixo, e de acordo com a temporada de férias, vamos dizer que você é responsável por fazer um pouco de contratação nos escritórios corporate AdventureWorks. Um elfo Velho e alegre está sendo contratado para algumas promoções na loja e de acordo com a política corporativa você sempre executa uma revisão de 90 dias para qualquer novo contrato. Queremos que a anotação seja registada quando a nova contratação for inscrita sem qualquer trabalho adicional por parte dos Recursos Humanos. O código abaixo demonstra como podemos usar INSERT-OUTPUT para fazer isso.

USE AdventureWorks;
GO
- - - Create Example Tables
/ *
Note, this is not fully-normalized. Eu teria incluído outra tabela
para tipos de notificação se esta fosse uma solução real.
I would also use an int NotificationTypeID column in Notifications table
instead of a varchar (xx) NotificationType column.
*/
CREATE SCHEMA AUTHORIZATION dbo;
GO
CREATE TABLE .
(
IDENTITY(1,1) NOT NULL,
VARCHAR(30) NOT NULL,
VARCHAR (30) NOT NULL,
CONSTRAINT PRIMARY KEY CLUSTERED
(
ASC
)ON
) ON ;
CREATE TABLE .
(
IDENTITY(1,1) not NULL,
NÃO NULO,
DATETIME not NULL,
VARCHAR(30) not NULL,
RESTRIÇÃO de CHAVE PRIMÁRIA de CLUSTER
(
ASC
)NO
) EM ;

Agora, o que nós construímos os objetos deste pequeno exercício podemos olhar para INSERIR-SAÍDA de construir na ação…

/ *
demonstre como você pode inserir os valores-chave adicionados à equipe.StaffID em notificações.StaffID in single transaction
* /
INSERT INTO HR. Staff (FirstName, LastName )
OUTPUT INSERTED.StaffID, DATEADD(d,90,GETDATE()),'90 Dias de Revisão'
EM RH.Notificação
(
StaffID,
NotificationDate,
NotificationType
)
VALUES ( 'Santa','Noel');

a Seleção a partir de agora, tanto o Pessoal e tabelas de Notificação, você verá que os valores da chave foram inseridas com êxito para ambas as tabelas:

SELECT * FROM RH.Pessoal;
SELECT * FROM RH.Notificação;

Now there is a very important – and quite limiting caveat to using INSERT-OUTPUT. O alvo de saída não pode fazer parte de nenhuma relação chave estrangeira. Mesmo que não haja uma relação em cascata com qualquer outro objeto através dessa relação no banco de dados. Vamos ver o que acontece se for. Adicionaremos uma chave estrangeira à notificação sobre o StaffID, Referenciando a coluna do StaffID na tabela de pessoal e, em seguida, tentar adicionar alguma ajuda adicional de férias:

--Add Foreign Key para StaffID coluna à tabela de Notificações
ALTER TABLE, HR.Notificação ADD CONSTRAINT
CHAVE ESTRANGEIRA
(
StaffID
)
REFERÊNCIAS de RH.Pessoal
(
StaffID
);
/*
Demonstram como você pode inserir os valores de chave adicionado aos Funcionários.StaffID em notificações.StaffID in single transaction
* /
INSERT INTO HR. Staff (FirstName, LastName )
OUTPUT INSERTED.StaffID, DATEADD(d,90,GETDATE()),'90 Dias de Revisão'
EM RH.Notificação
(
StaffID,
NotificationDate,
NotificationType
)
VALUES ( 'Gelado','Boneco de neve");
SELECT * FROM RH.Pessoal;
SELECT * FROM RH.A notificação;


A seguinte mensagem de erro é retornado como o esperado:

Msg 332, NÍVEL de 16, Estado 1, Linha 17
A TABELA de destino 'de RH.Notificação " DA SAÍDA EM cláusula não pode ser EM ambos os lados DE uma CHAVE PRIMÁRIA, CHAVE ESTRANGEIRA) de relacionamento. Foi encontrada a restrição de referência "FK_Notification_Staff".

isto é provavelmente bom neste caso, uma vez que as chances são boas Sr. Snowman não vai estar por perto em 90 dias.

próximas etapas

• leia mais sobre a cláusula de saída
• leia também esta dica anterior sobre a cláusula de saída
• mais dicas do autor estão disponíveis através desta ligação.

Última actualização: 2010-12-13

Sobre o autor

Tim Ford é um Sênior, Administrador de Banco de dados com MindBody.
View all my tips

Related Resources

• More Database Developer Tips…