CREATE TRIGGER (Transact-SQL)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Hiermee maakt u een DML-, DDL- of aanmeldingstrigger. Een trigger is een speciaal type opgeslagen procedure dat automatisch wordt uitgevoerd wanneer een gebeurtenis plaatsvindt op de databaseserver. DML-triggers worden uitgevoerd wanneer een gebruiker gegevens probeert te wijzigen via een DML-gebeurtenis (Data Manipulat Language). DML-gebeurtenissen zijn INSERT, UPDATEof DELETE instructies in een tabel of weergave. Deze triggers worden geactiveerd wanneer een geldige gebeurtenis wordt geactiveerd, ongeacht of tabelrijen worden beïnvloed of niet. Zie DML-triggersvoor meer informatie.

DDL-triggers worden uitgevoerd als reactie op verschillende DDL-gebeurtenissen (Data Definition Language). Deze gebeurtenissen komen voornamelijk overeen met Transact-SQL CREATE, ALTERen DROP instructies en bepaalde door het systeem opgeslagen procedures die DDL-achtige bewerkingen uitvoeren.

Aanmelding wordt geactiveerd als reactie op de LOGON gebeurtenis die wordt gegenereerd wanneer de sessie van een gebruiker tot stand wordt gebracht. U kunt triggers rechtstreeks maken vanuit Transact-SQL instructies of van methoden van assembly's die zijn gemaakt in de Common Language Runtime (CLR) van Microsoft .NET Framework en geüpload naar een exemplaar van SQL Server. Met SQL Server kunt u meerdere triggers maken voor een specifieke instructie.

Transact-SQL syntaxis-conventies

Syntaxis

SQL Server-syntaxis

Activeren op een INSERT, UPDATEof DELETE instructie voor een tabel of weergave (DML-trigger):

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

Activeren op een INSERT, UPDATEof DELETE instructie naar een tabel (DML-trigger voor tabellen die zijn geoptimaliseerd voor geheugen):

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 ]

Trigger op een CREATE, ALTER, , GRANTDROP, , DENY, of UPDATEREVOKEinstructie (DDL-trigger):

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 ]

Activeren voor een LOGON gebeurtenis (aanmeldingstrigger):

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-syntaxis

Activeren op een INSERT, UPDATEof DELETE instructie voor een tabel of weergave (DML-trigger):

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 ]

Trigger op een CREATE, ALTER, , GRANTDROP, , DENY, of UPDATE STATISTICSREVOKEinstructie (DDL-trigger):

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 ]

Argumenten

OF ALTER

Van toepassing op: SQL Server 2016 (13.x) SP1 en latere versies, en Azure SQL Database

Wijzigt de trigger alleen voorwaardelijk als deze al bestaat.

schema_name

De naam van het schema waartoe een DML-trigger behoort. DML-triggers zijn gericht op het schema van de tabel of weergave waarop ze worden gemaakt. schema_name kan niet worden opgegeven voor DDL- of aanmeldingstriggers.

trigger_name

De naam van de trigger. Een trigger_name moet de regels voor id's volgen, behalve dat trigger_name niet met of ##kan beginnen#.

tafel | bekijken

De tabel of weergave waarop de DML-trigger wordt uitgevoerd. Deze tabel of weergave wordt ook wel de triggertabel of triggerweergave genoemd. Het opgeven van de volledig gekwalificeerde naam van de tabel of weergave is optioneel. U kunt alleen verwijzen naar een weergave door een INSTEAD OF trigger. U kunt geen DML-triggers definiëren voor lokale of globale tijdelijke tabellen.

DATABANK

Hiermee past u het bereik van een DDL-trigger toe op de huidige database. Indien opgegeven, wordt de trigger geactiveerd wanneer event_type of event_group plaatsvindt in de huidige database.

ALLE SERVER

Hiermee past u het bereik van een DDL- of aanmeldingstrigger toe op de huidige server. Indien opgegeven, wordt de trigger geactiveerd wanneer event_type of event_group ergens op de huidige server plaatsvindt.

MET VERSLEUTELING

