Piemērs: DML trigeris audita ierakstiem

Piemērā izveidota testa tabula un audit tabula, kurā tiek reģistrēts izmaiņu veicējs (kopā ar citu informāciju). Parādīta Inserted un Deleted tabulu izmantošana praksē.

Tabulu izveides skripti:

Create Table dbo.TestTable
(
   TestTableID int Identity primary key,
   Skaitlis int,
   TrisBurti char(3),
   MainigsGarums NVarChar(20),
   GarsTeksts NVarChar(100)
)
Go
Create Table dbo.AuditTable
(
    ID int primary key identity,
    MDate DateTime default GetDate(),
    MUser nvarchar(50) default suser_sname(),
    MAffectedRows int,
    MAction char(1)
)

Trigera izveide (iekrāsotas daļas, par ko pēc skripta apraksts):

Create Trigger tr_TestTable_AuditIUD On dbo.TestTable
After Insert, Update, Delete
As
    Set NoCount On;
   
    declare @ins as int;
    declare @del as int;
    Select @ins = Count(*) From Inserted;
    Select @del = Count(*) From Deleted;
   
    Insert Into dbo.AuditTable(MAction, MAffectedRows)
        Values (Case When @ins > 0 And @del > 0 Then 'U'
                    When @ins > 0 Then 'I'
                    Else 'D'
                 End, Case When @ins > 0 Then @ins Else @del End)

tr_TestTable_AuditIUD - vēlams trigera nosaukumu rūpīgi izvēlēties, lai neskatoties dziļāk būtu skaidrs, ko trigeris apmēram dara.

After - ir divu veidu trigeri "Pēc" un "Tā vietā". Šajā gadījumā "Pēc" trigeris ir vispiemērotākais. Plašāk rakstā DML trigeri

Set NoCount On - veicot DML darbības, pēc komandas izpildes tiek atgriezts, cik rindas tika modificētas. Ar šo komandu panākam, ka netiek izvadīts rindu skaits, kas mainīts trigera ietvaros!

Case When @ins > 0 Then @ins Else @del End - jocīga konstrukcija, bet ideja vienkārša- gan Insert, gan Update komandas gadījumos rindu skaitu var iegūt, saskaitot cik rindu ir Inserted tabulā.

Trigera testēšanai skripti:

-- Pārliecināmies, ka tukšs           
Select * From dbo.TestTable
Select * From dbo.AuditTable

-- Ievieto datus
Insert Into dbo.TestTable(Skaitlis) Values (1)
Insert Into dbo.TestTable(Skaitlis) Values (2)
Insert Into dbo.TestTable(Skaitlis) Values (3)
-- Apskatamies, kā izskatās
Select * From dbo.TestTable
Select * From dbo.AuditTable

-- Pamēģinām pamainīt kaut ko..
Update dbo.TestTable Set Skaitlis = -1 Where TestTableID = 2
Update dbo.TestTable Set MainigsGarums = 'Sveiki!'
-- Atkal apskatamies, kā izskatās
Select * From dbo.TestTable
Select * From dbo.AuditTable

-- Izdzēšam kaudzīti rindiņu
Delete dbo.TestTable Where TestTableID < 3
-- Un atkal apskatamies, kā izskatās
Select * From dbo.TestTable
Select * From dbo.AuditTable

Pēc izpildes audita tabulā izskatās šādi:

Pēc sevis satīram:

Drop Table dbo.TestTable
Drop Table dbo.AuditTable