Przekazywanie informacji o tym, kto usunął rekord do wyzwalacza Usuń

Podczas konfigurowania ścieżki audytu nie mam problemu ze śledzeniem, kto aktualizuje lub wstawia rekordy do tabeli, jednak śledzenie, kto usuwa rekordy, wydaje się bardziej problematyczne.

Mogę śledzić Wstawki / Aktualizacje, umieszczając w polu Wstaw / Aktualizuj pole „Zaktualizowany przez”. Dzięki temu wyzwalacz INSERT / UPDATE ma dostęp do pola „Zaktualizowany przez” za pośrednictwem inserted.UpdatedBy. Jednak w przypadku wyzwalacza Usuń żadne dane nie są wstawiane / aktualizowane. Czy istnieje sposób na przekazanie informacji do wyzwalacza usuwania, aby mógł wiedzieć, kto usunął rekord?

Oto wyzwalacz Wstaw / Aktualizuj

ALTER TRIGGER [dbo].[trg_MyTable_InsertUpdate] 
ON [dbo].[MyTable]
FOR INSERT, UPDATE
AS  

INSERT INTO AuditTable (IdOfRecordedAffected, UserWhoMadeChanges) 
VALUES (inserted.ID, inserted.LastUpdatedBy)
FROM inserted 

Korzystanie z SQL Server 2012

Czy istnieje sposób na przekazanie informacji do wyzwalacza usuwania, aby mógł wiedzieć, kto usunął rekord?

Tak: używając bardzo fajnej (i niewykorzystanej funkcji) o nazwie CONTEXT_INFO. Zasadniczo jest to pamięć sesji, która istnieje we wszystkich zakresach i nie jest związana transakcjami. Może być używany do przekazywania informacji (dowolnych informacji - cóż, które mieszczą się w ograniczonej przestrzeni) do wyzwalaczy, a także w obie strony między wywołaniami sub-proc / EXEC. Użyłem go już wcześniej w tej samej sytuacji.

• Informacje kontekstowe to VARBINARY (128)

• Ustaw za pomocą: SET CONTEXT_INFO

• Uzyskaj przez: CONTEXT_INFO ()

Przetestuj poniższe, aby zobaczyć, jak to działa. Zauważ, że przechodzę CHAR(128)na CONVERT(VARBINARY(128), ... Ma to na celu wymuszenie wypełniania pustego pola, aby ułatwić powrót do niego VARCHARpo wyjściu, CONTEXT_INFO()ponieważ VARBINARY(128)jest on odpowiednio wypełniony za pomocą 0x00s.

SELECT CONTEXT_INFO();
-- Initially = NULL

DECLARE @EncodedUser VARBINARY(128);
SET @EncodedUser = CONVERT(VARBINARY(128),
                            CONVERT(CHAR(128), 'I deleted ALL your records! HA HA!')
                          );
SET CONTEXT_INFO @EncodedUser;

SELECT CONTEXT_INFO() AS [RawContextInfo],
       RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())) AS [DecodedUser];

Wyniki:

0x492064656C6574656420414C4C20796F7572207265636F7264732120484120484121202020202020...
I deleted ALL your records! HA HA!

KŁADĄC WSZYSTKO RAZEM:

1. Aplikacja powinna wywołać procedurę przechowywaną „Usuń”, która przechodzi w nazwę użytkownika (lub cokolwiek innego), która usuwa rekord. Zakładam, że jest to już używany model, ponieważ wygląda na to, że śledzisz już operacje wstawiania i aktualizacji.

2. Procedura przechowywana „Usuń”:

   DECLARE @EncodedUser VARBINARY(128);
   SET @EncodedUser = CONVERT(VARBINARY(128),
                               CONVERT(CHAR(128), @UserName)
                             );
   SET CONTEXT_INFO @EncodedUser;
   
   -- DELETE STUFF HERE