De tekst van de CREATE TRIGGER instructie wordt verborgen. Hiermee WITH ENCRYPTION voorkomt u dat de trigger wordt gepubliceerd als onderdeel van SQL Server-replicatie. WITH ENCRYPTION kan niet worden opgegeven voor CLR-triggers.

UITVOEREN ALS

Hiermee geeft u de beveiligingscontext op waaronder de trigger wordt uitgevoerd. Hiermee kunt u bepalen welk gebruikersaccount het exemplaar van SQL Server gebruikt om machtigingen te valideren voor databaseobjecten waarnaar wordt verwezen door de trigger.

Deze optie is vereist voor triggers in tabellen die zijn geoptimaliseerd voor geheugen.

Zie EXECUTE AS-component voor meer informatie.

NATIVE_COMPILATION

Geeft aan dat de trigger systeemeigen is gecompileerd.

Deze optie is vereist voor triggers in tabellen die zijn geoptimaliseerd voor geheugen.

SCHEMABINDING

Zorgt ervoor dat tabellen waarnaar wordt verwezen door een trigger niet kunnen worden verwijderd of gewijzigd.

Deze optie is vereist voor triggers in tabellen die zijn geoptimaliseerd voor geheugen en wordt niet ondersteund voor triggers in traditionele tabellen.

FOR | NA

FOR of AFTER geeft aan dat de DML-trigger alleen wordt geactiveerd wanneer alle bewerkingen die zijn opgegeven in de activerende SQL-instructie zijn gestart. Alle referentiële trapsgewijze acties en beperkingscontroles moeten ook slagen voordat deze trigger wordt geactiveerd.

U kunt geen triggers definiëren AFTER voor weergaven.

IN PLAATS VAN

Hiermee geeft u op dat de DML-trigger wordt gestart in plaats van de activerende SQL-instructie, waardoor de acties van de triggerinstructies worden overschreven. U kunt geen DDL- of aanmeldingstriggers opgeven INSTEAD OF .

U kunt maximaal één INSTEAD OF trigger per, UPDATEof DELETE instructie definiëren INSERTin een tabel of weergave. U kunt ook weergaven definiëren voor weergaven waarin elke weergave een eigen INSTEAD OF trigger heeft.

U kunt geen triggers definiëren INSTEAD OF voor updatable weergaven die gebruikmaken van WITH CHECK OPTION. Dit resulteert in een fout wanneer een INSTEAD OF trigger wordt toegevoegd aan een opgegeven updatable weergave WITH CHECK OPTION . U verwijdert deze optie door deze te gebruiken ALTER VIEW voordat u de INSTEAD OF trigger definieert.

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

Hiermee geeft u de instructies voor het wijzigen van gegevens op die de DML-trigger activeren wanneer deze wordt geprobeerd voor deze tabel of weergave. Geef ten minste één optie op. Gebruik een willekeurige combinatie van deze opties in elke volgorde in de triggerdefinitie.

Voor INSTEAD OF triggers kunt u de DELETE optie niet gebruiken voor tabellen met een referentiële relatie, waarbij een trapsgewijze actie ON DELETEwordt opgegeven. Op dezelfde manier is de UPDATE optie niet toegestaan voor tabellen met een referentiële relatie, waarbij een trapsgewijze actie ON UPDATEwordt opgegeven.

MET TOEVOEGEN

Van toepassing op: SQL Server 2008 (10.0.x) tot en met SQL Server 2008 R2 (10.50.x).

Hiermee geeft u op dat een extra trigger van een bestaand type moet worden toegevoegd. WITH APPEND kan niet worden gebruikt met INSTEAD OF triggers of als een AFTER trigger expliciet wordt vermeld. Gebruik voor compatibiliteit met eerdere versies alleen WITH APPEND wanneer FOR is opgegeven, zonder INSTEAD OF of AFTER. U kunt niet opgeven of u EXTERNAL NAME deze gebruikt (dat wil gezegdWITH APPEND, als de trigger een CLR-trigger is).

event_type

De naam van een Transact-SQL taal gebeurtenis die na het starten een DDL-trigger veroorzaakt. Geldige gebeurtenissen voor DDL-triggers worden vermeld in DDL-gebeurtenissen.

event_group

