Sdílet prostřednictvím

CREATE TRIGGER (Transact-SQL)

platí pro: SQL Server Azure SQL DatabaseAzure SQL Managed Instance

Vytvoří trigger DML, DDL nebo přihlášení. Trigger je speciální typ uložené procedury, která se automaticky spustí při výskytu události na databázovém serveru. Triggery DML se spustí, když se uživatel pokusí upravit data prostřednictvím události jazyka pro manipulaci s daty (DML). Události DML jsou INSERT, UPDATEnebo DELETE příkazy v tabulce nebo zobrazení. Tyto triggery se aktivují, když se aktivuje jakákoli platná událost, ať už jsou ovlivněné řádky tabulky, nebo ne. Další informace najdete v tématu DML triggery.

Triggery DDL se spouští v reakci na různé události jazyka DDL (Data Definition Language). Tyto události primárně odpovídají Transact-SQL CREATE, ALTERa příkazům DROP a určitým systémovým uloženým procedurám, které provádějí operace podobné DDL.

Aktivuje se přihlášení v reakci na LOGON událost, která se vyvolá při vytváření relace uživatele. Triggery můžete vytvářet přímo z příkazů Transact-SQL nebo z metod sestavení vytvořených v modulu CLR (Common Language Runtime) rozhraní Microsoft .NET Framework a nahraných do instance SQL Serveru. SQL Server umožňuje vytvořit více aktivačních událostí pro jakýkoli konkrétní příkaz.

Důležité

Škodlivý kód uvnitř triggerů může běžet pod eskalovanými oprávněními. Další informace o tom, jak tuto hrozbu zmírnit, najdete v tématu Správa zabezpečení triggerů.

Poznámka:

Integrace .NET Framework CLR do SQL Server je popsána v tomto článku. Integrace CLR se nevztahuje na Azure SQL Database.

Transact-SQL konvence syntaxe

Syntaxe

Syntaxe SQL Serveru

Aktivační událost v tabulce INSERTUPDATEnebo DELETE zobrazení (trigger DML):

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement  [ ; ] [ , ...n ] | EXTERNAL NAME <method_specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Trigger pro INSERTtabulku nebo UPDATEDELETE příkaz pro tabulku (trigger DML u tabulek optimalizovaných pro paměť):

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table }
[ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement  [ ; ] [ , ...n ] }

<dml_trigger_option> ::=
    [ NATIVE_COMPILATION ]
    [ SCHEMABINDING ]
    [ EXECUTE AS Clause ]

Aktivační událost v objektu CREATE, ALTER, DROP, GRANT, DENY, REVOKEnebo příkazu UPDATE (trigger DDL):

