T-SQL Output klauza

Rakstā DML trigeri tika aprakstītas divas tabulas- Inserted un Deleted, kas pieejamas trigera iekšienē. Tās ļoti noder, piemēram, kad nepieciešams salīdzināt tabulā mainīto datu iepriekšējās un jaunās vērtības. Šīs tabulas ir pieejamas arī lietošanai kopā ar Output klauzu.

Output klauzu izmantoju gadījumā, kad ir vajadzība apskatīt izmaiņas, kas tiek veiktas datu bāzē (Insert, Update, Delete komandas + arī Merge komanda sākot no SQL Server 2008).

Piemēra nolūkos izveidošu un turpmāk izmantošu temporālu tabulu:

Create Table #TestTable
(   
    TestTableID Int Primary Key identity,
    Skaitlis int,
    Datums datetime default getdate()
)
Insert Into #TestTable (Skaitlis) values (1)
Insert Into #TestTable (Skaitlis) values (2)

Output klauza Insert, Delete un Update komandās
1. piemērs:

Insert Into #TestTable (Skaitlis) Output Inserted.Skaitlis Values (3)

Iegūtais rezultāts:

2. piemērs:
Var iegūt arī to kolonnu vērtības, kas netiek Insert aiztiktas:

Insert Into #TestTable (Skaitlis)   
Output Inserted.TestTableID, Inserted.Skaitlis, Inserted.Datums
Values (4)

Iegūstam rezultātu (jau ģenerēts TestTableID):

3. piemērs:
Tieši tāpat kā Insert komandā, var izmantot arī Delete (pieejama Deleted tabula) Update (pieejamas Inserted un Deleted tabulas):

Update #TestTable
    Set Skaitlis = Skaitlis + 1
Output Deleted.*, Inserted.*

Rezultātā tiek izvadītas gan iepriekšējaš vērtības, gan jaunās vērtības:

Līdzīgu piemēru varētu izveidot arī Delete komandai (Output klauzā Delete komandai nav pieejama Inserted tabula).

Kuras rindas mainītas?
Output klauza ir noderīga arī gadījumos, ja vēlamies uzzināt Update/Delete komandas mainīto rindu primārās atslēgas vērtības, sevišķi ja rindas mainītas balstoties uz citu lauku vērtībām vaicājuma Where daļā.

Piemēram, ja nepieciešams atlasīt rindas, ko mainījis šāds vaicājums:

Update #TestTable
    Set Skaitlis = Skaitlis + Rand() * 30
Where Skaitlis > 3

Vaicājumu vajag uzrakstīt šādi:

Update #TestTable
    Set Skaitlis = Skaitlis + Rand() * 30
    Output Inserted.*
Where Skaitlis > 3

(Var no sākuma datus atlasīt, tad mainīt, tad veidot Select vaicājumu.. bet kāpēc darīt lieku?)

Output klauza izvadot rezultātus citā tabulā / tabulas mainīgajā
Rezultātus var izvadīt arī tabulā vai tabulas mainīgajā.
Piemērs ar datu izvadīšanu citā tabulā: nepieciešams reģistrēt kas, kad un kādus datus ievietojis tabulā (trigeris būtu labāks, ja nepieciešams reģistrēt pilnīgi visas Insert komandas. Output, savukārt, būtu piemērotāks, ja vēlamies reģistrēt tikai konkrētās komandas rezultātus). Piemēra nolūkam izveidošu temporālu tabulu un ierakstīšu tajā datus:

Create Table #AuditTable
(
    AuditTableID Int Primary Key identity,
    Datums datetime default getdate(),
    Lietotajs nvarchar(50) default suser_sname(),
    SkaitlisIevietots int,
);

Insert Into #TestTable (Skaitlis)
Output Inserted.Skaitlis Into #AuditTable(SkaitlisIevietots)
values (4);

Select * From #AuditTable

Piemērs ar datu izvadīšanu tabulas mainīgajā: ja nepieciešams uzzināt, kādas ir ģenerētās surogāt-atslēgas vērtības:

Declare @SurogatatslegasVertibas Table(TestTableID Int)

Insert Into #TestTable (Skaitlis)
    Output Inserted.TestTableID Into @SurogatatslegasVertibas(TestTableID)
    Select Object_ID from Sys.Objects

Select * From @SurogatatslegasVertibas