De naam van een vooraf gedefinieerde groepering van Transact-SQL taalgebeurtenissen. De DDL-trigger wordt geactiveerd na het starten van een Transact-SQL taalevenement dat deel uitmaakt van event_group. Geldige gebeurtenisgroepen voor DDL-triggers worden vermeld in DDL-gebeurtenisgroepen.

Nadat de CREATE TRIGGER uitvoering is voltooid, fungeert event_group ook als macro door de gebeurtenistypen toe te voegen die worden behandeld in de sys.trigger_events catalogusweergave.

NIET VOOR REPLICATIE

Geeft aan dat de trigger niet mag worden uitgevoerd wanneer een replicatieagent de tabel wijzigt die bij de trigger betrokken is.

sql_statement

De triggervoorwaarden en acties. Triggervoorwaarden geven aanvullende criteria op die bepalen of de geprobeerde DML-, DDL- of aanmeldingsevenementen ertoe leiden dat de triggeracties worden uitgevoerd.

De triggeracties die in de Transact-SQL-instructies zijn opgegeven, worden van kracht wanneer de bewerking wordt uitgevoerd.

Triggers kunnen elk willekeurig aantal en het type Transact-SQL instructies bevatten, met uitzonderingen. Zie Opmerkingen voor meer informatie. Een trigger is ontworpen om gegevens te controleren of te wijzigen op basis van een gegevenswijziging of definitie-instructie. De trigger mag geen gegevens retourneren aan de gebruiker. De Transact-SQL-instructies in een trigger bevatten vaak controle-van-stroomtaal.

DML-triggers gebruiken de verwijderde en ingevoegde logische (conceptuele) tabellen. Ze zijn structureel vergelijkbaar met de tabel waarop de trigger is gedefinieerd, dat wil gezegd de tabel waarop de gebruikersactie wordt geprobeerd. De verwijderde en ingevoegde tabellen bevatten de oude waarden of nieuwe waarden van de rijen die kunnen worden gewijzigd door de gebruikersactie. Als u bijvoorbeeld alle waarden in de deleted tabel wilt ophalen, gebruikt u:

SELECT * FROM deleted;

Zie De ingevoegde en verwijderde tabellen gebruiken voor meer informatie.

DDL- en aanmeldingstriggers leggen informatie vast over de triggerende gebeurtenis met behulp van de functie EVENTDATA . Zie De functie EVENTDATA gebruiken voor meer informatie.

Met SQL Server kunnen tekst-, ntekst- of afbeeldingskolommen worden bijgewerkt via de INSTEAD OF trigger voor tabellen of weergaven.

Voor triggers in tabellen die zijn geoptimaliseerd voor geheugen, is het enige sql_statement dat op het hoogste niveau is toegestaan een ATOMIC blok. De T-SQL die binnen het ATOMIC blok is toegestaan, wordt beperkt door de T-SQL die is toegestaan binnen systeemeigen procs.

<method_specifier>

Voor een CLR-trigger geeft u de methode van een assembly op die moet worden verbonden met de trigger. De methode mag geen argumenten aannemen en ongeldigheid retourneren. class_name moet een geldige SQL Server-id zijn en moet bestaan als een klasse in de assembly met zichtbaarheid van de assembly. Als de klasse een naamruimte-gekwalificeerde naam heeft die wordt gebruikt . voor het scheiden van naamruimteonderdelen, moet de klassenaam worden gescheiden met behulp van [] of " " scheidingstekens. De klasse kan geen geneste klasse zijn.

Opmerkingen voor DML-triggers

DML-triggers worden vaak gebruikt voor het afdwingen van bedrijfsregels en gegevensintegriteit. SQL Server biedt declaratieve referentiële integriteit (DRI) via de ALTER TABLE en CREATE TABLE instructies. DRI biedt echter geen referentiële integriteit tussen databases. Referentiële integriteit verwijst naar de regels over de relaties tussen de primaire en refererende sleutels van tabellen. Als u referentiële integriteit wilt afdwingen, gebruikt u de PRIMARY KEY en FOREIGN KEY beperkingen in ALTER TABLE en CREATE TABLE. Als er beperkingen bestaan in de triggertabel, worden deze gecontroleerd nadat de INSTEAD OF trigger wordt uitgevoerd en voordat de AFTER trigger wordt uitgevoerd. Als de beperkingen worden geschonden, worden de INSTEAD OF triggeracties teruggedraaid en wordt de AFTER trigger niet geactiveerd.

