SQL tabellen beveiligen voor ongelukken ?>

SQL tabellen beveiligen voor ongelukken

Wie kent het niet? Zo’n moment waarop je beseft net geen update gedaan te hebben op de hele database. Hoe belangrijk is het om in een query op de SQL database altijd een filter toegepast te hebben. Een update doen over teveel records kan immers tot onherstelbare schade leiden. Een goede back-up is eveneens onmisbaar, maar het kost ook veel tijd om vanuit een back-up data te restoren. En wat ik net geschreven heb over een update, geldt zeker zo voor een delete uitgevoerd op een tabel.

Het is daarom goed om tabellen op de nodige plaatsen te voorzien van beveiliging op het doen van grote mutaties. Door middel van een eenvoudig script kan je al bepalen dat je niet alle regels uit een tabel tegelijk kan verwijderen of updaten. Of dat er altijd een filter toegepast moet zijn om data te mogen verwijderen. Dit aantal records is ook te bepalen op een maximaal aantal. In een aantal gevallen zal dit misschien wat ongemak veroorzaken, maar een update of delete kan, wanneer je dit handmatig moet doen, ook best in een aantal batches uitgevoerd worden.

Wanneer een van de ingestelde grenswaarden wordt overschreden zal SQL een ingestelde foutmelding teruggeven. Deze melding kan je zelf instellen. Dat maakt het gebruik nog wat makkelijker, je krijgt immers een melding die je zelf duidelijk kan maken.

Waar je bij het instellen rekening mee moet houden, is dat deze beperking in alle gevallen zal werken. Dus ook wanneer je in je programmatuur hebt ingesteld dat een tabel leeg gemaakt moet worden, wat in sommige gevallen natuurlijk best mogelijk is, zal de SQL server deze bewerking blokkeren. Zorg er dus altijd voor dat de restricties die op een tabel worden ingesteld, niet tegenstrijdig zijn met de regels vanuit de applicatie.

Hieronder vindt je een stukje voorbeeldcode om het updaten van teveel regels te blokkeren.

CREATE TRIGGER [dbo].[uPreventWholeDelete] 
ON [dbo].[DimTijdsblokken2]
FOR DELETE AS 
BEGIN
 
 DECLARE @Count int
 SET @Count = @@ROWCOUNT;
 
 IF @Count > 100
 BEGIN
 RAISERROR('Er kunnen maximaal 100 regels tegelijk verwijderd worden!',16,1) 
 ROLLBACK TRANSACTION
 RETURN;
 END
END

De eerste regel met CREATE bevat de naam van de trigger, waarbij je op regel 2 aangeeft over welke tabel de code betrekking heeft. Daarna wordt er gekeken hoeveel regels er binnen de query uitgevoerd zouden worden, wat gedaan wordt met de functie @@ROWCOUNT. In de If-clausule bepaalt de trigger of het aantal records binnen de limiet valt, in dit geval 100 records. Wanneer @@ROWCOUNT meer dan 100 regels bevat, wordt er een melding teruggegeven en wordt de transactie teruggedraaid.

Naast een beperking voor delete kan je eenzelfde beperking instellen voor het updaten van een bepaald aantal regels tegelijk.

CREATE TRIGGER [dbo].[uPreventWholeUpdate]
ON [dbo].[DimTijdsblokken2]
FOR UPDATE AS
BEGIN

DECLARE @Count int
SET @Count = @@ROWCOUNT;

IF @Count > 100
BEGIN
RAISERROR('Er kunnen maximaal 100 regels tegelijk geüpdate worden!',16,1)
ROLLBACK TRANSACTION
RETURN;
END
END

Wanneer je de trigger verder nog wilt bewerken, wijzig je CREATE door ALTER, waarna met de nieuwe beperkingen wordt gewerkt.

Deze twee triggers kun je ook altijd nog eenvoudig beheren. Wanneer je in SQL Management Studio op het +-teken voor een tabel klikt, krijg je een aantal opties, waaronder [Triggers]. Onder dit mapje vind je de zojuist aangemaakte triggers die een delete of update op de specifieke tabel beperken.

 

Klik hier om naar onze site te gaan

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *