SKAPA UTLÖSARE (Transact-SQL)

Gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Förhandsversion av Microsoft Fabric

Skapar en DML-, DDL- eller inloggningsutlösare. En utlösare är en särskild typ av lagrad procedur som körs automatiskt när en händelse inträffar på databasservern. DML-utlösare körs när en användare försöker ändra data via en DML-händelse (Data Manipulation Language). DML-händelser är INSERT, UPDATEeller DELETE -instruktioner i en tabell eller vy. Dessa utlösare utlöses när en giltig händelse utlöses, oavsett om tabellrader påverkas eller inte. Mer information finns i DML-utlösare.

DDL-utlösare körs som svar på olika DDL-händelser (Data Definition Language). Dessa händelser motsvarar främst Transact-SQL CREATE, ALTERoch DROP -instruktioner och vissa system lagrade procedurer som utför DDL-liknande åtgärder.

Inloggning utlöser eld som svar på den LOGON händelse som utlöses när en användares session upprättas. Du kan skapa utlösare direkt från Transact-SQL-instruktioner eller från metoder för sammansättningar som skapas i Microsoft .NET Framework common language runtime (CLR) och laddas upp till en instans av SQL Server. Med SQL Server kan du skapa flera utlösare för en specifik instruktion.

Transact-SQL syntaxkonventioner

Syntax

SQL Server-syntax

Utlösare för en INSERT, UPDATE, eller DELETE -instruktion till en tabell eller vy (DML-utlösare):

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

Utlösare för en INSERT, UPDATE, eller DELETE -instruktion till en tabell (DML-utlösare för minnesoptimerade tabeller):

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 ]

Utlösare för en CREATE, ALTER, DROP, GRANT, DENY, eller REVOKEUPDATE-instruktion (DDL-utlösare):

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 ]

Utlösare för en LOGON händelse (inloggningsutlösare):

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 ]

Azure SQL Database eller SQL Database i Fabric-syntax

Utlösare för en INSERT, UPDATE, eller DELETE -instruktion till en tabell eller vy (DML-utlösare):

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 ]

Utlösare för en CREATE, ALTER, DROP, GRANT, DENY, eller REVOKEUPDATE STATISTICS-instruktion (DDL-utlösare):

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 ]

Argumentpunkter

ELLER ÄNDRA

Gäller för: SQL Server 2016 (13.x) SP1 och senare versioner, Azure SQL Database och SQL Database i Microsoft Fabric Preview.

Villkorligt ändrar utlösaren endast om den redan finns.

schema_name

Namnet på schemat som en DML-utlösare tillhör. DML-utlösare är begränsade till schemat för tabellen eller vyn där de skapas. schema_name kan inte anges för DDL- eller inloggningsutlösare.

trigger_name

Namnet på utlösaren. En trigger_name måste följa reglerna för identifierare, förutom att trigger_name inte kan börja med # eller ##.

bord | utsikt

Tabellen eller vyn där DML-utlösaren körs. Den här tabellen eller vyn kallas ibland för utlösartabellen eller utlösarvyn. Det är valfritt att ange det fullständigt kvalificerade namnet på tabellen eller vyn. Du kan bara referera till en vy med en INSTEAD OF utlösare. Du kan inte definiera DML-utlösare i lokala eller globala temporära tabeller.

DATABAS

Tillämpar omfånget för en DDL-utlösare på den aktuella databasen. Om det anges utlöses utlösaren när event_type eller event_group inträffar i den aktuella databasen.

ALL SERVER

Tillämpar omfånget för en DDL- eller inloggningsutlösare på den aktuella servern. Om det anges utlöses utlösaren när event_type eller event_group inträffar någonstans på den aktuella servern.

MED KRYPTERING

Döljer texten i -instruktionen CREATE TRIGGER . Med hjälp WITH ENCRYPTION av förhindras utlösaren från att publiceras som en del av SQL Server-replikering. WITH ENCRYPTION kan inte anges för CLR-utlösare.

KÖR SOM

Anger säkerhetskontexten under vilken utlösaren körs. Gör att du kan styra vilket användarkonto som SQL Server-instansen använder för att verifiera behörigheter för alla databasobjekt som refereras till av utlösaren.

