nullable bit típus auditálása

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:

 1declare @t as table
 2(
 3    a bit null,
 4    b bit null
 5)
 6 
 7insert into @t values
 8(null, null),
 9(null, 0),
10(null, 1),
11(0, 0),
12(0, 1),
13(1, 0),
14(1, 1),
15(0, null),
16(1, null)
17 
18--hibás eredmény
19select
20    a,
21    b,
22    ISNULL(a, -1),
23    ISNULL(b, -1),
24    CASE
25        WHEN ISNULL(a, -2) <> ISNULL(b, -2)   THEN 'nem egyenlo'
26        ELSE 'egyenlo' END AS [egyenloseg]
27from
28    @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:

 1declare @t as table
 2(
 3    a bit null,
 4    b bit null
 5)
 6 
 7insert into @t values
 8(null, null),
 9(null, 0),
10(null, 1),
11(0, 0),
12(0, 1),
13(1, 0),
14(1, 1),
15(0, null),
16(1, null)
17 
18--helyes megoldas
19select
20    a,
21    b,
22    ISNULL(a-2,0)+2,
23    ISNULL(b-2,0)+2,
24    CASE
25        WHEN ISNULL(a-2, 0)+2 <> ISNULL(b-2, 0)+2 THEN 'nem egyenlo'
26        ELSE 'egyenlo' END AS [egyenloseg]
27from
28    @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.