A leggyakoribb auditálási módszer az un. shadow table adatváltozás követése esetében. Ez azt jelenti, hogy van egy T1 táblám, akkor készítek egy (nagyrészt) megegyező táblát T1_Audit néven és egy trigger segítségével átrakom a változásokat. Most tekintsünk el a T1 és a T1_Audit táblák kialakításától, ami igazán érdekes, hogy egy nullable bit típusú oszloppal mit tudok kezdeni.
A bit típus alapjában 3 állapotot tud kezelni: NULL, 0, 1. Igen ám, de egy audit során meg kell tudnom mondani, hogy egy érték miről-mire változott. Oké, ez eddig rendben is van, de mi van akkor ha NULL értékről 0-ra vagy 1-re változik és vissza? Jöhet az ISNULL? Persze, csak nem lesz jó: milyen értéket vegyen fel, ha a NULL értéken kívül 2 értéket tud felvenni csak? Hát persze: bármi mást, mint az a 2 érték - 0 vagy 1. hmm... mostmár remélem teljes a zürzavar :-). Lényegében a bit típus egy olyan integer, ami csak a fentebb említett 3 értéket veheti fel, de senki nem akadályozza meg, hogy egy WHERE feltételben ez más értékeket vegyen fel az egyenlőtlenség vizsgálat során.
Lássuk ezt egy konkrét példán: egyenlőtlenséget kell vizsgálnunk az alábbi esetekre.
- NULL <> NULL
- NULL <> 0
- NULL <> 1
- 0 <> 0
- 0 <> 1
- 1 <> 0
- 1 <> 1
- 0 <> NULL
- 1 <> NULL
A NULL értéket tartalmazó sorokra se nem lesz igaz se nem lesz hamis az egyelőtlenség, Így próbálkozzunk az ISNULL értékkel. Már fentebb feszegettem, hogy mi legyen a NULL értéket helyettesítendő. Legyen 0 vagy 1? Lássuk csak:
declare @t as table
(
a bit null,
b bit null
)
insert into @t values
(null, null),
(null, 0),
(null, 1),
(0, 0),
(0, 1),
(1, 0),
(1, 1),
(0, null),
(1, null)
--hibás eredmény
select
a,
b,
ISNULL(a, -1),
ISNULL(b, -1),
CASE
WHEN ISNULL(a, -2) <> ISNULL(b, -2) THEN 'nem egyenlo'
ELSE 'egyenlo' END AS [egyenloseg]
from
@t
Ennek az eredménye sajnos nem lesz jó. Az ISNULL esetén, ha NULL értékem lesz, sajnos nem -1, hanem 1 lesz az érték. Pedig milyen jó is lett volna, de azért nem sokkal bonyolúltabb a megoldás:
declare @t as table
(
a bit null,
b bit null
)
insert into @t values
(null, null),
(null, 0),
(null, 1),
(0, 0),
(0, 1),
(1, 0),
(1, 1),
(0, null),
(1, null)
--helyes megoldas
select
a,
b,
ISNULL(a-2,0)+2,
ISNULL(b-2,0)+2,
CASE
WHEN ISNULL(a-2, 0)+2 <> ISNULL(b-2, 0)+2 THEN 'nem egyenlo'
ELSE 'egyenlo' END AS [egyenloseg]
from
@t
A fenti két lekérdezést azért az ANSI_NULLS beállításoktól függően egyszerűbben is meg lehet írni. Pl. az ANSI_NULLS OFF esetén NULL = NULL, de ez már más lapra tartozik ;-).
Okés, lehet mondani, hogy ott a Change Data Capture, meg a Change Tracking, de sajnos egyik sem tudja a felhasználó nevét auditálni, amikor az egy service account-tal jön be az adatbázisba - impersonation, ilyenkor mindig a service account felhasználó neve lesz benne a CDC táblákban és nem az aki ténylegesen csinált valamit a UI-on. Ilyen eset lehet például egy SharePoint lista auditálása is.