Det här alternativet krävs för utlösare i minnesoptimerade tabeller.

Mer information finns i EXECUTE AS-satsen.

NATIVE_COMPILATION

Anger att utlösaren är internt kompilerad.

Det här alternativet krävs för utlösare i minnesoptimerade tabeller.

SCHEMABINDNING

Ser till att tabeller som refereras av en utlösare inte kan tas bort eller ändras.

Det här alternativet krävs för utlösare i minnesoptimerade tabeller och stöds inte för utlösare i traditionella tabeller.

FÖR | EFTER

FOR eller AFTER anger att DML-utlösaren endast utlöses när alla åtgärder som anges i den utlösande SQL-instruktionen har startats. Alla kaskadåtgärder och villkorskontroller för referent måste också lyckas innan utlösaren utlöses.

Du kan inte definiera AFTER utlösare för vyer.

ISTÄLLET FÖR

Anger att DML-utlösaren startas i stället för den utlösande SQL-instruktionen, vilket åsidosätter åtgärderna i utlösande instruktioner. Du kan inte ange INSTEAD OF för DDL- eller inloggningsutlösare.

Som mest kan du definiera en INSTEAD OF utlösare per INSERT, UPDATEeller DELETE -instruktion i en tabell eller vy. Du kan också definiera vyer för vyer där varje vy har en egen INSTEAD OF utlösare.

Du kan inte definiera INSTEAD OF utlösare för uppdateringsbara vyer som använder WITH CHECK OPTION. Detta resulterar i ett fel när en INSTEAD OF utlösare läggs till i en uppdaterad vy WITH CHECK OPTION som anges. Du tar bort det alternativet med hjälp ALTER VIEW av innan du definierar utlösaren INSTEAD OF .

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

Anger de datamodifieringsinstruktioner som aktiverar DML-utlösaren när den testas mot den här tabellen eller vyn. Ange minst ett alternativ. Använd valfri kombination av dessa alternativ i valfri ordning i utlösardefinitionen.

För INSTEAD OF utlösare kan du inte använda DELETE alternativet för tabeller som har en referensrelation, vilket anger en kaskadåtgärd ON DELETE. UPDATE På samma sätt tillåts inte alternativet för tabeller som har en referensrelation, vilket anger en kaskadåtgärd ON UPDATE.

MED TILLÄGG

Gäller för: SQL Server 2008 (10.0.x) via SQL Server 2008 R2 (10.50.x).

Anger att ytterligare en utlösare av en befintlig typ ska läggas till. WITH APPEND kan inte användas med INSTEAD OF utlösare eller om en AFTER utlösare uttryckligen anges. För bakåtkompatibilitet använder du WITH APPEND endast när FOR har angetts, utan INSTEAD OF eller AFTER. Du kan inte ange WITH APPEND om du använder EXTERNAL NAME (det vill: om utlösaren är en CLR-utlösare).

event_type

Namnet på en Transact-SQL språkhändelse som efter start orsakar en DDL-utlösare att utlösas. Giltiga händelser för DDL-utlösare visas i DDL-händelser.

event_group

Namnet på en fördefinierad gruppering av Transact-SQL språkhändelser. DDL-utlösaren utlöses efter lanseringen av alla Transact-SQL språkhändelser som tillhör event_group. Giltiga händelsegrupper för DDL-utlösare visas i DDL-händelsegrupper.

När körningen CREATE TRIGGER är klar fungerar event_group också som ett makro genom att lägga till de händelsetyper som beskrivs i sys.trigger_events katalogvyn.

INTE FÖR REPLIKERING

Anger att utlösaren inte ska köras när en replikeringsagent ändrar tabellen som är involverad i utlösaren.

sql_statement

Utlösarvillkor och -åtgärder. Utlösarvillkor anger ytterligare villkor som avgör om de provade DML-, DDL- eller inloggningshändelserna orsakar att utlösaråtgärderna körs.

Utlösaråtgärderna som anges i Transact-SQL-uttrycken träder i kraft när åtgärden provas.