U kunt de eerste en laatste AFTER triggers opgeven die in een tabel moeten worden uitgevoerd met behulp van sp_settriggerorder. U kunt slechts één eerste en één laatste AFTER trigger voor elke INSERT, UPDATEen DELETE bewerking voor een tabel opgeven. Als er andere AFTER triggers in dezelfde tabel staan, worden ze willekeurig uitgevoerd.

Als een ALTER TRIGGER instructie een eerste of laatste trigger wijzigt, wordt het eerste of laatste kenmerk dat is ingesteld op de gewijzigde trigger verwijderd en moet u de orderwaarde opnieuw instellen met behulp van sp_settriggerorder.

Een AFTER trigger wordt pas uitgevoerd nadat de sql-instructie wordt geactiveerd. Deze geslaagde uitvoering omvat alle referentiële trapsgewijze acties en beperkingscontroles die zijn gekoppeld aan het object bijgewerkt of verwijderd. Een AFTER trigger wordt niet recursief INSTEAD OF geactiveerd in dezelfde tabel.

Als een INSTEAD OF trigger die is gedefinieerd in een tabel een instructie uitvoert voor de tabel die de INSTEAD OF trigger normaal gesproken opnieuw zou activeren, wordt de trigger niet recursief aangeroepen. In plaats daarvan wordt de instructie verwerkt alsof de tabel geen INSTEAD OF trigger had en de keten van beperkingsbewerkingen en AFTER triggeruitvoeringen start. Als een trigger bijvoorbeeld is gedefinieerd als een INSTEAD OF INSERT trigger voor een tabel. En als de trigger een INSERT instructie uitvoert in dezelfde tabel, roept de INSERT instructie die door de INSTEAD OF trigger wordt gestart, de trigger niet opnieuw aan. De INSERT trigger start het proces voor het uitvoeren van beperkingsacties en het AFTER INSERT activeren van triggers die zijn gedefinieerd voor de tabel.

Wanneer een INSTEAD OF trigger die is gedefinieerd in een weergave een instructie uitvoert voor de weergave die de trigger normaal gesproken opnieuw zou activeren, wordt deze INSTEAD OF niet recursief aangeroepen. In plaats daarvan wordt de instructie omgezet als wijzigingen ten opzichte van de basistabellen die onder de weergave zijn. In dit geval moet de weergavedefinitie voldoen aan alle beperkingen voor een updatable weergave. Zie Gegevens wijzigen via een weergave voor een definitie van updatable weergaven.

Als een trigger bijvoorbeeld is gedefinieerd als een INSTEAD OF UPDATE trigger voor een weergave. En de trigger voert een UPDATE instructie uit die verwijst naar dezelfde weergave. De UPDATE instructie die door de INSTEAD OF trigger wordt gestart, roept de trigger niet opnieuw aan. De UPDATE trigger die door de trigger wordt gestart, wordt verwerkt in de weergave alsof de weergave geen trigger heeft INSTEAD OF . De kolommen die door de UPDATE kolommen zijn gewijzigd, moeten worden omgezet in één basistabel. Elke wijziging in een onderliggende basistabel begint de keten van het toepassen van beperkingen en het activeren AFTER van triggers die zijn gedefinieerd voor de tabel.

Testen op ACTIES BIJWERKEN of INVOEGEN voor specifieke kolommen

U kunt een Transact-SQL trigger ontwerpen om bepaalde acties uit te voeren op UPDATE basis van of INSERT wijzigingen in specifieke kolommen. Gebruik UPDATE of COLUMNS_UPDATED in de hoofdtekst van de trigger voor dit doel. UPDATE() tests voor UPDATE of INSERT pogingen op één kolom. COLUMNS_UPDATED tests voor UPDATE of INSERT acties die worden uitgevoerd op meerdere kolommen. Deze functie retourneert een bitpatroon dat aangeeft welke kolommen zijn ingevoegd of bijgewerkt.

Triggerbeperkingen

CREATE TRIGGER moet de eerste instructie in de batch zijn en kan slechts op één tabel worden toegepast.

