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 vil dele sine mange års erfaring for at give nogle tip til, hvad der har fungeret bedst for ham, og hvordan du kan bruge noget af denne viden.
Problem
ofte befinder jeg mig i situationer, hvor jeg har brug for at indsætte poster i en tabel i en sætbaseret operation indpakket inde i en transaktion, hvor jeg sekundært og inden for den samme transaktion gyder efterfølgende indsatser i relaterede tabeller, hvor jeg skal indtaste nøgleværdier, der var resultatet af den oprindelige Indsæt kommando. Takket være en Transact/
løsning
en af transaktions-/KVL-forbedringerne i Microsoft er OUTPUTUNDERKLAUSULEN i Indsæt-sætningen. Du kan nu fange de poster, der er indsat via en INSERT-sætning (tænk også at kunne fange IDENTITETSKOLONNEVÆRDIER for de nye rækker) til efterfølgende brug i en ekstra INSERT-sætning for en underordnet tabel for at fortsætte referentiel integritet uden behov for en INSERT-trigger.
hvorfor ikke bare bruge en trigger? Det er en levedygtig og gennemprøvet konstruktion af en Server, ikke?
det korte svar er ” ja, det er.”Udløsere er dog en af de grimme små hemmeligheder, som databasen holder. De hopper ikke bare lige ud på dig og siger “Her er jeg!”Tag for eksempel fejlfindingsprocessen med deadlocks eller tuning af en dårligt udført forespørgsel-en trigger, der sidder i baggrunden, der udfører, som den er blevet bedt om, kan forårsage dine problemer, men du vil gennemgå mange gentagelser af søgning i lagrede procedurer og ad hoc – kode, før du sandsynligvis endda stopper for at overveje, at der er en trigger, der affyrer dataændringssprogkommandoer (DML)-indsætter, opdaterer eller sletter, der er et supplement til det, du prøver at diagnosticere. Jeg forbinder brugen af triggere med brugen af ad hoc t/kvm-kode, der bruges i en applikations kodestak og overføres til en CCL – Serverinstans til behandling-praksis at vige væk fra.
derfor kan jeg godt lide det, jeg ser med INSERT-OUTPUT-konstruktionen. Du får fordelene ved at kunne fange de indsatte værdier, som du derefter kan overføre til en sekundær kommando – og du kan pakke alt dette ind i en enkelt transaktion for atomicity. Syntaksen for denne konstruktion er vist nedenfor og adskiller sig kun lidt fra den grundlæggende indsæt t/:
INSERT INTO SOME_TABLE>
(
column_list>
)
OUTPUT INSERTED.identity_column> - og andre kolonner fra SOME_TABLE om nødvendigt
ind i SOME_OTHER_TABLE>
(
column_list>
)
Vælg
(
column_list>
)
Vælg
(
column_list>
)
fra source_table_or_join_of_multiple_tables>
hvor filtrering_kriterier >
den eneste forskellen mellem dette og en standard insert-erklæring er inkluderingen på output…Til erklæring. For at gøre det nemt, tænk på det som blot en sekundær indsæt sætning inde i den oprindelige indsæt sætning, der fanger værdierne i den virtualiserede indsatte tabel – den samme tabel, som en udløser ville bruge – til at behandle en sekundær indsats til en anden tabel. I eksemplet nedenfor, og i overensstemmelse med feriesæsonen, lad os sige, at du er ansvarlig for at ansætte lidt på virksomhedens Eventyrværkskontorer. En ret-jolly gammel elf bliver hyret til nogle kampagner i butikken, og i overensstemmelse med virksomhedspolitikken udfører du altid en 90 dages gennemgang for nye ansættelser. Vi ønsker at få notationen registreret, når den nye leje indtastes uden yderligere arbejde fra menneskelige ressourcer. Koden nedenfor viser, hvordan vi kan bruge INSERT-OUTPUT til at gøre dette.
brug eventyrværker;
Gå
---Opret eksempel tabeller
/*
Bemærk, Dette er ikke fuldt normaliseret. Jeg ville have medtaget en anden tabel
For meddelelsestyper, hvis dette var en egentlig løsning.
Jeg vil også bruge en int NotificationTypeID-kolonne i Meddelelsestabellen
i stedet for en varchar-meddelelsestype-kolonne.
*/
Opret skema autorisation dbo;
Gå
Opret tabel .
(
identitet (1,1) ikke NULL,
VARCHAR(30) ikke NULL,
VARCHAR(30)ikke NULL,
begrænsning primær nøgle grupperet
(
ASC
) på
) På ;
Opret tabel .
(
identitet (1,1) ikke NULL,
ikke NULL,
DATETIME ikke NULL,
VARCHAR(30)ikke NULL,
begrænsning primær nøgle grupperet
(
ASC
) på
) på ;
nu hvor vi har bygget objekterne til denne lille øvelse, kan vi se på INSERT-OUTPUT-konstruktionen i aktion…
/ *
Demonstrer, hvordan du kan indsætte de nøgleværdier, der er tilføjet til personalet.StaffID
I meddelelser.StaffID i enkelt transaktion
*/
indsæt i HR. Staff(fornavn, efternavn)
OUTPUT indsat.StaffID, DATEADD(d,90,GETDATE()),'90-dages gennemgang'
til HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
værdier ( 'Santa','Claus'); Hvis du vælger nu fra både Personale-og Meddelelsestabellerne, kan du se, at nøgleværdierne blev indtastet i begge tabeller:
vælg * fra hr.staff;
vælg * fra hr.notifikation; 
nu er der en meget vigtig – og ganske begrænsende advarsel til at bruge INSERT-OUTPUT. Outputmålet kan ikke være en del af et fremmed nøgleforhold. Selvom der ikke er noget cascading forhold til noget andet objekt via dette forhold i databasen. Lad os se på, hvad der sker, hvis det er. Vi tilføjer en fremmed nøgle til meddelelsen på StaffID, henviser til staffid-kolonnen i Personaletabellen og prøver derefter at tilføje yderligere feriehjælp:
--Tilføj fremmed nøgle til staffid-kolonne til Meddelelsestabellen
ALTER TABLE HR.Notification Tilføj begrænsning
fremmed nøgle
(
StaffID
)
referencer HR.Staff
(
StaffID
);
/ *
Demonstrer, hvordan du kan indsætte de nøgleværdier, der er tilføjet til personalet.StaffID
I meddelelser.StaffID i enkelt transaktion
*/
indsæt i HR. Staff(fornavn, efternavn)
OUTPUT indsat.StaffID, DATEADD (d, 90, GETDATE ()),'90-dages gennemgang'
til HR. notifikation
(
StaffID,
NotificationDate,
NotificationType
)
værdier ( 'Frosty', 'snemand');
Vælg * fra HR. Staff;
Vælg * fra HR.Notification;
følgende fejlmeddelelse returneres som forventet:
Msg 332, niveau 16, tilstand 1, linje 17
måltabellen 'HR.Notification' af OUTPUT i klausul kan ikke være på begge sider af et (primær nøgle, fremmed nøgle) forhold. Fundet referencebegrænsning 'FK_Notification_Staff'. Dette er nok godt i dette tilfælde, da chancerne er gode Mr. Snemand kommer ikke til at være omkring i 90 dage.
næste trin
- Læs mere om OUTPUTKLAUSULEN
- Læs også dette forrige tip om OUTPUTKLAUSULEN
- flere tip fra forfatteren er tilgængelige via dette link.
sidst opdateret: 2010-12-13
om forfatteren
Tim Ford er en senior databaseadministrator med MINDBODY.Se alle mine tips
- flere Database Developer Tips…