Utlösare kan innehålla valfritt antal och typer av Transact-SQL-instruktioner, med undantag. Mer information finns i Anmärkningar. En utlösare är utformad för att kontrollera eller ändra data baserat på en instruktion för dataändring eller definition. Utlösaren ska inte returnera data till användaren. De Transact-SQL-uttrycken i en utlösare innehåller ofta kontroll av flödesspråk.

DML-utlösare använder de borttagna och infogade logiska tabellerna (konceptuella). De är strukturellt lika tabellen där utlösaren definieras, dvs. tabellen där användaråtgärden provas. De borttagna och infogade tabellerna innehåller gamla värden eller nya värden för de rader som kan ändras av användaråtgärden. Om du till exempel vill hämta alla värden i deleted tabellen använder du:

SELECT * FROM deleted;

Mer information finns i Använda de infogade och borttagna tabellerna.

DDL- och inloggningsutlösare samlar in information om utlösande händelse med hjälp av funktionen EVENTDATA . Mer information finns i Använda funktionen EVENTDATA.

SQL Server tillåter uppdatering av text-, ntext- eller bildkolumner via utlösaren INSTEAD OF i tabeller eller vyer.

För utlösare i minnesoptimerade tabeller är det enda sql_statement som tillåts på den översta nivån ett ATOMIC block. Den T-SQL som tillåts i ATOMIC blocket begränsas av den T-SQL som tillåts i interna processer.

<method_specifier>

För en CLR-utlösare anger metoden för en sammansättning som ska bindas till utlösaren. Metoden får inte ta några argument och returnera void. class_name måste vara en giltig SQL Server-identifierare och måste finnas som en klass i sammansättningen med sammansättningssynlighet. Om klassen har ett namnområdeskvalificerat namn som används . för att separera namnområdesdelar måste klassnamnet avgränsas med hjälp av [ ] eller " " avgränsare. Klassen kan inte vara en kapslad klass.

Kommentarer för DML-utlösare

DML-utlösare används ofta för att framtvinga affärsregler och dataintegritet. SQL Server tillhandahåller deklarativ referensintegritet (DRI) via ALTER TABLE - och-instruktioner CREATE TABLE . DRI tillhandahåller dock inte referensintegritet mellan databaser. Referensintegritet refererar till reglerna om relationerna mellan tabellernas primära och externa nycklar. Om du vill framtvinga referensintegritet använder PRIMARY KEY du begränsningarna och FOREIGN KEY i ALTER TABLE och CREATE TABLE. Om det finns begränsningar i utlösartabellen kontrolleras de efter att utlösaren INSTEAD OF har körts och innan utlösaren AFTER körs. Om begränsningarna överträds återställs utlösaråtgärderna INSTEAD OF och utlösaren AFTER utlöses inte.

Du kan ange de första och sista AFTER utlösarna som ska köras i en tabell med hjälp sp_settriggerorderav . Du kan bara ange en första och en sista AFTER utlösare för varje INSERT, UPDATEoch DELETE åtgärd i en tabell. Om det finns andra AFTER utlösare i samma tabell körs de slumpmässigt.

Om en ALTER TRIGGER instruktion ändrar en första eller sista utlösare tas det första eller sista attributet som angetts för den ändrade utlösaren bort och du måste återställa ordervärdet med hjälp sp_settriggerorderav .

En AFTER utlösare körs endast efter att den utlösande SQL-instruktionen har körts. Den här lyckade körningen omfattar alla referensåtgärder och villkorskontroller som är associerade med objektet som har uppdaterats eller tagits bort. En AFTER utlöser inte rekursivt en INSTEAD OF utlösare i samma tabell.

Om en INSTEAD OF utlösare som definierats i en tabell kör en instruktion mot tabellen som normalt INSTEAD OF utlöser utlösaren igen anropas inte utlösaren rekursivt. I stället bearbetas instruktionen som om tabellen inte hade någon INSTEAD OF utlösare och startar kedjan med begränsningsåtgärder och AFTER utlösarkörningar. Om en utlösare till exempel definieras som en INSTEAD OF INSERT utlösare för en tabell. Och om utlösaren kör en INSERT -instruktion i samma tabell anropar inte -instruktionen INSERTINSTEAD OF som startades av utlösaren utlösaren igen. Utlösaren INSERT startar processen med att köra begränsningsåtgärder och utlösa utlösare AFTER INSERT som definierats för tabellen.