Er wordt alleen een trigger gemaakt in de huidige database; Een trigger kan echter verwijzen naar objecten buiten de huidige database.

Als de naam van het triggerschema is opgegeven om de trigger te kwalificeren, moet u de tabelnaam op dezelfde manier kwalificeren.

Dezelfde triggeractie kan worden gedefinieerd voor meer dan één gebruikersactie (bijvoorbeeld INSERT en UPDATE) in dezelfde CREATE TRIGGER instructie.

INSTEAD OF DELETE / INSTEAD OF UPDATE triggers kunnen niet worden gedefinieerd voor een tabel met een refererende sleutel met een trapsgewijs gedefinieerde DELETE/UPDATE actie.

Elke SET-instructie kan worden opgegeven binnen een trigger. De geselecteerde optie SET blijft van kracht tijdens de uitvoering van de trigger en wordt vervolgens teruggezet naar de vorige instelling.

Wanneer een trigger wordt geactiveerd, worden resultaten geretourneerd naar de aanroepende toepassing, net als bij opgeslagen procedures. Als u wilt voorkomen dat resultaten worden geretourneerd naar een toepassing vanwege een trigger die wordt geactiveerd, moet u geen instructies opnemen SELECT die resultaten of instructies retourneren die variabele toewijzing in een trigger uitvoeren. Een trigger die instructies SELECT bevat die resultaten retourneren aan de gebruiker of instructies die variabele toewijzingen uitvoeren, vereist speciale verwerking. U moet de geretourneerde resultaten naar elke toepassing schrijven waarin wijzigingen in de triggertabel zijn toegestaan. Als variabeletoewijzing moet plaatsvinden in een trigger, gebruikt u een SET NOCOUNT instructie aan het begin van de trigger om het retourneren van resultatensets te voorkomen.

Hoewel een TRUNCATE TABLE instructie van kracht DELETE is, wordt er geen trigger geactiveerd omdat met de bewerking geen afzonderlijke rijverwijderingen worden vastgelegd. Alleen gebruikers met machtigingen om een TRUNCATE TABLE instructie uit te voeren, moeten zich echter zorgen maken over onbedoelde omzeiling van een DELETE trigger op deze manier.

De WRITETEXT instructie, ongeacht of deze is vastgelegd of niet is vastgelegd, activeert geen trigger.

De volgende Transact-SQL instructies zijn niet toegestaan in een DML-trigger:

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

Daarnaast zijn de volgende Transact-SQL instructies niet toegestaan in de hoofdtekst van een DML-trigger wanneer deze wordt gebruikt voor de tabel of weergave die het doel is van de triggeractie.

• CREATE INDEX (inclusief CREATE SPATIAL INDEX en CREATE XML INDEX)
• ALTER INDEX
• DROP INDEX
• DROP TABLE
• DBCC DBREINDEX
• ALTER PARTITION FUNCTION
• ALTER TABLE wanneer deze worden gebruikt om de volgende acties uit te voeren:
  • Kolommen toevoegen, wijzigen of verwijderen.
  • Schakelen tussen partities.
  • Beperkingen toevoegen of UNIQUE verwijderenPRIMARY KEY.

DML-triggers optimaliseren

Triggers werken in transacties (impliciet of anderszins) en terwijl ze geopend zijn, vergrendelen ze resources. De vergrendeling blijft aanwezig totdat de transactie wordt bevestigd (met COMMIT) of geweigerd (met een ROLLBACK). Hoe langer een trigger wordt uitgevoerd, hoe groter de kans dat een ander proces vervolgens wordt geblokkeerd. Schrijftriggers om zo mogelijk hun duur te verminderen. Een manier om een kortere duur te bereiken, is door een trigger vrij te geven wanneer een DML-instructie nul rijen wijzigt.

Als u de trigger wilt vrijgeven voor een opdracht die geen rijen wijzigt, gebruikt u de systeemvariabele ROWCOUNT_BIG.

In het volgende T-SQL-codefragment ziet u hoe u de trigger kunt vrijgeven voor een opdracht die geen rijen wijzigt. Deze code moet aanwezig zijn aan het begin van elke DML-trigger:

IF (ROWCOUNT_BIG() = 0)
RETURN;

Opmerkingen voor DDL-triggers

DDL-triggers, zoals standaardtriggers, starten opgeslagen procedures als reactie op een gebeurtenis. Maar, in tegenstelling tot standaardtriggers, worden ze niet uitgevoerd als reactie op UPDATE, INSERTof DELETE instructies in een tabel of weergave. In plaats daarvan worden ze voornamelijk uitgevoerd als reactie op DDL-instructies (Data Definition Language). De instructietypen omvatten CREATE, , ALTER, DROP, GRANT, DENY, en REVOKE.UPDATE STATISTICS Bepaalde door het systeem opgeslagen procedures die DDL-achtige bewerkingen uitvoeren, kunnen ook DDL-triggers activeren.

Zie DDL-triggers voor meer informatie over DDL-triggers.

DDL-triggers worden niet geactiveerd als reactie op gebeurtenissen die van invloed zijn op lokale of globale tijdelijke tabellen en opgeslagen procedures.

In tegenstelling tot DML-triggers zijn DDL-triggers niet gericht op schema's. U kunt dus geen functies zoals OBJECT_ID, OBJECT_NAMEen OBJECTPROPERTYOBJECTPROPERTYEX voor het uitvoeren van query's op metagegevens over DDL-triggers. Gebruik in plaats daarvan de catalogusweergaven. Zie Informatie over DDL-triggers ophalen voor meer informatie.

Aanmeldingstriggers

Aanmeldingstriggers voeren opgeslagen procedures uit als reactie op een LOGON gebeurtenis. Deze gebeurtenis treedt op wanneer een gebruikerssessie tot stand wordt gebracht met een exemplaar van SQL Server. Aanmelding wordt geactiveerd nadat de verificatiefase van het aanmelden is voltooid, maar voordat de gebruikerssessie tot stand is gebracht. Alle berichten die afkomstig zijn van de trigger die doorgaans de gebruiker bereiken, zoals foutberichten en berichten uit de PRINT instructie, worden dus omgeleid naar het SQL Server-foutenlogboek. Zie Aanmeldingstriggers voor meer informatie.

Aanmeldingstriggers worden niet geactiveerd als verificatie mislukt.

Gedistribueerde transacties worden niet ondersteund in een aanmeldingstrigger. Fout 3969 retourneert wanneer een aanmeldingstrigger die een gedistribueerde transactie brandt.

Een aanmeldingstrigger uitschakelen

Een aanmeldingstrigger kan effectieve verbindingen met de database-engine voor alle gebruikers voorkomen, inclusief leden van de vaste serverfunctie sysadmin . Wanneer een aanmeldingstrigger verbindingen verhindert, kunnen leden van de vaste serverfunctie sysadmin verbinding maken met behulp van de toegewezen beheerdersverbinding of door de database-engine te starten in de minimale configuratiemodus (-f). Zie Database Engine Service-opstartoptiesvoor meer informatie.

Algemene triggeroverwegingen

Resultaten retourneren

De mogelijkheid om resultaten van triggers te retourneren, wordt verwijderd in een toekomstige versie van SQL Server. Triggers die resultatensets retourneren, kunnen onverwacht gedrag veroorzaken in toepassingen die niet zijn ontworpen om ermee te werken. Vermijd het retourneren van resultatensets van triggers in nieuwe ontwikkelwerkzaamheden en plan om toepassingen te wijzigen die momenteel worden uitgevoerd. Als u wilt voorkomen dat triggers resultatensets retourneren, stelt u de optie voor het weigeren van triggers in op 1.

Aanmeldingstriggers weigeren altijd het retourneren van resultatensets en dit gedrag kan niet worden geconfigureerd. Als een aanmeldingstrigger een resultatenset genereert, kan de trigger niet worden gestart en wordt de aanmeldingspoging die de trigger heeft geactiveerd geweigerd.

Meerdere triggers

Met SQL Server kunt u meerdere triggers maken voor elke DML-, DDL- of LOGON gebeurtenis. Als CREATE TRIGGER FOR UPDATE er bijvoorbeeld wordt uitgevoerd voor een tabel die al een UPDATE trigger heeft, wordt er een extra updatetrigger gemaakt. In eerdere versies van SQL Server is slechts één trigger toegestaan voor elke INSERTtabel UPDATEof DELETE voor gegevenswijziging.

