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:

 

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. 

Comments (5) -

  • Kovács Zsolt

    5/30/2013 7:54:33 PM | Reply

    Ez egész jó pofa a cikk Smile
    Az tetszik, hogy olyan gondolatmenet alapján oldottad meg a feladatot, ami nekem nem jutott volna eszembe.

  • Zoltán Horváth

    6/3/2013 5:43:18 PM | Reply

    Szép trükk, jó ez fejtörőnek is, kösz Jani!

    Amit én tudtam összehozni, az 1-et ad vissza, ha változás volt - és 0-t, ha nem volt. Meg nincs implicit cast - a visszatérési típus is bit. Íme:

    ISNULL(a ^ b, COALESCE(~a|a, ~b|b, 0))

  • Kovács Zsolt

    6/7/2013 8:51:11 PM | Reply

    Én ilyet tudtam összeütni:
    case when a = b or (a is null and b is null) then 0 else 1 end

    Egyszerű és könnyen átlátható, cserébe nem olyan fantáziadús, mint a Ti megoldásaitok.

  • Bécsy Márk

    6/24/2013 9:18:37 AM | Reply

    A COALESCE(@bit, -1) viszont működik, mert a visszaadott érték int lesz, vagyis -1. Az én megoldásom se túl dörzsölt, viszont rövid Smile

  • Berke János

    6/24/2013 9:25:03 AM | Reply

    Azt hiszem Márké eddig a legegyszerűbb megoldás Smile

Add comment