Using INSERT OUTPUT in a SQL Server Transaction
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 condividerà i suoi molti anni di esperienza per dare alcune indicazioni su ciò che ha funzionato meglio per lui e come è possibile utilizzare alcune di queste conoscenze.
Problema
Spesso mi trovo in situazioni in cui ho bisogno di inserire dei record in una tabella di un set di base di funzionamento avvolto all’interno di una transazione in cui secondariamente, e all’interno della stessa transazione, ho spawn-off successivo inserimento in tabelle correlate in cui ho bisogno di passare tasto di valori che sono stati il risultato iniziale di comando INSERT. Grazie a un miglioramento Transact / SQL in SQL Server, questo è diventato molto più semplice e può essere fatto in una singola istruzione… SENZA UN GRILLETTO!
Soluzione
Uno dei miglioramenti Transact / SQL in Microsoft SQL Server è la sotto-clausola di OUTPUT dell’istruzione INSERT. È ora possibile acquisire i record inseriti tramite un’istruzione INSERT (si pensi anche di essere in grado di acquisire i valori delle colonne di IDENTITÀ per le nuove righe) per l’uso successivo in un’istruzione INSERT aggiuntiva affinché una tabella figlio mantenga l’integrità referenziale senza la necessità di un trigger INSERT.
Perché non usare semplicemente un trigger? È un costrutto praticabile e collaudato di SQL Server, giusto?
La risposta breve è ” Sì, lo è.”Tuttavia, i trigger sono uno di quei piccoli segreti cattivi che il database mantiene. Non si limitano a saltare a destra fuori a voi e dire ” ECCOMI!”Prendiamo ad esempio il processo di risoluzione dei problemi di deadlock o l’ottimizzazione di un mal di esecuzione di query – un trigger di seduta in background in esecuzione di quanto è stato chiesto di potrebbe causare problemi, ma si sta andando a passare attraverso molte iterazioni di ricerca stored procedure e ad-hoc T/SQL codice prima probabilmente anche smettere di considerare che c’è un’attivazione dei trigger off di modifica dei dati lingua dei comandi (DML) – operazioni di inserimento, aggiornamento o eliminazione aggiunta a quello che stai cercando di diagnosticare. Associo l’uso di trigger con l’uso di codice T/SQL ad hoc utilizzato nello stack di codice di un’applicazione e passato a un’istanza di SQL Server per le pratiche di elaborazione da cui rifuggire.
Ecco perché mi piace quello che vedo con il costrutto INSERT-OUTPUT. Ottieni i vantaggi di essere in grado di catturare i valori inseriti che puoi quindi passare a un comando secondario – e puoi racchiudere tutto all’interno di una singola transazione per atomicità. La sintassi per questo costrutto è mostrata di seguito e differisce solo leggermente dal comando INSERT T/SQL di base:
INSERT INTO SOME_TABLE>
(
column_list>
)
OUTPUT INSERTED.identity_column> --e altre colonne SOME_TABLE se necessario
IN SOME_OTHER_TABLE>
(
column_list>
SELEZIONARE
(
column_list>
)
DA source_table_OR_JOIN_of_multiple_tables>
DOVE filtering_criteria>
L’unica differenza tra questo e un INSERTO standard istruzione è l’inclusione in USCITA…IN dichiarazione. Per rendere questo facile pensarlo come semplicemente un’istruzione INSERT secondaria all’interno dell’istruzione INSERT originale che cattura i valori nella tabella INSERITA virtualizzata – la stessa tabella utilizzata da un trigger – per elaborare un INSERT secondario in un’altra tabella. Nell’esempio seguente, e in linea con la stagione delle vacanze, diciamo che sei responsabile di fare un po ‘ di assunzione presso gli uffici aziendali AdventureWorks. Un vecchio elfo giusto-jolly viene assunto per alcune promozioni in negozio e in linea con la politica aziendale si esegue sempre una revisione di 90 giorni per eventuali nuove assunzioni. Vogliamo che la notificazione sia registrata quando viene inserito il nuovo noleggio senza alcun lavoro aggiuntivo da parte delle Risorse umane. Il codice seguente dimostra come possiamo usare INSERT-OUTPUT per farlo.
USA AdventureWorks;
VAI
---Crea tabelle di esempio
/ *
Nota, questo non è completamente normalizzato. Avrei incluso un'altra tabella
per i tipi di notifica se questa fosse una soluzione reale.
Vorrei anche usare una colonna int NotificationTypeID nella tabella delle notifiche
invece di una colonna varchar(xx) NotificationType.
*/
CREA AUTORIZZAZIONE SCHEMA dbo;
VAI
CREA TABELLA .
(
IDENTITY(1,1) NOT NULL,
VARCHAR(30) NOT NULL,
VARCHAR(30) NOT NULL,
VINCOLO CHIAVE PRIMARIA CLUSTER
(
ASC
)ON
) ON;
CREA TABELLA .
(
IDENTITY(1,1) NOT NULL,
NOT NULL,
DATETIME NOT NULL,
VARCHAR(30) NOT NULL,
CONSTRAINT PRIMARY KEY CLUSTERED
(
ASC
)ON
) ON; Ora che abbiamo costruito gli oggetti per questo piccolo esercizio possiamo guardare il costrutto INSERT-OUTPUT in azione…
/ *
Dimostrare come è possibile inserire i valori chiave aggiunti al Rigo.StaffID
nelle notifiche.StaffID in singola transazione
*/
INSERISCI IN HR. Staff(Nome, COGNOME)
OUTPUT INSERITO.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
IN HR.Notifica
(
StaffID,
NotificationDate,
NotificationType
VALUES ( 'Santa','Natale');
Selezionare ora sia il Personale e le tabelle di Notifica vedrai che i valori della chiave sono entrato con successo in entrambe le tabelle:
SELECT * FROM HR.Personale;
SELECT * FROM HR.Notifica; 
Ora c’è un avvertimento molto importante e piuttosto limitante all’utilizzo di INSERT – OUTPUT. La destinazione di output non può far parte di alcuna relazione di chiave esterna. Anche se non esiste una relazione a cascata con qualsiasi altro oggetto tramite tale relazione nel database. Diamo un’occhiata a cosa succede se lo è. Aggiungeremo una chiave esterna alla notifica su StaffID, facendo riferimento alla colonna StaffID nella tabella Staff e quindi proveremo ad aggiungere un ulteriore aiuto per le vacanze:
Add Add Foreign Key for StaffID column to Notifications table
ALTER TABLE HR.Notification ADD CONSTRAINT
FOREIGN KEY
(
StaffID
)
REFERENCES HR.Staff
(
StaffID
);
/ *
Dimostra come puoi inserire i valori chiave aggiunti al Rigo.StaffID
nelle notifiche.StaffID in singola transazione
*/
INSERISCI IN HR. Staff(Nome, COGNOME)
OUTPUT INSERITO.StaffID, DATEADD (d,90,GETDATE ()),'90 giorni di revisione'
IN HR. Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALORI ('Frosty','Snowman');
SELECT * FROM HR.Staff;
SELECT * FROM HR.Notification;
Il seguente messaggio di errore viene restituito come previsto:
Msg 332, LEVEL 16, State 1, Line 17
La TABELLA di destinazione 'HR.Notification' DELLA clausola OUTPUT INTO non può trovarsi SU entrambi i lati DI una relazione (CHIAVE PRIMARIA, CHIAVE ESTERNA). Trovato VINCOLO di riferimento 'FK_Notification_Staff'. Questo è probabilmente buono in questo caso dal momento che le probabilità sono buone Mr. Snowman non sarà in giro in 90 giorni.
Passi successivi
- Leggi di più sulla clausola OUTPUT
- Leggi anche questo precedente suggerimento sulla clausola OUTPUT
- Ulteriori suggerimenti dell’autore sono disponibili tramite questo link.
Ultimo Aggiornamento: 2010-12-13
Circa l’autore
Tim Ford è un Senior Amministratore di Database con MindBody.Visualizza tutti i miei suggerimenti
- Altri suggerimenti per gli sviluppatori di database…