SQL | Triggery v SQL Server – Definice, Typy, Syntaxe a Příklady

Trigger je objekt v databázi (procedury), který slouží jako hlídač určité události (eventu). Pomocí triggerů můžeme na základě výskytu této události vyvolat nějakou dodatečnou akci – třeba logování nebo odmítnutí této akce.

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

K čemu se triggery 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.

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 velmi 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.

Příklad na instead of trigger

Ř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')

Instead of trigger vysledek

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í ROLLBACK 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;

Rate this article

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Category: Nepropásněte SQL Administrace

About Ing. Jan Zedníček - BI Developer, Finance controller

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.