CREATE [ OR ALTER ] TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ , ...n ] ]
{ FOR | AFTER } { event_type | event_group } [ , ...n ]
AS { sql_statement  [ ; ] [ , ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

Trigger události LOGON (trigger přihlášení):

CREATE [ OR ALTER ] TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ , ...n ] ]
{ FOR | AFTER } LOGON
AS { sql_statement  [ ; ] [ , ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<logon_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

Syntaxe Azure SQL Database

Aktivační událost v tabulce INSERTUPDATEnebo DELETE zobrazení (trigger DML):

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table | view }
 [ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
  AS { sql_statement  [ ; ] [ , ...n ] [ ; ] > }

<dml_trigger_option> ::=
        [ EXECUTE AS Clause ]

Aktivační událost v objektu CREATE, ALTER, DROP, GRANT, DENY, REVOKEnebo příkazu UPDATE STATISTICS (trigger DDL):

CREATE [ OR ALTER ] TRIGGER trigger_name
ON { DATABASE }
 [ WITH <ddl_trigger_option> [ , ...n ] ]
{ FOR | AFTER } { event_type | event_group } [ , ...n ]
AS { sql_statement  [ ; ] [ , ...n ]  [ ; ] }

<ddl_trigger_option> ::=
    [ EXECUTE AS Clause ]

Argumenty

NEBO ALTER

Platí pro: SQL Server 2016 (13.x) SP1 a novější verze a Azure SQL Database

Podmíněně změní trigger pouze v případě, že už existuje.

schema_name

Název schématu, do kterého patří trigger DML. Triggery DML jsou vymezeny na schéma tabulky nebo zobrazení, na které se vytvářejí. schema_name nelze zadat pro triggery DDL ani přihlášení.

trigger_name

Název triggeru. Trigger_name musí dodržovat pravidla identifikátorů s tím rozdílem#, že trigger_name nemůže začínat nebo ##.

stůl | pohled

Tabulka nebo zobrazení, na kterém se spouští trigger DML. Tato tabulka nebo zobrazení se někdy označuje jako tabulka triggerů nebo zobrazení aktivační události. Zadání plně kvalifikovaného názvu tabulky nebo zobrazení je volitelné. Na zobrazení můžete odkazovat pouze triggerem INSTEAD OF . Triggery DML nemůžete definovat v místních nebo globálních dočasných tabulkách.

DATABÁZE

Použije obor triggeru DDL pro aktuální databázi. Pokud je tato hodnota zadaná, aktivuje se aktivační událost při každém výskytu event_type nebo event_group v aktuální databázi.

ALL SERVER

Použije obor triggeru DDL nebo přihlášení na aktuální server. Pokud je tato hodnota zadaná, trigger se aktivuje vždy, když event_type nebo event_group dojde kdekoli na aktuálním serveru.

S ŠIFROVÁNÍM

Zakrývá text CREATE TRIGGER příkazu. Použití WITH ENCRYPTION zabraňuje publikování triggeru jako součásti replikace SQL Serveru. WITH ENCRYPTION pro triggery CLR není možné zadat.

VYKONAT JAKO

Určuje kontext zabezpečení, pod kterým se trigger spouští. Umožňuje řídit, který uživatelský účet instance SQL Serveru používá k ověření oprávnění u všech databázových objektů, na které aktivační událost odkazuje.

Tato možnost se vyžaduje pro triggery v tabulkách optimalizovaných pro paměť.

Další informace naleznete v tématu EXECUTE AS – klauzule.

NATIVE_COMPILATION

Označuje, že trigger je nativně zkompilován.

Tato možnost se vyžaduje pro triggery v tabulkách optimalizovaných pro paměť.

SCHEMABINDING

Zajišťuje, že tabulky odkazované triggerem nelze vynechat ani změnit.

Tato možnost je vyžadována pro triggery v tabulkách optimalizovaných pro paměť a nepodporuje se pro triggery v tradičních tabulkách.

FOR | PO

FOR nebo AFTER určuje, že trigger DML se aktivuje pouze tehdy, když se úspěšně spustily všechny operace zadané v příkazu triggering SQL. Všechny referenční kaskádové akce a kontroly omezení musí být úspěšné i před aktivací tohoto triggeru.

V zobrazeních nemůžete definovat AFTER triggery.

NAMÍSTO

Určuje, že se trigger DML spustí místo aktivačního příkazu SQL, a tím přepíše akce aktivačních příkazů. U aktivačních událostí DDL ani přihlášení není možné zadat INSTEAD OF .

Ve většině případů můžete definovat jednu INSTEAD OF aktivační událost na INSERT, UPDATEnebo DELETE příkaz v tabulce nebo zobrazení. Můžete také definovat zobrazení v zobrazeních, kde má každé zobrazení vlastní INSTEAD OF aktivační událost.

Aktivační události nelze definovat INSTEAD OF v aktualizovatelných zobrazeních, která používají WITH CHECK OPTION. Výsledkem je chyba při přidání triggeru INSTEAD OF do aktualizovatelného zobrazení WITH CHECK OPTION . Tuto možnost odeberete pomocí před ALTER VIEW definováním triggeru INSTEAD OF .

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }

Určuje příkazy pro úpravu dat, které aktivují trigger DML při pokusu o porovnání s touto tabulkou nebo zobrazením. Zadejte aspoň jednu možnost. V definici triggeru použijte libovolnou kombinaci těchto možností v libovolném pořadí.

U INSTEAD OF triggerů nemůžete použít DELETE možnost u tabulek, které mají referenční relaci, a určit kaskádovou akci ON DELETE. Podobně není tato UPDATE možnost povolena u tabulek, které mají referenční relaci, a to určení kaskádové akce ON UPDATE.

S PŘIPOJENÍM

Platí pro: SQL Server 2008 (10.0.x) až SQL Server 2008 R2 (10.50.x).

Určuje, že se má přidat další aktivační událost existujícího typu. WITH APPEND nelze použít s INSTEAD OF triggery nebo pokud AFTER je aktivační událost explicitně uvedena. Pro zpětnou kompatibilitu používejte WITH APPEND pouze v případě, že FOR je zadána bez INSTEAD OF nebo AFTER. Nemůžete určit WITH APPEND , jestli používáte EXTERNAL NAME (to znamená, že je aktivační událost triggerem CLR).

event_type

Název události jazyka Transact-SQL, která po spuštění způsobí aktivaci triggeru DDL. Platné události pro triggery DDL jsou uvedeny v událostech DDL.

event_group

Název předdefinované skupiny událostí jazyka Transact-SQL. Trigger DDL se aktivuje po spuštění jakékoli události jazyka Transact-SQL, která patří do event_group. Platné skupiny událostí pro triggery DDL jsou uvedeny ve skupinách událostí DDL.

CREATE TRIGGER Po dokončení běhu event_group také funguje jako makro přidáním typů událostí, které pokrývá do sys.trigger_events zobrazení katalogu.

NE PRO REPLIKACI

Označuje, že aktivační událost by neměla být spuštěna, když agent replikace upraví tabulku, která je součástí triggeru.

sql_statement

Podmínky a akce triggeru. Podmínky triggeru určují další kritéria, která určují, jestli se mají spustit akce aktivační události DML, DDL nebo přihlášení.

Akce triggeru zadané v příkazech Transact-SQL se projeví při pokusu o operaci.

Triggery můžou obsahovat libovolný počet a typ příkazů Transact-SQL s výjimkami. Další informace naleznete v tématu Poznámky. Trigger je navržený tak, aby kontroloval nebo měnil data na základě změny dat nebo příkazu definice. Trigger by neměl uživateli vracet data. Příkazy Transact-SQL v triggeru často zahrnují jazyk control-of-flow.

Triggery DML používají odstraněné a vložené logické (koncepční) tabulky. Strukturálně se podobají tabulce, na které je aktivační událost definována, tj. tabulka, na které se akce uživatele pokouší. Odstraněné a vložené tabulky obsahují staré hodnoty nebo nové hodnoty řádků, které mohou být změněny akcí uživatele. Pokud chcete například načíst všechny hodnoty v deleted tabulce, použijte:

SELECT * FROM deleted;

Další informace najdete v tématu Použití vložených a odstraněných tabulek.

Triggery DDL a přihlášení zaznamenávají informace o aktivační události pomocí funkce EVENTDATA . Další informace naleznete v tématu Použití funkce EVENTDATA.

SQL Server umožňuje aktualizaci sloupců textu, ntextu nebo obrázku prostřednictvím triggeru INSTEAD OF v tabulkách nebo zobrazeních.

Důležité

Ntext, text a image datové typy budou odebrány v budoucí verzi Microsoft SQL Serveru. Vyhněte se používání těchto datových typů v nové vývojové práci a naplánujte úpravu aplikací, které je aktuálně používají. Místo toho použijte nvarchar(max), varchar(max) a varbinary(max). Triggery AFTER podporují vložených a odstraněných tabulkách data varchar(max), nvarchar(max) a varbinary(max).INSTEAD OF

U triggerů v tabulkách optimalizovaných pro paměť je ATOMIC jediným sql_statement povoleným na nejvyšší úrovni blok. T-SQL povolený uvnitř ATOMIC bloku je omezen t-SQL povolený uvnitř nativních procs.

<method_specifier>

Pro trigger CLR určuje metodu sestavení, která se má svázat s triggerem. Metoda nesmí obsahovat žádné argumenty a vrátit void. class_name musí být platný identifikátor SQL Serveru a musí existovat jako třída v sestavení s viditelností sestavení. Pokud má třída kvalifikovaný název oboru názvů, který se používá . k oddělení částí oboru názvů, musí být název třídy oddělen pomocí [ ] nebo " oddělovačů. Třída nemůže být vnořenou třídou.

Poznámka:

Ve výchozím nastavení je možnost SQL Serveru spouštět kód CLR vypnutá. Databázové objekty, které odkazují na moduly spravovaného kódu, můžete vytvářet, upravovat a odstraňovat, ale tyto odkazy se nespouštějí v instanci SQL Serveru, pokud není povolená možnost clr s sp_configure.

Poznámky pro triggery DML

Triggery DML se často používají k vynucování obchodních pravidel a integrity dat. SQL Server poskytuje deklarativní referenční integritu (DRI) prostřednictvím ALTER TABLE příkazů a CREATE TABLE příkazů. Dri ale neposkytuje referenční integritu mezi databázemi. Referenční integrita odkazuje na pravidla týkající se relací mezi primárními a cizími klíči tabulek. Chcete-li vynutit referenční integritu, použijte omezení PRIMARY KEY a FOREIGN KEY omezení v ALTER TABLE a CREATE TABLE. Pokud v tabulce triggerů existují omezení, zkontrolují se po spuštění triggeru INSTEAD OF a před spuštěním triggeru AFTER . Pokud dojde k porušení omezení, akce triggeru INSTEAD OF se vrátí zpět a AFTER trigger se neaktivuje.

První a poslední AFTER triggery, které se mají spouštět v tabulce, můžete zadat pomocí příkazu sp_settriggerorder. Pro každou UPDATEINSERTz nich můžete zadat pouze jeden první a jeden poslední AFTER trigger a DELETE operaci v tabulce. Pokud ve stejné tabulce existují další AFTER triggery, náhodně se spustí.

Pokud příkaz ALTER TRIGGER změní první nebo poslední aktivační událost, první nebo poslední atribut nastavený na změněnou aktivační událost je vyřazen a je nutné resetovat hodnotu objednávky pomocí sp_settriggerorder.

Aktivační AFTER událost se spustí až po úspěšném spuštění aktivačního příkazu SQL. Toto úspěšné spuštění zahrnuje všechny referenční kaskádové akce a kontroly omezení přidružené k objektu aktualizované nebo odstraněné. Neaktivuje AFTER rekurzivně INSTEAD OF trigger ve stejné tabulce.

INSTEAD OF Pokud trigger definovaný v tabulce spustí příkaz proti tabulce, která by trigger obvykle znovu aktivovalaINSTEAD OF, aktivační událost se nevolá rekurzivně. Místo toho příkaz zpracovává, jako by tabulka neměla žádnou INSTEAD OF aktivační událost a spouští řetěz operací omezení a AFTER spouštění triggerů. Pokud je například aktivační událost definována jako INSTEAD OF INSERT aktivační událost pro tabulku. A pokud trigger spustí INSERT příkaz ve stejné tabulce, INSERT příkaz spuštěný INSTEAD OF triggerem znovu nevolá trigger. Spuštění INSERT triggeru spustí proces spuštění akcí omezení a spuštění všech AFTER INSERT triggerů definovaných pro tabulku.

INSTEAD OF Když trigger definovaný v zobrazení spustí příkaz proti zobrazení, které by obvykle trigger znovu aktivovaloINSTEAD OF, není volána rekurzivně. Místo toho se příkaz vyřeší jako úpravy u základních tabulek, které jsou podkladovým zobrazením. V tomto případě musí definice zobrazení splňovat všechna omezení pro aktualizovatelné zobrazení. Definice aktualizovatelných zobrazení najdete v tématu Úprava dat prostřednictvím zobrazení.

Pokud je například aktivační událost definována INSTEAD OF UPDATE jako aktivační událost pro zobrazení. Aktivační událost spustí UPDATE příkaz odkazující na stejné zobrazení, UPDATE příkaz spuštěný INSTEAD OF triggerem znovu nevolá trigger. Spuštění UPDATE triggeru se zpracuje v zobrazení, jako by zobrazení nemělo INSTEAD OF aktivační událost. Sloupce změněné tabulkou UPDATE musí být přeloženy na jednu základní tabulku. Každá úprava podkladové základní tabulky začíná řetězem použití omezení a aktivací AFTER triggerů definovaných pro tabulku.

Testování akcí UPDATE nebo INSERT pro konkrétní sloupce

Můžete navrhnout trigger Transact-SQL, který provede určité akce na UPDATE základě konkrétních sloupců nebo INSERT jejich úprav. K tomuto účelu použijte funkci UPDATE nebo COLUMNS_UPDATED v textu triggeru. UPDATE() testy nebo UPDATEINSERT pokusy o jeden sloupec. COLUMNS_UPDATED testy nebo UPDATEINSERT akce, které běží na více sloupcích. Tato funkce vrátí bitový vzor, který označuje, které sloupce byly vloženy nebo aktualizovány.

Omezení aktivačních událostí

CREATE TRIGGER musí být prvním příkazem v dávce a může se vztahovat pouze na jednu tabulku.

Aktivační událost se vytvoří pouze v aktuální databázi; Aktivační událost však může odkazovat na objekty mimo aktuální databázi.

Pokud je název schématu triggeru určený pro kvalifikaci triggeru, opravte název tabulky stejným způsobem.

Stejnou akci triggeru lze definovat pro více než jednu akci uživatele (například INSERT a UPDATE) ve stejném CREATE TRIGGER příkazu.

INSTEAD OF DELETE / INSTEAD OF UPDATE Triggery nelze definovat v tabulce, která má cizí klíč s kaskádou definovanou DELETE/UPDATE akcí.

V triggeru je možné zadat libovolný příkaz SET. Vybraná možnost SET zůstane v platnosti během provádění triggeru a pak se vrátí k dřívějšímu nastavení.

Při aktivaci triggeru se výsledky vrátí do volající aplikace stejně jako u uložených procedur. Aby se zabránilo vrácení výsledků do aplikace kvůli aktivaci triggeru, nezahrnujte ani SELECT příkazy, které vrací výsledky nebo příkazy, které provádějí přiřazení proměnné v triggeru. Aktivační událost, která obsahuje příkazy SELECT , které vrací výsledky uživateli nebo příkazům, které přiřazovat proměnné, vyžadují zvláštní zpracování. Vrácené výsledky byste museli napsat do každé aplikace, ve které jsou povolené úpravy tabulky triggerů. Pokud musí v triggeru dojít k přiřazení proměnné, použijte SET NOCOUNT příkaz na začátku triggeru, abyste zabránili vrácení všech sad výsledků.

TRUNCATE TABLE Příkaz sice funguje jako DELETE příkaz, ale neaktivuje aktivační událost, protože operace nezapíše odstranění jednotlivých řádků. Na neúmyslné obejití triggeru DELETE je ale potřeba, aby se neúmyslně obešli jenom uživatelé s oprávněními ke spuštění TRUNCATE TABLE příkazu.

Příkaz WRITETEXT bez ohledu na to, jestli je protokolovaný nebo nezalogovaný, neaktivuje aktivační událost.

V triggeru DML nejsou povolené následující příkazy Transact-SQL:

  • ALTER DATABASE
  • CREATE DATABASE
  • DROP DATABASE
  • RESTORE DATABASE
  • RESTORE LOG
  • RECONFIGURE

Kromě toho následující příkazy Transact-SQL nejsou v těle triggeru DML povoleny, když se používají pro tabulku nebo zobrazení, které je cílem aktivační akce.

  • CREATE INDEX (včetně CREATE SPATIAL INDEX a CREATE XML INDEX)
  • ALTER INDEX
  • DROP INDEX
  • DROP TABLE
  • DBCC DBREINDEX
  • ALTER PARTITION FUNCTION
  • ALTER TABLE při použití k provedení následujících akcí:
    • Přidání, úprava nebo přetažení sloupců
    • Přepínání oddílů
    • Přidání nebo vyřazení PRIMARY KEY nebo UNIQUE omezení

Poznámka:

Vzhledem k tomu, že SQL Server nepodporuje uživatelem definované triggery v systémových tabulkách, doporučujeme nevytvořovat uživatelem definované triggery v systémových tabulkách.

Optimalizace triggerů DML

Triggery fungují v transakcích (implicitně nebo jinak) a když jsou otevřené, zamknou prostředky. Zámek zůstane v místě, dokud transakce nebude potvrzena (s COMMIT) nebo odmítnuta (s ROLLBACK). Čím déle se trigger spustí, tím vyšší je pravděpodobnost, že se pak zablokuje jiný proces. Proto se triggery zápisu sníží jejich dobu trvání, kdykoli je to možné. Jedním ze způsobů, jak dosáhnout kratší doby trvání, je uvolnit trigger, když příkaz DML změní nulové řádky.

Pokud chcete aktivační událost uvolnit pro příkaz, který nemění žádné řádky, zaužte systémovou proměnnou ROWCOUNT_BIG.

Následující fragment kódu T-SQL ukazuje, jak uvolnit trigger příkazu, který nemění žádné řádky. Tento kód by měl být na začátku každého triggeru DML:

IF (ROWCOUNT_BIG() = 0)
RETURN;

Poznámky pro triggery DDL

Triggery DDL, jako jsou standardní triggery, spouští uložené procedury v reakci na událost. Na rozdíl od standardních triggerů se ale nespouštějí v reakci na UPDATEpříkazy , INSERTani DELETE příkazy v tabulce nebo zobrazení. Místo toho se primárně spouští v reakci na příkazy jazyka DDL (Data Definition Language). Typy příkazů zahrnují CREATE, , ALTERDROP, GRANT, DENY, , REVOKE, a UPDATE STATISTICS. Některé systémové uložené procedury, které provádějí operace podobné DDL, mohou také aktivovat triggery DDL.

Důležité

Otestujte triggery DDL a určete jejich odpovědi na spouštění procedur uložených v systému. Například příkaz CREATE TYPE a sp_addtype uložené sp_rename procedury aktivují trigger DDL vytvořený v CREATE_TYPE události.

Další informace o triggerech DDL najdete v tématu Triggery DDL.

Triggery DDL se neaktivují v reakci na události, které ovlivňují místní nebo globální dočasné tabulky a uložené procedury.

Na rozdíl od triggerů DML nejsou triggery DDL vymezeny na schémata. Proto nemůžete používat funkce, jako OBJECT_IDje , OBJECT_NAMEOBJECTPROPERTYa OBJECTPROPERTYEX pro dotazování metadat o triggerech DDL. Místo toho použijte zobrazení katalogu. Další informace naleznete v tématu Získání informací o triggerech DDL.

Poznámka:

Triggery DDL v oboru serveru se zobrazí v Průzkumníku objektů aplikace SQL Server Management Studio ve složce Aktivační události . Tato složka se nachází ve složce Objekty serveru . Triggery DDL v oboru databáze se zobrazují ve složce Triggery databáze . Tato složka se nachází ve složce Programovatelnost odpovídající databáze.

Triggery přihlášení

Triggery přihlášení provádějí uložené procedury v reakci na LOGON událost. K této události dochází, když je relace uživatele vytvořena s instancí SQL Serveru. Přihlášení se aktivuje po dokončení ověřovací fáze přihlášení, ale před navázáním uživatelské relace. Takže všechny zprávy pocházející z triggeru, které by se obvykle dostaly k uživateli, jako jsou chybové zprávy a zprávy z PRINT příkazu, se přesměrují do protokolu chyb SQL Serveru. Další informace naleznete v tématu Triggery přihlášení.

Triggery přihlášení se neaktivují, pokud se ověření nezdaří.

Distribuované transakce nejsou podporovány v triggeru přihlášení. Chyba 3969 vrátí, když se aktivuje trigger přihlášení, který obsahuje distribuovanou transakci.

Zakázání triggeru přihlášení

Trigger přihlášení může účinně zabránit úspěšným připojením k databázovému stroji pro všechny uživatele, včetně členů pevné role serveru správce systému . Pokud trigger přihlášení brání připojení, členové pevné role serveru sysadmin se mohou připojit pomocí vyhrazeného připojení správce nebo spuštěním databázového stroje v minimálním režimu konfigurace (-f). Pro více informací se podívejte na možnosti spuštění služby databázového stroje .

Obecné aspekty aktivačních událostí

Vrácení výsledků

Možnost vrácení výsledků z triggerů se odebere v budoucí verzi SQL Serveru. Triggery, které vracejí sady výsledků, můžou způsobit neočekávané chování v aplikacích, které s nimi nejsou navržené. Vyhněte se vracení sad výsledků z triggerů v nové vývojové práci a naplánujte úpravu aplikací, které v současné době dělají. Pokud chcete triggerům zabránit v vracení sad výsledků, nastavte u aktivačních událostí možnost zakázat hodnotu 1.

Triggery přihlášení vždy nepovolují vrácení sad výsledků a toto chování není konfigurovatelné. Pokud aktivační událost přihlášení vygeneruje sadu výsledků, trigger se nespustí a pokus o přihlášení, který trigger aktivoval, se odepře.

Více spouštěčů

SQL Server umožňuje vytvořit více triggerů pro každou událost DML, DDL nebo LOGON událost. Pokud CREATE TRIGGER FOR UPDATE je například spuštěna pro tabulku, která již UPDATE má aktivační událost, vytvoří se další aktivační událost aktualizace. V dřívějších verzích SQL Serveru je pro každou tabulku povolen pouze jeden trigger pro každou INSERTUPDATEudálost , nebo DELETE událost úprav dat.

Rekurzivní triggery

SQL Server také podporuje rekurzivní vyvolání triggerů, pokud RECURSIVE_TRIGGERS je nastavení povoleno pomocí ALTER DATABASE.

Rekurzivní triggery umožňují výskyt následujících typů rekurzivních událostí:

  • Nepřímá rekurze: Při nepřímé rekurzi aktualizuje aplikace tabulku T1. Aktivuje se trigger TR1, aktualizuje se tabulka T2. Trigger T2 se pak aktivuje a aktualizuje tabulku T1.

  • Přímá rekurze: V přímé rekurzi aktualizuje aplikace tabulku T1. Aktivuje se trigger TR1, aktualizuje se tabulka T1. Protože se tabulka T1 aktualizovala, aktivuje se znovu aktivace TR1 atd.

Následující příklad používá nepřímý i přímý trigger rekurze Předpokládat, že dvě aktualizační triggery, TR1 a TR2, jsou definovány v tabulce T1. Aktivace TR1 rekurzivní aktualizace tabulky T1 Příkaz UPDATE se spustí vždy TR1 a TR2 jednou. Kromě toho spuštění aktivační TR1 události TR1 (rekurzivně) a TR2. Vložené a odstraněné tabulky pro konkrétní aktivační událost obsahují řádky, které odpovídají pouze UPDATE příkazu, který vyvolal trigger.

Poznámka:

K předchozímu chování dochází pouze v případě, že RECURSIVE_TRIGGERS je nastavení povoleno pomocí .ALTER DATABASE Neexistuje žádné definované pořadí, ve kterém se spouští více aktivačních událostí definovaných pro konkrétní událost. Každá aktivační událost by měla být samostatná.

Zakázání RECURSIVE_TRIGGERS nastavení brání pouze přímým rekurzím. Chcete-li zakázat nepřímou rekurzi, nastavte možnost serveru vnořených triggerů na hodnotu 0 pomocí sp_configure.

Pokud některý z aktivačních událostí provede ROLLBACK TRANSACTION, bez ohledu na úroveň vnoření, nespustí se žádné další triggery.

Vnořené triggery

Triggery můžete vnořit na maximálně 32 úrovní. Pokud trigger změní tabulku, na které je další aktivační událost, aktivuje se druhá aktivační událost a pak může volat třetí aktivační událost atd. Pokud nějaká aktivační událost v řetězu nastaví nekonečnou smyčku, dojde k překročení úrovně vnoření a aktivační událost se zruší. Když trigger Transact-SQL spustí spravovaný kód odkazem na rutinu, typ nebo agregaci CLR, počítá se tento odkaz jako jedna úroveň oproti limitu vnoření na úrovni 32. Metody vyvolané z spravovaného kódu se do tohoto limitu nezapočítávají.

Pokud chcete vnořené triggery zakázat, nastavte možnost sp_configure vnořených triggerů na hodnotu 0 (vypnuto). Výchozí konfigurace podporuje vnořené triggery. Pokud jsou vnořené triggery vypnuté, rekurzivní triggery se také deaktivují, a to i přes RECURSIVE_TRIGGERS nastavení nastavené pomocí ALTER DATABASE.

První AFTER trigger vnořený do triggeru INSTEAD OF se aktivuje i v případě, že je možnost konfigurace vnořeného triggeru serveru 0. V tomto nastavení se ale triggery později AFTER neaktivují. Zkontrolujte, jestli aplikace nespustí vnořené triggery, a zjistěte, jestli aplikace dodržují vaše obchodní pravidla, když je možnost konfigurace vnořeného triggeru serveru nastavená na hodnotu 0. Pokud ne, proveďte příslušné úpravy.

Odložené překlady ip adres

SQL Server umožňuje Transact-SQL uložených procedur, triggerů, funkcí a dávek odkazovat na tabulky, které v době kompilace neexistují. Tato schopnost se nazývá odložené překlady ip adres.

Povolení

Pokud chcete vytvořit trigger DML, vyžaduje ALTER oprávnění k tabulce nebo zobrazení, na kterém se trigger vytváří.

Pokud chcete vytvořit trigger DDL s oborem serveru (ON ALL SERVER) nebo přihlašovací aktivační událostí, vyžaduje CONTROL SERVER oprávnění na serveru. Pokud chcete vytvořit trigger DDL s oborem databáze (ON DATABASE), vyžaduje ALTER ANY DATABASE DDL TRIGGER oprávnění v aktuální databázi.

Příklady

A. Použití triggeru DML se zprávou připomenutí

Následující trigger DML vytiskne klientovi zprávu, když se někdo pokusí přidat nebo změnit data v tabulce v Customer databázi AdventureWorks2022.

CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10);
GO