När en INSTEAD OF utlösare som definierats i en vy kör en instruktion mot vyn som normalt utlöser utlösaren igen, anropas den INSTEAD OF inte rekursivt. I stället löses -instruktionen som ändringar mot bastabellerna som ligger bakom vyn. I det här fallet måste vydefinitionen uppfylla alla begränsningar för en uppdaterad vy. En definition av uppdateringsbara vyer finns i Ändra data via en vy.

Om en utlösare till exempel definieras som en INSTEAD OF UPDATE utlösare för en vy. Och utlösaren kör en UPDATE -instruktion som refererar till samma vy, instruktionen UPDATEINSTEAD OF som startas av utlösaren anropar inte utlösaren igen. Utlösaren UPDATE som startades bearbetas mot vyn som om vyn inte hade någon INSTEAD OF utlösare. Kolumnerna som ändras av UPDATE måste matchas till en enda bastabell. Varje ändring av en underliggande bastabell startar kedjan med att tillämpa begränsningar och utlösare AFTER som definierats för tabellen.

Testa uppdaterings- eller INSERT-åtgärder för specifika kolumner

Du kan utforma en Transact-SQL utlösare för att utföra vissa åtgärder baserat på UPDATE eller INSERT ändringar av specifika kolumner. Använd UPDATE eller COLUMNS_UPDATED i utlösarens brödtext för detta ändamål. UPDATE() tester för UPDATE eller INSERT försök på en kolumn. COLUMNS_UPDATED tester för UPDATE eller INSERT åtgärder som körs på flera kolumner. Den här funktionen returnerar ett bitmönster som anger vilka kolumner som har infogats eller uppdaterats.

Utlösarbegränsningar

CREATE TRIGGER måste vara den första instruktionen i batchen och kan endast tillämpas på en tabell.

En utlösare skapas endast i den aktuella databasen. En utlösare kan dock referera till objekt utanför den aktuella databasen.

Om namnet på utlösarschemat har angetts för att kvalificera utlösaren kvalificerar du tabellnamnet på samma sätt.

Samma utlösaråtgärd kan definieras för mer än en användaråtgärd (till exempel INSERT och UPDATE) i samma CREATE TRIGGER instruktion.

INSTEAD OF DELETE / INSTEAD OF UPDATE utlösare kan inte definieras i en tabell som har en sekundärnyckel med en kaskad på DELETE/UPDATE åtgärden definierad.

En SET-instruktion kan anges i en utlösare. Det valda SET-alternativet gäller fortfarande under körningen av utlösaren och återgår sedan till den tidigare inställningen.

När en utlösare utlöses returneras resultaten till det anropande programmet, precis som med lagrade procedurer. Om du vill förhindra att resultat returneras till ett program på grund av en utlösare som utlöses ska du inte inkludera några SELECT instruktioner som returnerar resultat eller instruktioner som utför variabeltilldelning i en utlösare. En utlösare som innehåller antingen SELECT instruktioner som returnerar resultat till användaren eller -instruktioner som utför variabeltilldelning, kräver särskild hantering. Du måste skriva de returnerade resultaten till varje program där ändringar i utlösartabellen tillåts. Om variabeltilldelning måste ske i en utlösare använder du en SET NOCOUNT -instruktion i början av utlösaren för att förhindra att resultatuppsättningar returneras.

Även om en TRUNCATE TABLE instruktion är i praktiken en DELETE -instruktion aktiveras inte en utlösare eftersom åtgärden inte loggar enskilda radborttagningar. Men endast de användare som har behörighet att köra en TRUNCATE TABLE instruktion behöver oroa sig för att oavsiktligt kringgå en DELETE utlösare på det här sättet.

Instruktionen WRITETEXT , oavsett om den är loggad eller ologgad, aktiverar ingen utlösare.

Följande Transact-SQL-instruktioner tillåts inte i en DML-utlösare:

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

Dessutom tillåts inte följande Transact-SQL-instruktioner i brödtexten i en DML-utlösare när den används mot tabellen eller vyn som är målet för utlösande åtgärden.

• CREATE INDEX (inklusive CREATE SPATIAL INDEX och CREATE XML INDEX)
• ALTER INDEX
• DROP INDEX
• DROP TABLE
• DBCC DBREINDEX
• ALTER PARTITION FUNCTION
• ALTER TABLE när det används för att utföra följande åtgärder:
  • Lägg till, ändra eller ta bort kolumner.
  • Växla partitioner.
  • Lägg till eller släpp PRIMARY KEY eller UNIQUE begränsningar.

Optimera DML-utlösare

Utlösare fungerar i transaktioner (underförstådda eller på annat sätt) och när de är öppna låser de resurser. Låset förblir på plats tills transaktionen har bekräftats (med COMMIT) eller avvisats (med en ROLLBACK). Ju längre en utlösare körs, desto högre är sannolikheten att en annan process sedan blockeras. Skriv utlösare för att minska varaktigheten när det är möjligt. Ett sätt att uppnå kortare varaktighet är att släppa en utlösare när en DML-instruktion ändrar noll rader.

Om du vill frigöra utlösaren för ett kommando som inte ändrar några rader använder du systemvariabeln ROWCOUNT_BIG.

Följande T-SQL-kodfragment visar hur du släpper utlösaren för ett kommando som inte ändrar några rader. Den här koden ska finnas i början av varje DML-utlösare:

IF (ROWCOUNT_BIG() = 0)
RETURN;

Kommentarer för DDL-utlösare

DDL-utlösare, till exempel standardutlösare, startar lagrade procedurer som svar på en händelse. Men till skillnad från standardutlösare körs de inte som UPDATEsvar på , INSERTeller DELETE -instruktioner i en tabell eller vy. I stället körs de främst som svar på DDL-instruktioner (Data Definition Language). Instruktionstyperna är CREATE, ALTER, DROP, GRANT, DENY, REVOKEoch UPDATE STATISTICS. Vissa system lagrade procedurer som utför DDL-liknande åtgärder kan också utlösa DDL-utlösare.

Mer information om DDL-utlösare finns i DDL-utlösare.

DDL-utlösare utlöses inte som svar på händelser som påverkar lokala eller globala temporära tabeller och lagrade procedurer.

Till skillnad från DML-utlösare är DDL-utlösare inte begränsade till scheman. Därför kan du inte använda funktioner som OBJECT_ID, OBJECT_NAME, OBJECTPROPERTYoch OBJECTPROPERTYEX för att fråga efter metadata om DDL-utlösare. Använd katalogvyerna i stället. Mer information finns i Hämta information om DDL-utlösare.

Inloggningsutlösare

Inloggningsutlösare utför lagrade procedurer som svar på en LOGON händelse. Den här händelsen inträffar när en användarsession upprättas med en instans av SQL Server. Inloggning utlöser eld när autentiseringsfasen för loggning har slutförts, men innan användarsessionen upprättas. Därför omdirigeras alla meddelanden som kommer från utlösaren som vanligtvis når användaren, till exempel felmeddelanden och meddelanden från -instruktionen PRINT , till SQL Server-felloggen. Mer information finns i Inloggningsutlösare.

Inloggningsutlösare utlöses inte om autentiseringen misslyckas.

Distribuerade transaktioner stöds inte i en inloggningsutlösare. Fel 3969 returneras när en inloggningsutlösare som innehåller en distribuerad transaktionsbrand.

Inaktivera en inloggningsutlösare

En inloggningsutlösare kan effektivt förhindra lyckade anslutningar till databasmotorn för alla användare, inklusive medlemmar i den fasta serverrollen sysadmin . När en inloggningsutlösare förhindrar anslutningar kan medlemmar i den fasta sysadmin-serverrollen ansluta med hjälp av den dedikerade administratörsanslutningen eller genom att starta databasmotorn i minimalt konfigurationsläge (-f). Mer information finns i startalternativ för Database Engine Service.

Allmänna överväganden för utlösare

Returnera resultat