3. Wyzwalacz kontroli:

   -- Set the INT value in LEFT (currently 50) to the max size of [UserWhoMadeChanges]
   INSERT INTO AuditTable (IdOfRecordedAffected, UserWhoMadeChanges) 
      SELECT del.ID, COALESCE(
                        LEFT(RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())), 50),
                        '<unknown>')
      FROM DELETED del;

4. Należy pamiętać, że jak zauważył @SeanGallardy w komentarzu, z powodu innych procedur i / lub zapytań ad hoc usuwających rekordy z tej tabeli, możliwe jest, że:

   • CONTEXT_INFOnie został ustawiony i nadal jest NULL:

     Z tego powodu zaktualizowałem powyższe, INSERT INTO AuditTableaby użyć COALESCEdomyślnej wartości. Lub, jeśli nie chcesz wartości domyślnej i potrzebujesz nazwy, możesz zrobić coś podobnego do:

     DECLARE @UserName VARCHAR(50); -- set to the size of AuditTable.[UserWhoMadeChanges]
     SET @UserName = LEFT(RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())), 50);
     
     IF (@UserName IS NULL)
     BEGIN
        ROLLBACK TRAN; -- cancel the DELETE operation
        RAISERROR('Please set UserName via "SET CONTEXT_INFO.." and try again.', 16 ,1);
     END;
     
     -- use @UserName in the INSERT...SELECT

   • CONTEXT_INFOzostała ustawiona na wartość, która nie jest prawidłową nazwą użytkownika, a zatem może przekraczać rozmiar AuditTable.[UserWhoMadeChanges]pola:

     Z tego powodu dodałem LEFTfunkcję zapewniającą, że cokolwiek zostanie złapane, CONTEXT_INFOnie złamie INSERT. Jak zaznaczono w kodzie, wystarczy ustawić 50rzeczywisty rozmiar UserWhoMadeChangespola.

AKTUALIZACJA DLA SQL Server 2016 i nowszych

SQL Server 2016 dodał ulepszoną wersję tej pamięci na sesję: kontekst sesji. Nowy kontekst sesji jest w zasadzie tabelą skrótów par klucz-wartość z „kluczem” będącym typem sysname(tj. NVARCHAR(128)) I „wartością” SQL_VARIANT. Znaczenie:

1. Istnieje teraz rozdział wartości, więc mniejsze prawdopodobieństwo konfliktu z innymi zastosowaniami
2. Możesz przechowywać różne typy, nie martwiąc się już o dziwne zachowanie podczas odzyskiwania wartości za pośrednictwem CONTEXT_INFO()(szczegółowe informacje znajdują się w moim poście: Dlaczego CONTEXT_INFO () Zwraca dokładną wartość ustawioną przez SET CONTEXT_INFO? )
3. Dostajesz o wiele więcej miejsca: maks. 8000 bajtów na „wartość”, łącznie do 256 kb na wszystkie klucze (w porównaniu do maks. 128 bajtów CONTEXT_INFO)

Aby uzyskać szczegółowe informacje, zobacz następujące strony dokumentacji:

• sp_set_session_context
• SESSION_CONTEXT

Nie możesz tego zrobić, chyba że chcesz zapisać identyfikator użytkownika serwera SQL zamiast aplikacji na poziomie pierwszym.

Możesz wykonać miękkie usuwanie, mając kolumnę o nazwie UsunięteBy i ustawiając ją w razie potrzeby, a następnie wyzwalacz aktualizacji może wykonać prawdziwe usunięcie (lub zarchiwizować rekord, na ogół unikam twardego usuwania, jeśli to możliwe i legalne), a także aktualizując dziennik audytu . Aby wymusić usunięcie w ten sposób, zdefiniuj on deletewyzwalacz, który wywołuje błąd. Jeśli nie chcesz dodawać kolumny do tabeli fizycznej, możesz zdefiniować widok, który dodaje kolumnę i zdefiniować instead ofwyzwalacze do obsługi aktualizacji tabeli podstawowej, ale może to być przesada.