B. Použití triggeru DML s e-mailovou zprávou s připomenutím

Následující příklad odešle e-mailovou zprávu zadané osobě (MaryM) při Customer změně tabulky.

CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
    EXECUTE msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2022 Administrator',
        @recipients = 'danw@Adventure-Works.com',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';
GO

C. Použití triggeru DML AFTER k vynucení obchodního pravidla mezi tabulkami PurchaseOrderHeader a Vendor

Vzhledem k tomu, že CHECK omezení odkazují pouze na sloupce, na kterých je definováno omezení na úrovni sloupců nebo na úrovni tabulky, musíte definovat všechna omezení křížové tabulky (v tomto případě obchodní pravidla) jako triggery.

Následující příklad vytvoří v databázi trigger AdventureWorks2022 DML. Tato aktivační událost zkontroluje, jestli je hodnocení kredibility pro dodavatele dobré (ne 5), když se do tabulky pokusíte vložit novou nákupní objednávku PurchaseOrderHeader . Pokud chcete získat rating dodavatele, Vendor musí se na tabulku odkazovat. Pokud je hodnocení kreditu příliš nízké, zobrazí se zpráva a vložení se nestane.

USE AdventureWorks2022;
GO

IF OBJECT_ID('Purchasing.LowCredit', 'TR') IS NOT NULL
    DROP TRIGGER Purchasing.LowCredit;
