20 maja 2011

Triggery DML w MS SQL Server - Cześć 2 dla zainteresowanych

Wprowadzenie


W poście 'Triggery DML w MS SQL Server' opisałem podstawowe informacje dotyczące triggerów DML implementowanych w T-SQLu. Chciałbym ten temat rozwinąć, opisując kilka zagadnień, których zrozumienie jest istotne do poprawnej implementacji DML triggerów.

Kolejność wykonywania wielu triggerów


Z bliżej nie wyjaśnionego biznesowego  powodu czasami trzeba zdefiniować kilka triggerów na danej tabeli (tylko triggery typu AFTER mogą być dodane kilka razy). Sql Server umożliwia ustalenie który z triggerów wykona się pierwszy, a który ostatni. Jeżeli mamy np. 4 triggery na danej tabeli to możemy ustawić, który trigger wykona się pierwszy, a który ostatni. Triggery wykonujące się pomiędzy pierwszym i ostatnim odpalą się w dowolnej kolejności. Należy też pamiętać, że nie można dodać danego triggera dwukrotnie do tej samej tabeli.

--use master;
--DROP DATABASE TriggerDatabase2;
GO
use master;
GO
CREATE DATABASE TriggerDatabase2;
GO
USE TriggerDatabase2;
GO

Po utworzeniu bazy uruchomimy skrytp tworzący tabelę Events. Tabelka będzie miała 4 triggery AFTER INSERT (Events_AfterInsertx).

CREATE TABLE dbo.[Events]
(
 EventName NVarchar(20) NOT NULL PRIMARY KEY,
 EventDate DateTime NOT NULL
)
GO
DECLARE @fourTriggersCount INT
SET @fourTriggersCount = 4
WHILE @fourTriggersCount > 0
BEGIN
  DECLARE @createTriggerSql VARCHAR(max)
  SET @createTriggerSql  = '
  CREATE TRIGGER Events_AfterInsert' + CAST(@fourTriggersCount AS VARCHAR) + ' 
      ON dbo.[Events]
   AFTER INSERT 
   AS 
   BEGIN
      PRINT ''Executed Events_AfterInsert' + CAST(@fourTriggersCount AS VARCHAR) + ' trigger''
   END
   '
   EXEC (@createTriggerSql)
   SET @fourTriggersCount = @fourTriggersCount - 1
END 

SELECT t.name, OBJECT_NAME(t.parent_id) TableName , te.is_first, te.is_last
  FROM sys.triggers t 
  JOIN sys.trigger_events te
    ON t.object_id = te.object_id
WHERE t.parent_id = object_id('dbo.Events')

Domyślnie kolejność wykonania triggerów nie jest ustalona. Aczkolwiek przy użyciu procedury systemowej sp_settriggerorder można ustawić który trigger będzie wykonany jako pierwszy, a który jako ostatni.


INSERT INTO dbo.[Events](EventName, EventDate)
VALUES('Euro 2012', GETDATE())

--Executed Events_AfterInsert4 trigger
--Executed Events_AfterInsert3 trigger
--Executed Events_AfterInsert2 trigger
--Executed Events_AfterInsert1 trigger
--(1 row(s) affected)

Przy użyciu sp_settriggerorder udaje się zmienić kolejność wywołań triggerów.

EXEC sp_settriggerorder 'Events_AfterInsert4', @order = 'last' , @stmttype='INSERT'
EXEC sp_settriggerorder 'Events_AfterInsert1', @order = 'first' , @stmttype='INSERT'

SELECT t.name, OBJECT_NAME(t.parent_id) TableName , te.is_first, te.is_last
  FROM sys.triggers t 
  JOIN sys.trigger_events te
    ON t.object_id = te.object_id
WHERE t.parent_id = object_id('dbo.Events')

INSERT INTO dbo.[Events](EventName, EventDate)
VALUES('Euro 2016', GETDATE())

--Executed Events_AfterInsert1 trigger
--Executed Events_AfterInsert3 trigger
--Executed Events_AfterInsert2 trigger
--Executed Events_AfterInsert4 trigger
--
--(1 row(s) affected)

Procedura sp_settriggerorder musi być wywoływana przy wykonaniu ALTER TRIGGER lub DROP/CREATE, gdyż informacje o kolejności wykonania są tracone przy zmianie definicji triggera.

Rekurencyjne wywołania triggerów


Trigger może modyfikować dane tabeli (np. Products) dla której został uruchomiony. Możliwość wywoływania rekurencyjnego triggerów jest kontrolowana na poziomie bazy danych poleceniem ALTER DATABASE. Opcja RECURSIVE_TRIGGERS jest domyślnie wyłączona na poziomie bazy danych. Jeżeli RECURSIVE_TRIGGERS jest ustawiona na ON, modyfikacja danych spowoduje ponowne uruchomienie triggera na danej tabeli (w naszym przypadku Products). Stan opcji RECURSIVE_TRIGGERS można sprawdzić odpytując sys.databases.

SELECT name, is_recursive_triggers_on 
  FROM sys.databases WHERE name = 'TriggerDatabase2'

Przykład prezetujący uruchamianie rekurencyjne triggerów będzie bazował na strukturze organizacyjnej w firmie KarpCorp. Struktura jest następująca:

CEO
   ->CIO
     ->IT Manager
      ->IT Specialist
   ->CFO
     ->Accountant

