SQL trigger je objekt v databázi, který slouží jako hlídač určité události (eventu) a do češtiny by se to dalo přeložit asi jako “spoušť”. Jak již překlad napovídá, pomocí triggerů můžeme na základě výskytu této události vyvolat (spustit) nějakou následnou akci – třeba logování, test podmínky, odmítnutí této akce nebo něco jiného.

Událost, která spustí požadovanou akci triggeru může být třeba:

• Vložení (INSERT) nebo změna (UPDATE) záznamů v tabulce
• Smazání (DELETE) záznamů v tabulce
• Vytvoření (CREATE) / smazání (DROP) objektu v databázi
• Příhlášení uživatele
• a mnoho dalších

K čemu se triggery v SQL používají?

Triggery jsou využívány převážně z důvodu auditování objektů v databázi (ale není to podmínkou)

Pokud například chceme mít přehled o tom, co se v databázi nad nějakou konkrétní tabulkou odehrává za změny, tak vytvořením triggeru můžeme tyto změny logovat nebo třeba poslat notifikaci emailem, že k určité události došlo.

Triggery jsou dobrý nástroj, ale špatný pán. Před jeho nasazením je potřeba zvážit řadu aspektů, z nichž velmi důležitým je performance neboli výkon sql dotazů a celkově databáze.

Pokud nad objektem nasadíme trigger, tak pochopitelně veškeré operace, které trigger aktivují budou trvat déle. Nad velkými tabulkami to může znamenat velký problém.

Triggery dělíme do 3 kategorií podle toho, jakou událost (event) chceme sledovat.

• DML (Data manipulation language) triggery
• DDL (Data definition language) triggery
• Logon Triggery

DML (Data Manipulation Language) Trigger – INSERT, UPDATE, DELETE

Příkazy ze skupiny DML jsou takové, které mění obsah tabulky nebo view. Jde o příkazy pomocí kterých:

• Vkládáme záznamy do tabulky pomocí INSERT
• Upravujeme záznamy pomocí UPDATE
• Nebo je mažeme pomocí DELETE

DML Trigger je pak aktivován v momentě, kdy je nad tabulkou taková operace realizována.

Syntaxe CREATE DML triggeru (obecná)

CREATE TRIGGER [schema].[trigger_name]
ON {table | view}
{ FOR | AFTER | INSTEAD OF }
{ INSERT [,] UPDATE [,] DELETE }
AS {sql_statement}

Syntaxe vychází z Microsoft dokumentace a lehce jsem ji zjednodušil, aby se dala lépe číst. Vidíme, že při založení triggeru musíme definovat DML typ, který chceme sledovat (INSERT, UPDATE, DELETE). Zvolit lze 1 operaci nebo třeba všechny 3 oddělené čárkou.

Dále také zjednodušeně definujeme, jak se má trigger chovat (FOR/AFTER a INSTEAD OF). Podle toho také DML triggery můžeme rozdělit do 2 kategorií (viz. dále)

Logické tabulky – [inserted] a [deleted] pro DML triggery

DML triggery mají tu vlastnost, že při jejich aktivaci dochází k založení 2 speciálních logických tabulek – inserted a deleted. To je důležitá a užitečná součást celého triggeru.

Tyto tabulky obsahují data ze zdrojové tabulky, která byla pomocí DML operace (INSERT, UPDATE nebo DELETE) nějakým způsobem ovlivněna.

• Tabulka [inserted] obsahuje nové a změněné záznamy
• Tabulka [deleted] pak obsahuje smazané záznamy

INSTEAD OF Trigger

Pro INSTEAD OF trigger platí, že DML operace se vůbec neprovede a místo toho se spustí <sql_statement>. Jinými slovy pomocí triggeru zakážeme danou operaci uskutečnit a místo toho provedeme operaci jinou.

Info: Typickým příkladem použití může být třeba test na duplicity, kdy do cílové tabulky chceme vložit pouze ty záznamy, které se v tabulce ještě nevyskytují (například klient, který je zadaný 2x jinak). Nechceme tedy celou operaci zakázat jako takovou, ale odmítnout pouze určité zdrojové záznamy.

Příklad:

Mějme tabulku [Test], která obsahuje sloupec [ID] a [Text_]. Do této tabulky chceme vkládat pouze jedinečné hodnoty na základě sloupce [ID]. Pokus o vložení duplicitního záznamu bude ignorován.

Řešení:

CREATE TRIGGER [dbo].[Remove_Duplicities]
ON [Temp].[dbo].[Test]
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO [Temp].[dbo].[Test] (
    [ID]
    ,[Text_]
  )
  SELECT
    [ID]
    ,[Text_]
  FROM [inserted]
  WHERE [ID] NOT IN (SELECT DISTINCT [ID] FROM [Temp].[dbo].[Test])
END

  GO

–pokusíme se vložit 3 záznamy z nichž 1 bude duplicitní
INSERT INTO [Temp].[dbo].[Test] (
  [ID]
  ,[Text_]
)
VALUES (2, 'Hi'),
(3, 'Hello'),
(1, 'Hey')

Vidíme, že duplicitní záznam byl ignorován. Všimněte si také, že v definici triggeru pracujeme s logickou tabulkou [inserted].

FOR/AFTER Trigger

Pro FOR/AFTER platí, že <sql_statement> je realizován až po proběhnutí dané DML operace. To znamená, že daná operace proběhne a poté se provede příkaz z triggeru (například logování).

Info: Výše zmíněné ovšem neznamená, že celou transakci nemůžeme “stornovat” pomocí rollbacku transakce pokud není splněna nějaká námi stanovená podmínka..pokud chceme.

Příklad: Mějme opět tabulku jako v předchozím případě. Pomocí AFTER triggeru bychom chtěli zalogovat operace typu INSERT a DELETE. Logovat chceme čas, typ operace, ID ze zdrojove tabulky a uživatele, který operaci vykonal.

CREATE TABLE [dbo].[DML_Audit] (
  [ID] INT IDENTITY(1,1)
  ,[DML_OPERATION] VARCHAR(255)
  ,[DML_DATETIME] DATETIME
  ,[DML_USER] VARCHAR(255)
  ,[ID_SourceTable] INT
)

GO

CREATE TRIGGER [dbo].[tr_DML_Audit]
ON [Temp].[dbo].[Test]
AFTER INSERT,DELETE
AS
BEGIN
INSERT INTO [dbo].[DML_Audit] (
  [DML_OPERATION]
  ,[DML_DATETIME]
  ,[DML_USER]
  ,[ID_SourceTable]
)
SELECT
  'INSERT' AS [DML_OPERATION]
  ,GETUTCDATE() AS [DML_DATETIME]
  ,SUSER_NAME() AS [DML_USER]
  ,[inserted].[ID] AS [ID_SourceTable]
FROM [inserted]

UNION ALL

SELECT
  'DELETED' AS [DML_OPERATION]
  ,GETUTCDATE() AS [DML_DATETIME]
  ,SUSER_NAME() AS [DML_USER]
  ,[deleted].[ID] AS [ID_SourceTable]
FROM [deleted]
END

DELETE FROM [Temp].[dbo].[Test] WHERE ID = 2

Smazání DROP DML Triggeru

DROP TRIGGER [schema].[trigger_name]

DDL (Data Definition Language) Trigger

Do příkazů typu DDL řadíme operace s objekty jako takovými. Zatímco DML triggery jsou definovány nad objekty, DDL trigger je definován nad databází nebo nad serverem. DDL trigger je aktivován, pokud je nad nějakým objektem v databázi provedeno:

• Create
• Alter
• Drop
• Grant
• Deny
• Revoke
• Update statistics

Info: DDL Triggery nikdy netvoří logické tabulky [inserted], [deleted] jako DML triggery

Syntaxe CREATE DDL Triggeru

CREATE TRIGGER [name]
ON { DATABASE | ALL SERVER}
[WITH option]
FOR {event_type}
AS {sql_statement}

Jestliže chceme použít v triggeru určitou DDL operaci pro skupinu objektů. Tak použijeme za příkazem podtřžítko a objekt. Např

• CREATE_TABLE
• DROP_VIEW
• Atd

Příklad: V databázi [Temp] chceme hromadně zakázat operaci typu ALTER View

USE [Temp]
GO
CREATE TRIGGER [restrict_alter_view]
ON DATABASE
FOR ALTER_VIEW
AS
BEGIN
PRINT 'Alter view operation is restricted'
ROLLBACK TRANSACTION
END

Pokud bychom se pokusili provést ALTER nějakého view, dostaneme odpověď:

Alter view operation is restricted
Msg 3609, Level 16, State 2, Procedure XYZ, Line 3 [Batch Start Line 9]
The transaction ended in the trigger. The batch has been aborted.

Smazání DROP DDL triggeru

DROP TRIGGER [trigger_name] ON DATABASE;