GO

-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).
CREATE TRIGGER Purchasing.LowCredit
ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
    IF (ROWCOUNT_BIG() = 0)
    RETURN;
    IF EXISTS (SELECT 1
        FROM inserted AS i
        INNER JOIN Purchasing.Vendor AS v
            ON v.BusinessEntityID = i.VendorID
            WHERE v.CreditRating = 5)
BEGIN
    RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
    ROLLBACK;
    RETURN;
END
GO

-- This statement attempts to insert a row into the PurchaseOrderHeader table  
-- for a vendor that has a below average credit rating.  
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.  

INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,
    VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
VALUES (2, 3, 261, 1652, 4, GETDATE(), GETDATE(), 44594.55, 3567.564, 1114.8638);
GO

D. Použití triggeru DDL s oborem databáze

Následující příklad používá trigger DDL, aby se zabránilo vyřazení synonym v databázi.

CREATE TRIGGER safety
    ON DATABASE
    FOR DROP_SYNONYM
    AS IF (@@ROWCOUNT = 0)
           RETURN;
       RAISERROR ('You must disable Trigger "safety" to remove synonyms!', 10, 1);
       ROLLBACK;
GO

DROP TRIGGER safety
    ON DATABASE;
GO

E. Použití triggeru DDL s oborem serveru