Czy istnieje sposób na przekazanie informacji do wyzwalacza usuwania, aby mógł wiedzieć, kto usunął rekord?

Tak, najwyraźniej istnieją dwa sposoby ;-). Jeśli istnieją jakiekolwiek zastrzeżenia do używania, CONTEXT_INFOjak zasugerowałem w mojej innej odpowiedzi tutaj , właśnie pomyślałem o innym sposobie, który ma czystsze funkcjonalne oddzielenie od innych kodów / procesów: użyj lokalnej tabeli tymczasowej.

Nazwa tabeli tymczasowej powinna zawierać nazwę tabeli usuwanej, ponieważ pomoże to oddzielić ją od wszelkich innych kodów, które mogą się zdarzyć w tej samej sesji. Coś w stylu:
#<TableName>DeleteAudit

Zaletą lokalnej tabeli temp. CONTEXT_INFOJest to, że jeśli ktoś w innym proc - czyli w jakiś sposób wywołuje ten konkretny proces „Usuń” - po prostu niepoprawnie używa tej samej nazwy tabeli temp, podproces a) utworzy nowy lokalny tabela tymczasowa żądanej nazwy, która będzie oddzielna od początkowej tabeli tymczasowej (mimo że ma taką samą nazwę), oraz b) wszelkie instrukcje DML względem nowej lokalnej tabeli tymczasowej w podprocesie nie wpłyną na żadne dane w lokalna tabela temp utworzona tutaj w procesie nadrzędnym, a zatem nie ma zastępowania danych. Oczywiście, jeśli kwestie podproces oświadczenie DML przeciwko tej temp nazwy tabeli bez uprzedniego wydawania CREATE TABLE tej samej nazwie, a następnie te oświadczenia DML będzie wpływać na dane w tej tabeli. ALE, w tym momencie stajemy się naprawdętutaj przypadek, nawet bardziej niż z prawdopodobieństwem nakładających się zastosowań CONTEXT_INFO(tak, wiem, że tak się stało, dlatego mówię „przypadek”, a nie „nigdy się nie zdarzy”).

1. Aplikacja powinna wywołać procedurę przechowywaną „Usuń”, która przechodzi w nazwę użytkownika (lub cokolwiek innego), która usuwa rekord. Zakładam, że jest to już używany model, ponieważ wygląda na to, że śledzisz już operacje wstawiania i aktualizacji.

2. Procedura przechowywana „Usuń”:

   CREATE TABLE #MyTableDeleteAudit (UserName VARCHAR(50));
   INSERT INTO #MyTableDeleteAudit (UserName) VALUES (@UserName);
   
   -- DELETE STUFF HERE

3. Wyzwalacz kontroli:

   -- Set the datatype and length to be the same as the [UserWhoMadeChanges] field
   DECLARE @UserName VARCHAR(50);
   IF (OBJECT_ID(N'tempdb..#TriggerTestDeleteAudit') IS NOT NULL)
   BEGIN
      SELECT @UserName = UserName
      FROM #TriggerTestDeleteAudit;
   END;
   
   -- catch the following conditions: missing table, no rows in table, or empty row
   IF (@UserName IS NULL OR @UserName NOT LIKE '%[a-z]%')
   BEGIN
     /* -- uncomment if undefined UserName == badness
      ROLLBACK TRAN; -- cancel the DELETE operation
      RAISERROR('Please set UserName via #TriggerTestDeleteAudit and try again.', 16 ,1);
      RETURN; -- exit
     */
     /* -- uncomment if undefined UserName gets default value
      SET @UserName = '<unknown>';
     */
   END;
   
   INSERT INTO AuditTable (IdOfRecordedAffected, UserWhoMadeChanges) 
      SELECT del.ID, @UserName
      FROM DELETED del;

   Przetestowałem ten kod w wyzwalaczu i działa zgodnie z oczekiwaniami.