Eerst een beetje achtergrondinformatie over triggers. Hoewel ze vaak worden verguisd, kunnen ze in bepaalde omstandigheden zeer nuttig zijn… als je ze eenmaal leert kennen. Bijvoorbeeld, het verzenden van een e-mail vanuit SQL Server kan worden gedaan met een trigger op basis van een verwachte gebeurtenis.
In dit artikel, zullen we ons richten op een geval met T-SQL data wijziging wanneer een Insert, Update of Delete statement de gegevens in onze tabel raakt. Zoals de naam al zegt, zal de trigger afgaan en dan kunnen we beslissingen nemen over wat er verder moet gebeuren op basis van nieuwe gegevens die binnenkomen of oude gegevens die worden vervangen. Dit is waar triggers het meest voor worden gebruikt.
Het nadeel van triggers is dat mensen in de problemen kunnen komen met recursieve triggers en geneste looping triggers, wat kan leiden tot een aantal prestatie-implicaties, maar we zullen ons in dit artikel beperken tot eenvoudige triggers, waarbij we in gedachten houden dat het belangrijk is om te weten wanneer, waar en hoe je ze moet gebruiken.
Op het gebied van prestaties is het het beste om triggers klein en eenvoudig te houden. Net als bij programmeren, ga bij het maken van een trigger uit van de logica dat minder meer is.
In dit artikel gaan we drie voorbeelden bekijken van het gebruik van een trigger met verschillende combinaties van operaties, bijv. insert en triggertypes, bijv. INSTEAD OF, waaronder:
- Het versturen van een e-mail, gebaseerd op een Insert operatie voor een bepaalde tabel. De tabel wordt niet vaak bijgewerkt en we hebben een stakeholder die wil weten wanneer er nieuwe gegevens aan worden toegevoegd
- Het bijhouden van alle wijzigingen in de tabel door wijzigingen naar een auditing tabel te schrijven
- Voorkomen van verwijdering van gegevens in de tabel met een execution guard
DML triggers overzicht
We moeten vermelden dat er ook DLL en CLR triggers zijn die ook een speciaal type T-SQL objecten zijn, maar de focus ligt hier op DML (data manipulatie taal) zoals een Insert, Update en Delete statement.
Trigger types
- AFTER – triggers vuren af als DML statements zijn voltooid
AFTER stelt dat de DML trigger pas wordt afgevuurd als alle in het SQL statement gespecificeerde operaties succesvol zijn uitgevoerd. Alle referentiële cascade-acties en constraint-controles moeten ook slagen voordat deze trigger afgaat. AFTER triggers kunnen niet op views gedefinieerd worden. - INSTEAD OF – triggers vuren af vóór DML statements
INSTEAD OF stelt dat de DML trigger wordt uitgevoerd voordat het DML statement helemaal doorloopt, waarbij de acties van de triggerende statements worden overruled. INSTEAD OF triggers kunnen niet worden gespecificeerd voor DDL of logon.
Goeg gepraat, laten we eens kijken hoe we eenvoudige DML triggers kunnen maken voor Insert en Update statements en dan nog een trigger voor een Delete statement op een belangrijke tabel die zal fungeren als fatal action guard en zal voorkomen dat de gebruiker gegevens verwijdert.
Change tracking/auditing trigger (update)
We gaan beginnen met de Update trigger. Voorwaarde hierbij is dat we een database hebben met informatie over producten, opgeslagen in tabellen. Een van de tabellen heeft een kolom met informatie over de prijs van producten. Laten we zeggen dat we de producten tabel willen monitoren en telkens wanneer een Update de tabel bereikt willen we dat de trigger afgaat omdat we informatie over de prijs historie willen bijhouden. Verder hebben we een extra tabel nodig waarin we de gemonitorde informatie van wijzigingen in een bestaande tabel opslaan.
Je kunt een eenvoudige tabel maken die aan je behoeften voldoet, maar hieronder staat een voorbeeld van de tabel die in dit geval wordt gebruikt:
USE AdventureWorks2014;GOIF OBJECT_ID('Purchasing.StandardPriceHistory', 'U') IS NOT NULL DROP TABLE Purchasing.StandardPriceHistory;GOCREATE TABLE Purchasing.StandardPriceHistory(PriceHistoryID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, ProductID INT NOT NULL, PreviousPrice DECIMAL(19, 4), NewPrice DECIMAL(19, 4), PriceChangeDate DATETIME NOT NULL)ON ;GO
Nu de tabel is gemaakt, kunnen we verder gaan met het maken van de trigger. Hieronder volgt een voorbeeld van de hierboven beschreven trigger:
USE AdventureWorks2014;GOIF OBJECT_ID('Purchasing.uStandardPriceHistory', 'TR') IS NOT NULL DROP TRIGGER Purchasing.uStandardPriceHistory;GOCREATE TRIGGER uStandardPriceHistory ON Purchasing.ProductVendorFOR UPDATEAS INSERT INTO Purchasing.StandardPriceHistory(PriceHistoryID, ProductID, PreviousPrice, NewPrice, PriceChangeDate) SELECT NEWID(), pv.ProductID, d.StandardPrice, i.StandardPrice, GETDATE() FROM Purchasing.ProductVendor pv JOIN INSERTED i ON pv.ProductID = i.ProductID JOIN DELETED d ON pv.ProductID = d.ProductID;GO
Deze trigger wordt aangemaakt op de tabel Purchasing.ProductsVendor voor het update-instructie en zal in principe een momentopname maken van de kolom Prijs van de oude prijs en de nieuwe prijs en deze plaatsen in de eerder gemaakte tabel ProductPriceHistory die hiervoor nodig is.
Na het uitvoeren van de code van hierboven, als we navigeren naar de ProductsVendor tabel en vouw de Triggers map moet er onze nieuw gecreëerde trigger. Wanneer deze afgaat, registreert deze dat oude gegevens worden vervangen door nieuwe gegevens en voegt deze informatie in de tabel ProductPriceHistory in:
Nu hoeven we alleen nog maar een Update statement tegen de tabel ProductsVendor uit te voeren om te zien hoe het werkt. In plaats van code te schrijven en omdat het sneller gaat, gebruiken we de optie Top 200 rijen bewerken door met de rechtermuisknop op de tabel in Object Explorer te klikken, waardoor een nieuw tabblad in Editor wordt geopend:
Hier hebben we een product met ID 1 dat een waarde heeft van 47,87. Laten we zeggen dat het artikel in de aanbieding is en dit wijzigen door de nieuwe waarde 39,99 in te voeren en op de Enter-toets te drukken:
Deze actie is hetzelfde als het schrijven van een update-instructie. Het geeft het statement op de achtergrond uit en als er niets mis is met de ingevoerde gegevens, worden de records bijgewerkt. Zo niet, dan verschijnt er een info-bericht over wat er fout is gegaan.
Het statement is doorgevoerd en een snelle Select uit de tabel die wordt gebruikt voor het opslaan van de gemonitorde gegevens laat veranderingen zien:
Nieuwe record melding (insert trigger)
Volgende is de Insert trigger. Laten we het Insert statement gebruiken in combinatie met een trigger als een notificatiemechanisme. We gaan eigenlijk een e-mail sturen vanuit een Insert trigger. Het idee hierachter is om bijvoorbeeld klanten te laten weten wanneer er een nieuw product beschikbaar is. Voorwaarde hierbij is dat de Database Mail correct is ingesteld en geconfigureerd.
Note: Als u nieuw bent met Database Mail, dit is een enterprise oplossing voor het versturen van email berichten vanuit de SQL Server Database Engine en het is sterk aan te raden om het volgende gedetailleerde artikel te bekijken over hoe dit in te stellen en bekende problemen op te lossen die kunnen optreden: How to configure database mail in SQL Server
Wanneer aan deze voorwaarde is voldaan, staat hieronder een voorbeeld voor de Insert-trigger die kan worden gebruikt:
USE AdventureWorks2014;GOIF OBJECT_ID('Production.iProductNotification', 'TR') IS NOT NULL DROP TRIGGER Purchasing.iProductNotification;GOCREATE TRIGGER iProductNotification ON Production.ProductFOR INSERTAS DECLARE @ProductInformation NVARCHAR(255); SELECT @ProductInformation = 'A new product, '+Name+', is now available for $'+CAST(StandardCost AS NVARCHAR(20))+'!' FROM INSERTED i; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Notifications', @recipients = 'Use a valid e-mail address', @body = @ProductInformation, @subject = 'New product notification';GO
Dit keer koppelen we de trigger aan de Production.Product-tabel. Merk op dat de trigger deze keer een FOR INSERT statement is. U kunt ook AFTER INSERT gebruiken, aangezien for en after in principe hetzelfde zijn. Dit is een van die achterwaartse compatibiliteit dingen.
Dus, zodra een nieuw product wordt ingevoegd, zal de trigger afgaan die dan wat basis informatie zal verzamelen, die informatie in een vorm van een email bericht zal verpakken en de opgeslagen procedure voor het verzenden van de email zal uitvoeren die uiteindelijk het bericht in de Database Mail wachtrij zal plaatsen. Dit is een uitstekend voorbeeld van hoe een trigger kan worden gebruikt in een kettingreactie.
Een snelle samenvatting van deze twee is dat je in gedachten moet houden dat zowel Update als Insert triggers behoren tot de groep van AFTER triggers. In beide voorbeelden hierboven vuren ze af als de DML statements zijn voltooid.
Action guard (delete trigger)
Over naar de Delete trigger, dit is het moment waarop we een voorbeeld kunnen laten zien van INSTEAD OF triggers die afvuren voorafgaand aan DML statements. Laten we eens kijken wat we met dit type trigger kunnen doen.
Want deze trigger kan voorkomen dat een DML statement wordt uitgevoerd, waarom zouden we hem dan niet gebruiken als een “action guard” tegen onbedoeld verlies van data voor belangrijke informatie. Een voorbeeld wordt beschreven en getoond in een ander artikel dat ook alternatieve manieren onderzoekt om onbedoeld gegevensverlies te voorkomen: How to prevent accidental data loss from executing a query in SQL Server aka “Practising safe coding”
Alrighty dan, we raden ten zeerste aan om de code uit het bovenstaande artikel te controleren, maar laten we hier ook een ander voorbeeld zien. Microsoft heeft eigenlijk een heel goede trigger in de AdventureWorks2014 database. Navigeer via Object Explorer en lokaliseer de dEployee trigger onder de tabel HumanResources.Employee tabel of gebruik een mooie ‘Ga naar object’ functie in ApexSQL Complete om snel het object te vinden en markeer het in de Object Explorer:
Klik met de rechtermuisknop op de dEmployee trigger en navigeer naar Script Trigger als > CREATE To en klik vervolgens op de opdracht New Query Editor Window:
Door deze actie wordt een nieuw tabblad in Editor geopend met de volgende code:
USE ;GO/****** Object: Trigger . Script Date: 12/18/2017 12:27:41 PM ******/SET ANSI_NULLS ON;GOSET QUOTED_IDENTIFIER ON;GOCREATE TRIGGER . ON .INSTEAD OF DELETENOT FOR REPLICATIONAS BEGIN DECLARE @Count INT; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN RAISERROR(N'Employees cannot be deleted. They can only be marked as not current.', -- Message 10, -- Severity. 1); -- State. -- Rollback any active or uncommittable transactions IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END; END; END;GOALTER TABLE . ENABLE TRIGGER ;GOEXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'INSTEAD OF DELETE trigger which keeps Employees from being deleted.', @level0type = N'SCHEMA', @level0name = N'HumanResources', @level1type = N'TABLE', @level1name = N'Employee', @level2type = N'TRIGGER', @level2name = N'dEmployee';GO
Nu zijn er hier een paar dingen aan de hand. Allereerst is niet alle code nodig, maar het is een perfect voorbeeld van een trigger die alles heeft. We hebben een INSTEAD OF trigger voor een Delete statement, wat betekent dat wanneer een Delete de gegevens in onze tabel raakt, de trigger eerst de code uitvoert die de gegevens controleert voordat het daadwerkelijk wordt uitgevoerd en ons een kans geeft om dingen terug te draaien.
De @@ROWCOUNT functie is nuttig in dit geval omdat het het aantal rijen controleert dat wordt beïnvloed door het Delete statement. Het zegt in feite als het aantal rijen nul is, de trigger helemaal niet af te vuren. Dit is geweldig vanuit het oogpunt van performance.
Als we op het punt zijn gekomen waar het Delete statement daadwerkelijk doorkwam, en er zijn daadwerkelijk rijen beïnvloed, dan zal de RAISERROR functie een door de gebruiker gedefinieerde boodschap laten zien. Om dit in actie te zien, gebruikt u de opdracht Edit Top 200 Rows opnieuw op de tabel HumanResources.Employee:
Selecteer een rij zoals hieronder afgebeeld en druk op de Delete-toets van het toetsenbord. Er verschijnt een waarschuwingsvenster ter bevestiging. Klik op de knop Ja om door te gaan met de actie:
Er verschijnt nog een informatiedialoogvenster met de foutmelding dat de transactie in de trigger is geëindigd. Bovendien wordt de string weergegeven die door de gebruiker zelf is opgegeven:
Dit is waar de ROLLBACK TRANSACTION-functie in werking treedt om alle gegevenswijzigingen te wissen die vanaf het begin van de transactie zijn gemaakt.
Hetzelfde zal ook vanuit Editor gebeuren. Onthoud dat het bovenstaande voorbeeld en de code hieronder precies hetzelfde doen:
Potentiële problemen
“Met grote macht komt grote verantwoordelijkheid” wordt vaak geciteerd uit de woorden van Uncle Ben uit de Spider-Man film. Nu we de werkelijke kracht van DML-triggers hebben gezien, gaan we terug naar het begin van het artikel, waar we het hadden over recursieve trigger en geneste looping-triggers. We zullen de voorbeelden overslaan omdat het doel van dit artikel is om een beter begrip te krijgen van hoe de triggers in het algemeen nuttig kunnen zijn en te zien wanneer, waar en hoe je ze kunt gebruiken, niet om de keerzijde te laten zien. Maar het is ook belangrijk om op zijn minst kort de mogelijke nadelen te beschrijven.
Nested triggers
Nested triggers zijn gevallen waarin een trigger van de ene tabel een actie uitvoert die een andere trigger in een andere tabel initieert, enz. Bovendien, als de trigger van de tweede tabel terugroept naar onze tabel en gegevens bijwerkt, dan hebben we een oneindige looping proces. Gelukkig voor ons heeft SQL Server een beveiliging en kunnen triggers genest worden tot een maximum van 32 niveaus (INSTEAD OF triggers kunnen genest worden ongeacht de instelling van deze optie). Deze instelling is standaard ingeschakeld in SQL Server en wat het betekent is dat als deze nesting limiet wordt overschreden de trigger wordt beëindigd.
Om de nested triggers optie te verifiëren, klikt u in Object Explorer met de rechter muisknop op een database, en selecteert u vervolgens Properties. Op de pagina Options staat de optie Nested Triggers Enabled en deze moet op True staan:
Merk op dat deze optie grijs is weergegeven, wat betekent dat deze niet in de GUI kan worden geconfigureerd. Als om wat voor reden dan ook deze optie is uitgeschakeld, wat niet aan te raden is, behalve in enkele zeldzame gevallen, voer dan de volgende code uit om hem in te schakelen:
USE AdventureWorks2014GOEXEC sp_configure 'nested triggers', 1; GO RECONFIGURE; GO
De instelling wordt onmiddellijk van kracht zonder dat de server opnieuw hoeft te worden opgestart. Als u een foutmelding krijgt, komt dat waarschijnlijk omdat dit een geavanceerde optie is. Voeg de onderstaande code toe vóór de opgeslagen systeemprocedure om dit te verhelpen:
USE AdventureWorks2014 ; GO EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE ; GO
Recursieve triggers
Recursieve triggers zijn gevallen waarin een trigger zichzelf herhaaldelijk aanroept. Dit gebeurt meestal als er geen logica in de trigger zit die de recursie kan doorbreken. Dit is ook bekend als een oneindige lus. Gelukkig zijn de recursieve triggers in SQL Server standaard uitgeschakeld. Zoals voorheen kunt u deze optie aanvinken in Object Explorer, klik met de rechter muisknop op een database en selecteer vervolgens Properties. Op de pagina Options staat de optie Recursive Triggers Enabled en deze staat op False:
Wanneer deze optie op False staat, wordt alleen directe recursie voorkomen. Deze optie kan rechtstreeks vanuit de GUI worden gewijzigd:
Opgemerkt moet worden dat de triggers aan tabellen zijn gekoppeld. Op grote databases met veel tabellen, kan het schrijven van triggers voor elke tabel erg tijdrovend zijn. Nu we elk DML statement hebben behandeld, laten we eens kijken naar een out-of-the-box oplossing voor het automatisch aanmaken van triggers om de bovenstaande doelen te bereiken.
Automatische, template-gebaseerde trigger creatie
ApexSQL Trigger is een auditing tool voor SQL Server databases die DML wijzigingen vastlegt die zijn opgetreden in een database. Dus, laten we eens kijken hoe we trigger based SQL auditing kunnen implementeren, gebruikmakend van een complexere versie van onze Update trigger voor change tracking/auditing, met deze tool in een paar stappen.
Start ApexSQL Trigger op, klik op de New knop om een nieuw project aan te maken. Dit zal een Nieuw project dialoog openen waar we de server, authenticatie methode en database selecteren waarop we wijzigingen willen auditeren. Klik na het instellen op de knop OK om door te gaan naar de volgende stap:
De applicatie zal vervolgens een dialoogvenster tonen over ontbrekende architectuur. Dit is normaal omdat dit de eerste keer is dat de applicatie is verbonden met deze database. Dit is een essentiële stap omdat de applicatie een specifieke architectuur van SQL objecten geïnstalleerd moet hebben voordat de auditing kan starten (vergelijkbaar met het aanmaken van onze tabel). Selecteer de standaardoptie en klik op de knop Ja om de architectuur te installeren:
De volgende stap is het verlaten van de standaardinstellingen en klik op de knop Installeren en bevestig vervolgens het uitvoeren van het script door op de knop Ja te klikken om de auditing-architectuur op de database te installeren:
Het uitvoeren van het script neemt slechts enkele seconden in beslag en zodra het succesvol is afgerond, klikt u op de knop OK om het venster te sluiten en terug te keren naar het venster Architectuur beheren:
Sluit dit venster omdat we het niet langer nodig hebben en u ziet het beginvenster van de toepassing:
Zoek de tabel Purchasing.ProductsVendor-tabel, vink de optie aan de linkerkant aan en u ziet alle kolommen onder het deelvenster Columns. Vink de kolom StandardPrice aan en klik vervolgens op de knop Create om de trigger te maken:
Klik op de knop Execute in het Script-venster om de trigger te maken:
Na afloop moet u het bericht zien dat alle bewerkingen zijn voltooid en dat het script met succes is uitgevoerd:
terug naar het beginscherm, merk op dat de tabel nu een groen vinkje heeft, wat betekent dat deze tabel wordt geaudit:
Als we teruggaan naar Object Explorer en de map Triggers verversen, zien we drie nieuwe triggers die door ApexSQL Trigger zijn gemaakt:
Elke trigger is voor één DML-instructie. Waarom? Omdat waarom niet, het is niet alsof we de code moesten typen. En dit zorgt voor meer granulariteit, voor het geval we operatie specifieke modificaties in een van de drie sjablonen willen maken. Het is echter aan u welke wijzigingen u wilt controleren en u kunt dit beslissen met een enkele klik.
We kunnen nu de uStandardPriceHistory trigger verwijderen, omdat we die niet langer nodig hebben. Vergeet ook niet dat het hebben van meerdere triggers op een tabel kan leiden tot prestatie implicaties (vooral in dit geval wanneer twee triggers precies hetzelfde doen). We moeten ook de eerder aangemaakte tabel Purchasing.StandardPriceHistory verwijderen, omdat deze nutteloos is zonder de trigger.
Laten we zeggen dat het item niet langer in de uitverkoop is, verander de prijs naar de oude waarde met behulp van de stappen van hiervoor, zodat we kunnen zien hoe we veranderingen kunnen bekijken. In tegenstelling tot het schrijven van het Select statement en het handmatig uit een tabel halen van de gegevens, doet ApexSQL Trigger dit door het genereren van door de gebruiker aanpasbare rapporten.
Klik op de Standaard knop onder de Rapporten groep:
In het volgende venster, klik op de Toepassen knop en de verandering zal hieronder worden getoond:
Nu kan het verzenden van een e-mail op een paar manieren worden gedaan. Een manier is al beschreven in een artikel over hoe de ApexSQL Trigger architectuur aan te passen, zodat e-mail alerts worden verzonden wanneer er wijzigingen worden aangebracht in een database record: How to send email alerts when a change is made to a database record
Als je de methode uit bovenstaand artikel toepast, zal Database Mail emails versturen voor alle DML wijzigingen op een tabel. In ons geval hebben we het alleen nodig voor het Insert statement. De gebruiker kan de sjablonen vrijelijk bewerken en wijzigen, wat betekent dat het invoegen van je eigen code voor de trigger mogelijk is. In dit geval kunnen we gewoon een nieuwe Insert trigger maken en het bestaande sjabloon bewerken door ons stukje code toe te voegen, zodat de trigger niet alleen de wijzigingen vastlegt, maar ook de sp_send_dbmail stored procedure uitvoert en een e-mail verstuurt.
Hetzelfde geldt voor een Delete statement. Door slechts een paar regels code toe te voegen, kun je een preventiemechanisme creëren dat je gegevens bewaakt door je niet per ongeluk alle gegevens in een tabel te laten verwijderen. In dit geval hebben we alleen de volgende code toegevoegd om het doel te bereiken:
DECLARE @Count int SET @Count = @@ROWCOUNT; IF @Count >= (SELECT SUM(row_count) FROM sys.dm_db_partition_stats WHERE OBJECT_ID = OBJECT_ID('Sales.Customer') AND index_id = 1) BEGIN RAISERROR('Cannot delete all rows',16,1) ROLLBACK TRANSACTION RETURN;END
Het bewerkte sjabloon ziet er als volgt uit:
Het uitvoeren van de bewerkte code, creëert een trigger die voorkomt dat de hele tabel wordt gewist:
Het opslaan van het sjabloon kan handig zijn voor toekomstig gebruik en het maken van dit op meerdere tabellen tegelijk.
Ik hoop dat u dit artikel nuttig vindt. Voor meer informatie over ApexSQL Trigger, bezoek de product pagina
Nuttige links
- Hoe voorkom je onbedoeld gegevensverlies door het uitvoeren van een query in SQL Server aka “Practising safe coding”
- Hoe stel je verschillende verbindingskleuren in SSMS in
- Fatal action guard: bescherming tegen het onbedoeld uitvoeren van code die gegevens in SQL Server kan beschadigen of vernietigen