Recursieve triggers

SQL Server biedt ook ondersteuning voor recursieve aanroep van triggers wanneer de RECURSIVE_TRIGGERS instelling wordt ingeschakeld met behulp van ALTER DATABASE.

Recursieve triggers maken het mogelijk om de volgende typen recursie op te treden:

• Indirecte recursie: Met indirecte recursie wordt een tabel T1met toepassingsupdates bijgewerkt. Hiermee wordt de trigger geactiveerd TR1, de tabel T2wordt bijgewerkt. Trigger T2 wordt vervolgens geactiveerd en werkt de tabel T1bij.

• Directe recursie: In directe recursie wordt de tabel T1met toepassingsupdates bijgewerkt. Hiermee wordt de trigger geactiveerd TR1, de tabel T1wordt bijgewerkt. Omdat de tabel is bijgewerkt, wordt de trigger T1TR1 opnieuw geactiveerd, enzovoort.

In het volgende voorbeeld wordt zowel indirecte als directe triggerrecursie gebruikt. Stel dat er twee updatetriggers TR1 zijn gedefinieerd in TR2de tabel T1. De TR1 tabel T1 wordt recursief bijgewerkt. Een UPDATE instructie wordt elke TR1 en TR2 één keer uitgevoerd. Daarnaast activeert het starten van TR1 triggers de uitvoering van TR1 (recursief) en TR2. De ingevoegde en verwijderde tabellen voor een specifieke trigger bevatten rijen die alleen overeenkomen met de UPDATE instructie die de trigger heeft aangeroepen.

Als u de RECURSIVE_TRIGGERS instelling uitschakelt, voorkomt u alleen directe recursies. Als u indirecte recursie ook wilt uitschakelen, stelt u de serveroptie geneste triggers in op 0 met behulp van sp_configure.

Als een van de triggers een ROLLBACK TRANSACTION, ongeacht het nestniveau, uitvoert, worden er geen triggers meer uitgevoerd.

Geneste triggers

U kunt triggers nesten tot maximaal 32 niveaus. Als een trigger een tabel wijzigt waarop er nog een trigger is, wordt de tweede trigger geactiveerd en vervolgens een derde trigger aangeroepen, enzovoort. Als een trigger in de keten een oneindige lus instelt, wordt het nestniveau overschreden en wordt de trigger geannuleerd. Wanneer een Transact-SQL trigger beheerde code start door te verwijzen naar een CLR-routine, type of aggregatie, telt deze verwijzing als één niveau ten opzichte van de nestlimiet op 32 niveaus. Methoden die vanuit beheerde code worden aangeroepen, tellen niet mee voor deze limiet.

Als u geneste triggers wilt uitschakelen, stelt u de optie voor geneste triggers in sp_configure op 0 (uit). De standaardconfiguratie ondersteunt geneste triggers. Als geneste triggers zijn uitgeschakeld, worden recursieve triggers ook uitgeschakeld, ondanks de RECURSIVE_TRIGGERS instelling die is ingesteld met behulp van ALTER DATABASE.

De eerste AFTER trigger die in een INSTEAD OF trigger is genest, wordt geactiveerd, zelfs als de configuratieoptie van de geneste triggerserver 0 is. Maar onder deze instelling worden de latere AFTER triggers niet geactiveerd. Controleer uw toepassingen voor geneste triggers om te bepalen of de toepassingen uw bedrijfsregels volgen wanneer de configuratieoptie voor geneste triggersserver is ingesteld op 0. Als dat niet het geval is, moet u de juiste wijzigingen aanbrengen.

Uitgestelde naamomzetting

Met SQL Server kunnen Transact-SQL opgeslagen procedures, triggers, functies en batches verwijzen naar tabellen die niet bestaan tijdens het compileren. Deze mogelijkheid wordt de uitgestelde naamomzetting genoemd.

Machtigingen

Als u een DML-trigger wilt maken, is hiervoor toestemming vereist ALTER voor de tabel of weergave waarop de trigger wordt gemaakt.