Każdy pracownik ma swoją ocenę (Rating), która stanowi skladową oceny swojego przełożonego i tak dalej. Jeżeli ocena IT Specialist wzrośnie to powininna wzrosnąć ocena IT Managera, CIO i CEO.

W rozwiązaniu opisanym poniżej użyjemy zagnieżdżonych triggerów. Aczkolwiek  w środowiskach produkcyjnych rekurencyjnych wywołań triggerów należy używać z ostrożnością. Po pierwsze możemy zderzyć się z limitem 32 wywołań rekurencyjnych, a po drugie takie operacje lepiej jest przeprowadzać w kodzie w sposób bardziej jawny i wydajniejszy. Należy też pamiętać, że zmiana  opcji RECURSIVE_TRIGGERS wpłynie na wszystkie triggery zdefiniowane w tej bazie.

CREATE TABLE dbo.Employees
(
 EmployeeId Int PRIMARY KEY NOT NULL,
 EmployeeName NVarchar(20) NOT NULL, 
 Rating Int NOT NULL DEFAULT(0),
 EmployeeManagerId Int NULL
)
GO
CREATE TRIGGER dbo.Employees_ManageEmployeeRating
ON dbo.Employees
AFTER UPDATE
AS
BEGIN
 IF @@ROWCOUNT = 0 RETURN

   IF UPDATE(Rating)
   BEGIN
    UPDATE e
   SET Rating = e.Rating + i.Rating - d.Rating
       FROM Employees e
      INNER JOIN inserted i
         ON e.EmployeeId = i.EmployeeManagerId
      INNER JOIN deleted d
         ON e.EmployeeId = d.EmployeeManagerId            
   END  
END
GO
INSERT INTO dbo.Employees(EmployeeId, EmployeeName, EmployeeManagerId)
SELECT 1, 'CEO', NULL 
UNION ALL
SELECT 2, 'CFO', 1
UNION ALL
SELECT 3, 'CIO', 1
UNION ALL
SELECT 4, 'IT Manager', 3
UNION ALL
SELECT 5, 'IT Specialist', 4
UNION ALL
SELECT 6, 'Accountant', 2
GO
SELECT * FROM Employees


Domyślnie RECURSIVE_TRIGGERS jest ustawiona na OFF, zatem aktualizacja oceny zadziała tylko jeden poziom wyżej w strukturze organizacyjnej.

UPDATE Employees
SET Rating = Rating + 1
WHERE EmployeeName = 'IT Specialist'

SELECT * FROM Employees
GO

Aczkolwiek, po włączeniu RECURSIVE_TRIGGERS aktualizacja struktury przebiega już poprawnie. Tym razem ocena CEO, CIO, CFO wzrośnie prawidłowo o jeden z każdej gałęzi organizacji.

SELECT * FROM Employees
GO
ALTER DATABASE TriggerDatabase2 SET RECURSIVE_TRIGGERS ON
GO
UPDATE Employees
SET Rating = Rating + 1
WHERE EmployeeName = 'IT Specialist'

UPDATE Employees
SET Rating = Rating + 1
WHERE EmployeeName = 'Accountant'

SELECT * FROM Employees
GO
ALTER DATABASE TriggerDatabase2 SET RECURSIVE_TRIGGERS OFF


Zagnieżdżone wywołania


Jeżeli trigger Trigger1 powiązany z tabelą Table1 wstawia rekord do tabeli Table2, na której jest zdefiniowany Trigger2, który zostaje wywołany jako konsekwencja wstawienia rekordu, to mówimy o wywołaniu zagnieżdżonym triggera (nested trigger). Domyślnie triggery mogą być w ten sposób wywoływane. Można te ustawienie skonfigurować na poziomie instancji SQL Servera. Polecenie poniżej wyświetla aktualną wartość dla instancji.

EXEC sp_configure 'nested triggers' 

"Cracking" i triggery


Triggery wykonują się w kontekście uprawnień użytkownika wykonującego polecenie powodujące uruchomienie triggera. Umożliwia to stworzenie triggera który np. wykonuje jakąś niepożądaną akcję (np. usunięcie danych w kluczowej bazie na lokalnej instancji).

Powiedzmy, że użytkownika Cracker1 zdobędzie uprawnienia do bazy NotImportantDB. Tworzy on na tabeli Tabel1 triggera AFTER INSERT, która wykonuje DELETE'a na tabeli FinancialDB.dbo.SalaryRecords. Do Tabel1 wstawia rekordy aplikacja webowa App1, która działa na koncie z uprawnieniami administracyjnymi do instancji SQL Server (znany przypadek gdy nie chce się sprawdzić jakich dokładnie uprawnień potrzebujemy). W ten sposób DELETE na bazie FinancialDB wykona się w kontekście konta z uprawnieniami administrator (sa).

Tym miłym akcentem chciałbym zakończyć serię o triggerach DML. Mam nadzieję, że będzie ona pomocna w lepszym zrozumieniu tej części platformy SQL Server.

Cały kod źródłowy jest do tutaj.

  1. Triggery DML w MS SQL Server
  2. Triggery DML w MS SQL Server - Cześć 2 dla zainteresowanych 
  3. Implementacja DML Triggerów w CLR .NET

Brak komentarzy:

Prześlij komentarz

Uwaga: tylko uczestnik tego bloga może przesyłać komentarze.