Möjligheten att returnera resultat från utlösare tas bort i en framtida version av SQL Server. Utlösare som returnerar resultatuppsättningar kan orsaka oväntat beteende i program som inte är utformade för att fungera med dem. Undvik att returnera resultatuppsättningar från utlösare i nytt utvecklingsarbete och planera att ändra program som för närvarande gör det. Om du vill förhindra att utlösare returnerar resultatuppsättningar anger du alternativet tillåtna resultat från utlösare till 1.

Inloggningsutlösare tillåter alltid inte att resultatuppsättningar returneras och det här beteendet kan inte konfigureras. Om en inloggningsutlösare genererar en resultatuppsättning kan utlösaren inte startas och inloggningsförsöket som utlöste utlösaren nekas.

Flera utlösare

Med SQL Server kan du skapa flera utlösare för varje DML, DDL eller LOGON händelse. Om till exempel CREATE TRIGGER FOR UPDATE körs för en tabell som redan har en UPDATE utlösare skapas en ytterligare uppdateringsutlösare. I tidigare versioner av SQL Server tillåts endast en utlösare för varje INSERT, UPDATEeller DELETE dataändringshändelse för varje tabell.

Rekursiva utlösare

SQL Server stöder också rekursiv anrop av utlösare när RECURSIVE_TRIGGERS inställningen är aktiverad med hjälp av ALTER DATABASE.

Rekursiva utlösare gör att följande typer av rekursion kan inträffa:

• Indirekt rekursion: Med indirekt rekursion uppdaterar ett program tabellen T1. Detta utlöser utlösaren TR1, uppdaterar tabellen T2. Utlösaren T2 utlöses och uppdaterar sedan tabellen T1.

• Direkt rekursion: Vid direkt rekursion uppdaterar T1tabellen program . Detta utlöser utlösaren TR1, uppdaterar tabellen T1. Eftersom tabellen T1 har uppdaterats utlöses utlösaren TR1 igen och så vidare.

I följande exempel används både indirekt och direkt utlösarrekursion Anta att två uppdateringsutlösare TR1 och TR2, definieras i tabellen T1. Utlösaren TR1 uppdaterar tabellen T1 rekursivt. En UPDATE instruktion körs varje TR1TR2 gång. Dessutom utlöser lanseringen av TR1 körningen av TR1 (rekursivt) och TR2. De infogade och borttagna tabellerna för en specifik utlösare innehåller rader som endast motsvarar instruktionen UPDATE som anropade utlösaren.

Om du inaktiverar inställningen RECURSIVE_TRIGGERS förhindras endast direkta rekursioner. Om du också vill inaktivera indirekt rekursion anger du serveralternativet kapslade utlösare till 0 med hjälp sp_configureav .

Om någon av utlösarna utför en ROLLBACK TRANSACTION, oavsett kapslingsnivå, körs inga fler utlösare.

Kapslade utlösare

Du kan kapsla utlösare till högst 32 nivåer. Om en utlösare ändrar en tabell där det finns en annan utlösare aktiveras den andra utlösaren och kan sedan anropa en tredje utlösare och så vidare. Om någon utlösare i kedjan utlöser en oändlig loop överskrids kapslingsnivån och utlösaren avbryts. När en Transact-SQL utlösare startar hanterad kod genom att referera till en CLR-rutin, typ eller aggregering räknas den här referensen som en nivå mot kapslingsgränsen på 32 nivåer. Metoder som anropas inifrån hanterad kod räknas inte mot den här gränsen.

Om du vill inaktivera kapslade utlösare ställer du in alternativet sp_configure kapslade utlösare på 0 (av). Standardkonfigurationen stöder kapslade utlösare. Om kapslade utlösare är inaktiverade inaktiveras även rekursiva utlösare, trots den RECURSIVE_TRIGGERS inställning som anges med hjälp ALTER DATABASEav .

Den första AFTER utlösaren som är kapslad i en INSTEAD OF utlösare utlöses även om konfigurationsalternativet för kapslade utlösare är 0. Men under den här inställningen utlöses inte de senare AFTER utlösarna. Granska dina program för kapslade utlösare för att avgöra om programmen följer dina affärsregler när serverkonfigurationsalternativet för kapslade utlösare är inställt på 0. Om inte, gör du lämpliga ändringar.

Uppskjuten namnmatchning