Následující příklad používá trigger DDL k vytištění zprávy, pokud dojde k nějaké CREATE DATABASE události v aktuální instanci serveru a pomocí EVENTDATA funkce načte text odpovídajícího příkazu Transact-SQL. Další příklady, které se používají EVENTDATA v triggerech DDL, najdete v tématu Použití funkce EVENTDATA.

CREATE TRIGGER ddl_trig_database
    ON ALL SERVER
    FOR CREATE_DATABASE
    AS PRINT 'Database Created.';
       SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)');
GO

DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
GO

F. Použití triggeru přihlášení

Následující příklad triggeru přihlášení odmítne pokus o přihlášení k SQL Serveru jako člena login_test přihlášení, pokud již existují tři uživatelské relace spuštěné v rámci tohoto přihlášení. Změňte <password> na silné heslo.

USE master;
GO

CREATE LOGIN login_test
    WITH PASSWORD = '<password>' MUST_CHANGE, CHECK_EXPIRATION = ON;
GO

GRANT VIEW SERVER STATE TO login_test;
GO

CREATE TRIGGER connection_limit_trigger
    ON ALL SERVER
    WITH EXECUTE AS 'login_test'
    FOR LOGON
    AS BEGIN
           IF ORIGINAL_LOGIN() = 'login_test'
              AND (SELECT COUNT(*)
                   FROM sys.dm_exec_sessions
                   WHERE is_user_process = 1
                         AND original_login_name = 'login_test') > 3
               ROLLBACK;
       END

G. Zobrazení událostí, které způsobují aktivaci triggeru

Následující příklad dotazuje sys.triggers zobrazení katalogu a sys.trigger_events určí, které Transact-SQL událostí jazyka způsobí aktivaci triggeru safety . Trigger , safetyje vytvořen v příkladu D. Použijte trigger DDL s oborem databáze.

SELECT TE.*
FROM sys.trigger_events AS TE
     INNER JOIN sys.triggers AS T
         ON T.object_id = TE.object_id
WHERE T.parent_class = 0
      AND T.name = 'safety';
GO

Související obsah