Als u een DDL-trigger wilt maken met serverbereik (ON ALL SERVER) of een aanmeldingstrigger, moet u toestemming op de server hebben CONTROL SERVER . Voor het maken van een DDL-trigger met databasebereik (ON DATABASE) is een machtiging in de huidige database vereist ALTER ANY DATABASE DDL TRIGGER .

Voorbeelden

Eén. Een DML-trigger gebruiken met een herinneringsbericht

Met de volgende DML-trigger wordt een bericht naar de client afgedrukt wanneer iemand probeert gegevens toe te voegen aan of te wijzigen in de Customer tabel in de AdventureWorks2022-database.

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

B. Een DML-trigger gebruiken met een herinneringsbericht

In het volgende voorbeeld wordt een e-mailbericht verzonden naar een opgegeven persoon (MaryM) wanneer de Customer tabel wordt gewijzigd.

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. Een DML AFTER-trigger gebruiken om een bedrijfsregel af te dwingen tussen de tabellen PurchaseOrderHeader en Vendor

Omdat CHECK beperkingen alleen verwijzen naar de kolommen waarop de beperking op kolomniveau of tabelniveau is gedefinieerd, moet u eventuele beperkingen voor meerdere tabellen (in dit geval bedrijfsregels) definiëren als triggers.

In het volgende voorbeeld wordt een DML-trigger gemaakt in de AdventureWorks2022 database. Met deze trigger wordt gecontroleerd of de kredietclassificatie voor de leverancier goed is (niet 5) wanneer er wordt geprobeerd een nieuwe inkooporder in de PurchaseOrderHeader tabel in te voegen. Als u de kredietclassificatie van de leverancier wilt ophalen, moet naar de Vendor tabel worden verwezen. Als de rating te laag is, wordt er een bericht weergegeven en wordt de invoeging niet uitgevoerd.

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. Een DDL-trigger met databasebereik gebruiken

In het volgende voorbeeld wordt een DDL-trigger gebruikt om te voorkomen dat synoniemen in een database worden verwijderd.

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. Een DDL-trigger met serverbereik gebruiken

In het volgende voorbeeld wordt een DDL-trigger gebruikt om een bericht af te drukken als er CREATE DATABASE een gebeurtenis optreedt op het huidige serverexemplaren en wordt de EVENTDATA functie gebruikt om de tekst van de bijbehorende Transact-SQL instructie op te halen. Zie De functie EVENTDATA gebruiken voor meer voorbeelden die worden gebruikt EVENTDATA in DDL-triggers.

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. Een aanmeldingstrigger gebruiken

In het volgende aanmeldingstriggervoorbeeld wordt een poging om u aan te melden bij SQL Server geweigerd als lid van de login_test aanmelding als er al drie gebruikerssessies worden uitgevoerd onder die aanmelding. Wijzig <password> in een sterk wachtwoord.

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. De gebeurtenissen weergeven waardoor een trigger wordt geactiveerd

In het volgende voorbeeld worden de sys.triggers en sys.trigger_events catalogusweergaven opgevraagd om te bepalen welke Transact-SQL taal gebeurtenissen ertoe leiden dat trigger safety wordt geactiveerd. De trigger, safetywordt gemaakt in voorbeeld D. Gebruik een DDL-trigger met databasebereik.

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

Verwante inhoud

• ALTER TABLE (Transact-SQL)
• ALTER TRIGGER (Transact-SQL)
• COLUMNS_UPDATED (Transact-SQL)
• MAAK TABEL AAN (Transact-SQL)
• DROP TRIGGER (Transact-SQL)
• TRIGGER INSCHAKELEN (Transact-SQL)
• TRIGGER UITSCHAKELEN (Transact-SQL)
• TRIGGER_NESTLEVEL (Transact-SQL)
• EVENTDATA (Transact-SQL)
• sys.dm_sql_referenced_entities
• sys.dm_sql_referencing_entities
• sys.sql_expression_afhankelijkheden
• sp_help
• sp_helptrigger
• sp_helptext
• sp_rename
• sp_settriggerorder
• UPDATE - Triggerfuncties (Transact-SQL)
• Informatie over DML-triggers ophalen
• Informatie over DDL-triggers ophalen
• 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