MED SQL Server kan Transact-SQL lagrade procedurer, utlösare, funktioner och batchar referera till tabeller som inte finns vid kompileringstiden. Den här möjligheten kallas för uppskjuten namnmatchning.

Behörigheter

För att skapa en DML-utlösare krävs ALTER behörighet i tabellen eller vyn där utlösaren skapas.

Om du vill skapa en DDL-utlösare med serveromfång (ON ALL SERVER) eller en inloggningsutlösare kräver CONTROL SERVER du behörighet på servern. För att skapa en DDL-utlösare med databasomfånget (ON DATABASE) krävs ALTER ANY DATABASE DDL TRIGGER behörighet i den aktuella databasen.

Exempel

A. Använda en DML-utlösare med ett påminnelsemeddelande

Följande DML-utlösare skriver ut ett meddelande till klienten när någon försöker lägga till eller ändra data i Customer tabellen i databasen AdventureWorks2022.

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

B. Använda en DML-utlösare med ett påminnelsemeddelande via e-post

I följande exempel skickas ett e-postmeddelande till en angiven person (MaryM) när Customer tabellen ändras.

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. Använda en DML AFTER-utlösare för att framtvinga en affärsregel mellan tabellerna PurchaseOrderHeader och Vendor

Eftersom CHECK begränsningar endast refererar till de kolumner där villkoret på kolumnnivå eller tabellnivå definieras, måste du definiera eventuella begränsningar mellan tabeller (i det här fallet affärsregler) som utlösare.

I följande exempel skapas en DML-utlösare i AdventureWorks2022 databasen. Den här utlösaren kontrollerar att kreditbetyget för leverantören är bra (inte 5) när det görs ett försök att infoga en ny inköpsorder i PurchaseOrderHeader tabellen. För att få leverantörens Vendor kreditvärdighet måste tabellen refereras till. Om kreditbetyget är för lågt visas ett meddelande och infogningen sker inte.

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. Använda en DDL-utlösare med databasomfattning

I följande exempel används en DDL-utlösare för att förhindra att synonymer i en databas tas bort.

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. Använda en DDL-utlösare med serveromfattning

I följande exempel används en DDL-utlösare för att skriva ut ett meddelande om någon CREATE DATABASE händelse inträffar på den aktuella serverinstansen EVENTDATA och använder funktionen för att hämta texten i motsvarande Transact-SQL-instruktion. Fler exempel som används EVENTDATA i DDL-utlösare finns i Använda funktionen 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. Använda en inloggningsutlösare

I följande exempel på inloggningsutlösare nekas ett försök att logga in på SQL Server som medlem i inloggningen login_test om det redan finns tre användarsessioner som körs under inloggningen. Ändra <password> till ett starkt lösenord.

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. Visa de händelser som orsakar att en utlösare utlöses

I följande exempel frågas katalogvyerna sys.triggers och sys.trigger_events för att avgöra vilka Transact-SQL språkhändelser som orsakar utlösaren att utlösare utlöses safety . Utlösaren, safety, skapas i exempel D. Använd en DDL-utlösare med databasomfattning.

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

Relaterat innehåll

• ALTER TABLE (Transact-SQL)
• ALTER TRIGGER (Transact-SQL)
• COLUMNS_UPDATED (Transact-SQL)
• SKAPA TABELL (Transact-SQL)
• DROP TRIGGER (Transact-SQL)
• AKTIVERA UTLÖSARE (Transact-SQL)
• INAKTIVERA UTLÖSARE (Transact-SQL)
• TRIGGER_NESTLEVEL (Transact-SQL)
• HÄNDELSEDATA (Transact-SQL)
• sys.dm_sql_referenced_entities
• sys.dm_sql_referencing_entities
• sys.sql_expression_dependencies
• sp_help
• sp_helptrigger
• sp_helptext
• sp_rename
• sp_settriggerorder
• UPDATE – Utlösarfunktioner (Transact-SQL)
• Hämta information om DML-trigger
• Hämta information om DDL-utlösare
• sys.triggers
• sys.trigger_events
• sys.sql_modules
• sys.assembly_modules
• sys.server_triggers
• sys.server_trigger_events
• sys.server_sql_modules
• sys.server_assembly_modules