Exempel på triggers i ms sql-server. Trigger (databas). DDL-utlösare och deras applikationer

trigger:

<Определение_триггера>::= (SKAPA | ÄNDRA) TRIGGER trigger_name PÅ (tabellnamn | visningsnamn) ( ( ( FÖR | EFTER | I STÄLLET FÖR ) ( [ DELETE] [,] [ INFOGA] [,] [ UPPDATERA] ) [ MED BILAGA ] [ INTE FÖR REPLICATION ] AS sql_statement[...n] ) | ( (FÖR | EFTER | I STÄLLET FÖR ) ( [,] ) [ MED BILAGA] [ INTE FÖR REPLICERING] SOM ( OM UPPDATERING(kolumnnamn) [ (OCH | ELLER) UPPDATERING( column_name)] [...n] | IF (COLUMNS_UPDATES() (process_bit_operator) change_bit_mask) (comparison_bit_operator) bit_mask [...n]) sql_operator [...n] ) )

En trigger kan bara skapas i den aktuella databasen, men det är möjligt att komma åt andra databaser inom triggern, inklusive de som finns på en fjärrserver.

Låt oss titta på syftet med argumenten från CREATE | ALTER TRIGGER.

Triggernamnet måste vara unikt i databasen. Dessutom kan du ange ägarens namn.

När du anger argumentet WITH ENCRYPTION krypterar servern triggerkoden så att ingen, inklusive en administratör, kan komma åt eller läsa den. Kryptering används ofta för att dölja proprietära databehandlingsalgoritmer som är programmerarens immateriella rättigheter eller en affärshemlighet.

Triggertyper

I SQL Server Det finns två parametrar som bestämmer beteendet hos utlösare:

EFTER. Utlösaren exekveras efter att de kommandon som anropade den har slutförts. Om kommandona av någon anledning inte kan slutföras framgångsrikt, exekveras inte triggern. Det bör noteras att dataändringar som ett resultat av exekvering av en användarbegäran och triggerexekvering utförs i kroppen av en transaktion: om triggern rullas tillbaka, kommer användarändringar också att avvisas. Du kan definiera flera AFTER-utlösare för varje operation (INSERT, UPDATE, DELETE). Om du har flera AFTER-utlösare på en tabell kan du använda sp_settriggerorder-systemets lagrade procedur för att ange vilken utlösare som ska köras först och vilken som körs sist. Som standard, i SQL Server, är alla utlösare AFTER-utlösare.
ISTÄLLET FÖR . Utlösaren anropas istället för att utföra kommandon. Till skillnad från AFTER-utlösaren kan INSTEAD OF-utlösaren definieras för både en tabell och en vy. För varje INSERT-, UPDATE-, DELETE-operation kan endast en INSTEAD OF-utlösare definieras.

Triggers kännetecknas av den typ av kommandon som de svarar på.

Det finns tre typer av triggers:

INSERT TRIGGER – Utlöses när ett försök görs att infoga data med kommandot INSERT.
UPDATE TRIGGER – utlöses när ett försök görs att ändra data med kommandot UPDATE.
DELETE TRIGGER – utlöses när ett försök görs att radera data med kommandot DELETE.

Konstruktioner [ DELETE] [,] [ INFOGA] [,] [ UPPDATERA] Och FÖR | EFTER | ISTÄLLET FÖR ) ([,] avgöra vilket kommando utlösaren ska svara på. När du skapar den måste minst ett kommando anges. Tillåten skapa en trigger, svarar på två eller alla tre kommandon.

WITH APPEND låter dig skapa flera triggers av varje typ.

På skapa en trigger med argumentet INTE FÖR REPLIKATION är det förbjudet att köras medan tabeller modifieras av replikeringsmekanismer.

AS sql_operator[...n]-konstruktionen definierar en uppsättning SQL-satser och kommandon som kommer att exekveras när triggern startas.

Observera att ett antal operationer inte är tillåtna i en utlösare, till exempel:

skapa, modifiera och ta bort en databas;
återhämtning säkerhetskopia databas eller transaktionslogg.

Dessa kommandon tillåts inte att köras eftersom de inte kan återställas om transaktionen där triggern exekveras återställs. Det är osannolikt att detta förbud på något sätt påverkar funktionen hos skapade triggers. Det är svårt att hitta en situation där du till exempel efter att ha ändrat en tabellrad skulle behöva återställa en säkerhetskopia av transaktionsloggen.

Trigger programmering

När du kör kommandon för att lägga till, ändra och ta bort poster skapar servern två specialtabeller: insatt Och raderade. De innehåller listor över rader som kommer att infogas eller raderas när transaktionen är klar. Strukturen för de infogade och borttagna tabellerna är identisk med strukturen för de tabeller för vilka utlösaren är definierad. Varje utlösare skapar sin egen uppsättning infogade och borttagna tabeller, så ingen annan utlösare kan komma åt dem. Beroende på vilken typ av operation som fick utlösaren att köra, kan innehållet i de infogade och borttagna tabellerna vara olika:

INSERT kommando – den infogade tabellen innehåller alla rader som användaren försöker infoga i tabellen; det kommer inte att finnas en enda rad i den raderade tabellen; efter att triggern är klar kommer alla rader från den infogade tabellen att flyttas till källtabellen;
DELETE kommando – den raderade tabellen kommer att innehålla alla rader som användaren försöker ta bort; utlösaren kan kontrollera varje rad och avgöra om den är tillåten att raderas; det kommer inte att finnas några rader i den infogade tabellen;
UPDATE-kommandot - när det körs innehåller den raderade tabellen gamla radvärden som kommer att raderas efter framgångsrikt slutförande

Senast uppdaterad: 2017-11-09

Triggers är en speciell typ av lagrad procedur som anropas automatiskt när en viss åtgärd utförs på en tabell eller vy, i synnerhet när man lägger till, ändrar eller tar bort data, det vill säga när man utför INSERT, UPDATE, DELETE-kommandon.

Formell definition av en utlösare:

SKAPA TRIGGER trigger_name PÅ (tabellnamn | visningsnamn) (EFTER | I STÄLLET FÖR) SOM sql_expressions

För att skapa en trigger, använd CREATE TRIGGER-satsen följt av triggernamnet. Vanligtvis återspeglar triggernamnet typen av operation och namnet på tabellen där operationen utförs.

Varje utlösare är associerad med en specifik tabell eller vy, vars namn anges efter ordet PÅ .

Sedan är triggertypen inställd. Vi kan använda en av två typer:

EFTER: Utförs efter att åtgärden har slutförts. Definieras endast för tabeller.

I STÄLLET FÖR: utförs istället för en åtgärd (det vill säga att åtgärden - lägga till, ändra eller ta bort - utförs inte alls). Definierat för tabeller och vyer

Efter triggertypen kommer en indikation på operationen för vilken triggern är definierad: INSERT, UPDATE eller DELETE.

För en EFTER-utlösare kan du använda den för flera åtgärder samtidigt, till exempel UPPDATERA och INFOGA. I det här fallet indikeras operationer separerade med kommatecken. Du kan bara definiera en åtgärd för en ISTADEN FÖR utlösare.

Och sedan efter ordet AS kommer en uppsättning SQL-uttryck, som faktiskt utgör kroppen av utlösaren.

Låt oss skapa en trigger. Låt oss säga att vi har en databas productsdb med följande definition:

SKAPA DATABAS productdb; GÅ ANVÄND productdb; SKAPA TABELL Produkter (Id INT IDENTITET PRIMÄRNYCKEL, Produktnamn NVARCHAR(30) NOT NULL, Tillverkare NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Pris PENGAR INTE NULL);

Låt oss definiera en utlösare som aktiveras när du lägger till och uppdaterar data:

ANVÄND productdb; GÅ SKAPA TRIGGER Products_INSERT_UPDATE PÅ produkter EFTER INFOGA, UPPDATERA SOM UPPDATERING Produkter SET Pris = Pris + Pris * 0,38 WHERE Id = (VÄLJ ID FRÅN infogat)

Låt oss säga att tabellen Produkter lagrar data om produkter. Men priset på en produkt innehåller ofta olika tillägg som mervärdesskatt, tillagd korruptionsskatt och så vidare. Personen som lägger till uppgifterna kanske inte känner till alla dessa finesser med skattebasen, och han bestämmer nettopriset. Med hjälp av en trigger kan vi justera priset på en produkt med ett visst belopp.

På det här sättet kommer utlösaren att aktiveras vid alla INSERT- eller UPDATE-operationer i produkttabellen. Själva triggern kommer att ändra priset på produkten, och för att få produkten som har lagts till eller ändrats hittar vi denna produkt efter Id. Men vilket värde ska id:t för en sådan produkt ha? Faktum är att när du lägger till eller ändrar data sparas det i den infogade mellantabellen. Den skapas automatiskt. Och från den kan vi få data om tillagda/ändrade produkter.

Och efter att ha lagt till en produkt i produkttabellen kommer produkten i själva verket att ha ett något högre pris än det som fastställdes när det lades till:

Ta bort en trigger

För att ta bort en trigger måste du använda kommandot DROP TRIGGER:

SLUTA TRIGGER Products_INSERT_UPDATE

Inaktiverar en utlösare

Det händer att vi vill pausa en utlösare, men vi vill inte ta bort den helt. I det här fallet kan den tillfälligt inaktiveras med kommandot DISABLE TRIGGER:

INAKTIVERA TRIGGER Products_INSERT_UPDATE PÅ produkter

Och när du behöver en utlösare kan du aktivera den med kommandot ENABLE TRIGGER:

AKTIVERA TRIGGER Products_INSERT_UPDATE PÅ produkter

Utlösareär en subrutin som liknar en databasprocedur, som automatiskt anropas av DBMS när man ändrar, tar bort eller lägger till en post i en tabell. Triggers kan inte nås från ett program, skickas parametrar till dem eller få resultat från dem. Oftast används utlösare för att upprätthålla referensintegritet och kaskadoperationer i databasen. Referensspecifikationer, som definierar överlappande raderings- och uppdateringsåtgärder och skapas när tabeller deklareras, implementeras också genom utlösare, men texten i dessa utlösare är inte redigerbar.

Syftet med triggers

Förhindra ändringar (förhindra till exempel att fakturor ändras efter att de har skickats ut).
. Logga ändringar (behåll till exempel kopior av gamla data).
. Granska ändringar (för till exempel en logg över användare och roller som är involverade i ändringar).
. Fånga ändringar (till exempel se till att alla ändringar är daterade enligt serverns klocka, inte klientens).
. Implementering av affärsregler.
. Datareplikering (lagra till exempel en post över alla ändringar som kommer att skickas till en annan databas vid en senare version).
. Ökad produktivitet (till exempel uppdatering av saldot efter varje transaktionsdetalj, för att påskynda frågor).

Deklarerar triggers

SKAPA TRIGGER {FÖRE|EFTER} {DELETE|INSERT|UPPDATERA [AV ]} PÅ REFERENS {GAMMAL {[RAD]|TABELL [SOM] } NY {RAD|BORD} [SOM] }] [FÖR VARJE {UTTALANDE|RAD [NÄR ]}]
[BÖRJA ATOMIC]

[SLUTET]

Nyckelord

. FÖRE|EFTER– trigger starttid – före | efter uppdateringen.
. DELETE|INSERT|UPPDATERA= triggerhändelse.
. FÖR VARJE RAD– för varje rad (linjeutlösare, sedan NÄR).
. FÖR VARJE UTTALANDE– för hela laget (giltigt som standard).
. REFERENS– låter dig tilldela upp till 4 alias till gamla och | eller nya rader och | eller tabeller som kan nås av utlösare.

Utlösa begränsningar

Utlösarkroppen kan inte innehålla följande påståenden:
. Definiera, ta bort och ändra databasobjekt (tabeller, domäner, etc.)
. Transaktionsbearbetning (COMMIT, ROLLBACK)
. Anslutningar och frånkopplingar till databasen (CONNECT, DISCONNECT)

Funktioner i applikationen
. Triggern exekveras efter att alla andra (deklarativa) integritetskontroller har tillämpats och är användbar när testkriteriet är ganska komplext. Om deklarativa kontroller avvisar uppdateringsåtgärden exekveras inte triggers. Utlösaren fungerar i samband med en transaktion, men FK-begränsningen gör det inte.
. Om en utlösare orsakar en ytterligare modifiering av dess bastabell, leder detta oftast inte till dess rekursiva exekvering, men detta bör förtydligas. SQL Server 2005 ger möjlighet att specificera rekursion upp till 255 nivåer med nyckelordet OPTION (MAXRECURSIV 3).
. Triggers exekveras vanligtvis inte vid bearbetning av stora binära kolumner (BLOBs).
. Man bör komma ihåg att när data uppdateras skapar DBMS automatiskt så kallad trigger virtuella tabeller, som har olika namn i olika DBMS. I InterBase och Oracle – Dessa är nya och gamla. I SQL Server - Infogat och raderat. Dessutom, när data ändras, skapas båda. Dessa tabeller har samma antal kolumner, med samma namn och domäner som den tabell som uppdateras. SQL Server 2005 DBMS ger möjlighet att specificera en tabell, inklusive en temporär tabell, i vilken data ska infogas med hjälp av nyckelordet OUTPUT Inserted.ID,... INTO @.
. I ett antal DBMS är det tillåtet att deklarera triggers för flera åtgärder samtidigt. För att implementera olika reaktioner på olika åtgärder tillhandahåller Oracle predikat Deleting, Inserting, Update, som returnerar True för motsvarande typ av uppdatering.
. I Oracle DBMS kan du ange en lista med kolumner (Efter uppdatering av) för uppdateringsutlösare, vilket säkerställer att utlösaren endast anropas när värdena för endast dessa kolumner ändras.
. Flera triggers kan deklareras för varje triggerhändelse (Oracle har 12 triggers per tabell) och vanligtvis bestäms ordningen i vilken de utlöses av i vilken ordning de skapas. I vissa DBMS, som InterBase, specificeras startordningen med hjälp av det extra nyckelordet POSITION. I allmänhet bör utlösare exekveras för varje kommando först och sedan för varje rad.
. Triggers kan bäddas in i varandra. Således tillåter SQL Server 32 kapslingsnivåer (du kan använda @@NextLevel global variabel för att bestämma kapslingsnivån).

Nackdelar med triggers

Komplexitet. Att placera vissa åtgärder på data i databasen komplicerar dess design, implementering och administration.
. Smygande funktionalitet från användaren. Det är svårt att modernisera en applikation när vissa funktioner är dolda.
. Inverkan på prestanda. Med ett litet antal triggers ökar databehandlingstiden.

Redigera och ta bort triggers

För att ta bort en trigger, använd DROP TRIGGER-satsen
. För att ändra en trigger, använd ALTER TRIGGER...-satsen.
. Inaktiverar triggers
I vissa fall, till exempel under batchladdning, måste triggers inaktiveras. Ett antal DBMS tillhandahåller motsvarande funktioner. I Oracle och SQL Server är nyckelorden DISABLE|ENABLE, i InterBase INACTIVE|ACTIVE i ALTER TRIGGER-satsen.

Funktioner hos industriella servrar

1) InterBase/Firebird

SKAPA TRIGGER FÖR {AKTIV|INAKTIV} {FÖRE|EFTER} {INFOGA|RADERA|UPPDATERA} [PLACERA ]
SOM [DEKLARERA VARIABEL [()]]
BÖRJA

SLUTET

Exempel:

SKAPA TRIGGER BF_Del_Cust FÖR kunden
AKTIV INNAN RADERA POSITION 1 SOM
BÖRJA
DELETE FROM Orders WHERE Orders.CNum=Kund.CNum;
SLUTET;

2) SQL Server

SKAPA TRIGGER PÅ [MED KRYPTERING] {FÖR|AFTER|I STÄLLET FÖR} {INFOGA|UPPDATERA|RADERA}
SOM

ANVÄND B1;
GÅ
SKAPA TRIGGER InUpCust1 PÅ kunden EFTER INFOGA, UPPDATERING
AS RAISEERROR('Kundtabell ändrad');

Ytterligare typer av triggers

Oracle och SQL Server ger möjlighet att skapa (ersättande) triggers för vyer som inte uppdateras. För detta ändamål tillhandahålls INSTEAD OF nyckelorden:

SKAPA TRIGGER PÅ I STÄLLET FÖR INFOGA SOM ...

Du kan övervaka klientförsök att uppdatera data med hjälp av vyer och utföra eventuella åtgärder, hantera vyer som inte uppdateras, etc.
. SQL Server DBMS tillhandahåller en återställningsutlösare som faktiskt stoppar alla åtgärder och skickar ett meddelande:

ROLLBACK TRIGGER

Föreläsningsanteckningar om disciplinen "Databaser"

Ämne: Procedurer och triggers

(med MS SQL Server som exempel)

kompilator: L. V. Shchegoleva

PetrSU, Institutionen för tillämpad matematik och kybernetik

Introduktion................................................. ...................................................................... ............................
	Beskrivning av databasstrukturen......................................................... ............................
	Begreppet procedur................................................... ....................................................
	Kommandon för att arbeta med procedurer......................................... ............................................
	Konceptet med en trigger................................................ ......................................................
	Kommandon för att arbeta med triggers.......................................... ...................................................
	Exempel på triggerimplementeringar.................................................. ......................
	Exempel 1................................................ ...........................................................
	Exempel 2................................................ ...........................................................
	Exempel 3................................................ ...........................................................
	Exempel 4................................................ ...........................................................
	Exempel 5................................................ ...........................................................

PetrSU, Institutionen för tillämpad matematik och kybernetik

Introduktion

I Den här handboken ger exempel på kommandon för att skapa procedurer och utlösare

Med beskrivning av deras arbete.

Alla kommandon är skrivna i MS SQL Server-syntax.

Exemplen ges för en databas vars struktur beskrivs i

sektion 1.

PetrSU, Institutionen för tillämpad matematik och kybernetik

1 Beskrivning av databasstrukturen

tblFakultetstabellen innehåller information om universitetets fakulteter.

namn			Beskrivning
attribut
			Fakultets ID
			Fakultetens namn
			Dekanus fullständiga namn
			Dekanus nummer
			Dekanus telefonnummer
			Antal fakultetsstudenter

tblStudenttabellen innehåller information om universitetsstudenter under ett läsår.

Attributnamn			Beskrivning
			Student-ID
			Elevens namn

			Stipendium

Tabellen tblGroup innehåller information om universitetsstudentgrupper under ett läsår.

Attributnamn			Beskrivning
			Grupp-ID
			Hövding
			Gruppnummer
			Fakultet

tbl Ämnestabellen innehåller information om de discipliner som studerats av fakultetens studenter under ett läsår.



	Attributnamn			Beskrivning
				Artikelnummer
				Föremålsnamn
				Antal föreläsningstimmar
				Antal övningstimmar
				Fakultet

	Föreläsningsanteckningar om disciplinen "Databaser" (procedurer och triggers)

PetrSU, Institutionen för tillämpad matematik och kybernetik

Tabellen tblRoom innehåller information om universitetets klassrum.

Tabellen tblSchedule innehåller information om schemat för elevgrupper.

Attributnamn			Beskrivning
			Identifierare
			Publik
			Veckodag



			Lärarens fullständiga namn

PetrSU, Institutionen för tillämpad matematik och kybernetik

2 Begreppet förfarande

En procedur är ett program skrivet på det interna språket i DBMS, lagrat i databasen som ett oberoende objekt. Sådana procedurer kallas vanligtvis lagrade, bifogade procedurer. Förfaranden kan anropas av ett ansökningsprogram. Procedurerna exekveras på databasservern. Procedurer kan innehålla parametrar och returvärden, inklusive felmeddelanden.

Fördelar med att använda procedurer:

– centraliserad dataåtkomstkontroll;

– applikationsprogram kan anropa en procedur, vilket minskar tiden för att skriva program, när en procedur ändras kommer alla program som anropar den att få ny kod, kodoptimering;

– minskar nätverkstrafiken i systemen"klient-server" genom att bara skicka namnet på proceduren och dess parametrar istället för att utbyta data, och proceduren exekveras på servern;

– döljer många funktioner för användaren specifik enhet databaser, vilket säkerställer större dataoberoende;

– större datasäkerhet kan användaren ha rätt att ringa

procedur, men manipulerar inte data som anropas av denna procedur; Nackdel: brist på standarder vid genomförandet av förfaranden.

PetrSU, Institutionen för tillämpad matematik och kybernetik

3 kommandon för att arbeta med procedurer i MS SQL Server

Skapa en procedur

SKAPA PROCEDUR<имя процедуры>

[@<имя параметра> <тип данных> , ...]

BÖRJA

<операторы>

Namnen på alla variabler i MS SQL Server måste börja med symbolen

Kalla en procedur

KÖR<имя процедуры> [{@<имя переменной> | <значение параметра>}, ...]

Ta bort en procedur

SLIPPROCEDUR<имя процедуры>

Proceduren räknar antalet studenter som är inskrivna på institutionen vars identifierare är indataparametern för @id-proceduren och returnerar detta värde i parametern @total_sum.

Skapa procedur prStudentsOfFaculty @id int, @total_sum int output AS

Ställ in @total_sum = 0

Ställ in @total_sum = (Välj antal(*) Från tblStudent, tblGroup Where (tblStudent.GroupId = tblGroup.GroupId) och (tblGroup.FacultyId = @id)) End

PetrSU, Institutionen för tillämpad matematik och kybernetik

4 Konceptet med en trigger

En trigger (regel) är kopplad till en tabell och anropas automatiskt av databashanteringssystemet när uppdateringsåtgärder utförs på tabellen (lägger till, raderar, ändrar tabellposter).

Funktioner för att implementera triggers i MS SQL Server

I MS SQL Server:

– en trigger kan anropas antingen efter att en operation har utförts, eller istället för att en operation utförs;

– triggern anropas en gång för alla tabellposter på vilka operationen måste utföras;

– därför lagras posterna som ändras i två tabeller som skapas automatiskt när utlösaren anropas:

o Infogad tabell – innehåller ändrade eller tillagda tabellposter;

o Borttagen tabell – innehåller poster innan ändringar gjordes eller raderade tabellposter;

– i brödtexten av en utlösare definierad för en infogningsoperation är bara tabellen tillgänglig

– i kroppen av utlösaren som definierats för raderingsoperationen är bara tabellen tillgänglig

– i kroppen av utlösaren som definierats för uppdateringsoperationen är båda tabellerna tillgängliga

Infogat och raderat;

– Valfritt antal triggers kan skapas för samma händelse, och de anropas i en slumpmässig ordning (möjligen i den ordning som de skapades).

PetrSU, Institutionen för tillämpad matematik och kybernetik

5 kommandon för att arbeta med triggers

Skapande

SKAPA TRIGGER<имя триггера>PÅ<имя таблицы>

(FÖR | EFTER | I STÄLLET FÖR)

[INSERT] [,] [UPPDATERA] [,] [RADERA] SOM

DEKLARERA @<имя переменной> <тип данных>, ...

BÖRJA<операторы>

Borttagning

SLÄPP AVTRÖREN<имя триггера>

PetrSU, Institutionen för tillämpad matematik och kybernetik

6 Exempel på triggerimplementationer

Ämnesområdesbegränsning: En students stipendium kan inte ökas med mer än 5 % av det tidigare stipendiet.

SKAPA TRIGGER tgrStudentGrantUpdate

PÅ tblStudent EFTER UPPDATERING

DECLARE @Grant_old float, @Grant_new float, @Id int;

Välj @Grant_old = Bevilja från Borttaget

Välj @Grant_new = Grant, @Id = StudentId från Infogat

IF (@Grant_new - @Grant_old > 0,05 * @Grant_old)

UPPDATERING tblStudent SET Grant = 1,05 * @Grant_old

WHERE StudentId = @Id

Utlösaren tgrStudentGrantUpdate har skapats för tabellen tblStudent. Utlösaren aktiveras efter att dataändringsoperationen är klar.

I Triggern definierar tre lokala variabler: @Grant_old (riktig typ) för att lagra det gamla studentstipendiet, @Grant_new (real typ) för att lagra det nya studentstipendiet, @Id (heltalstyp) för att lagra studentidentifieraren.

När en utlösare anropas skapar DBMS två tabeller: Deleted, som innehåller de modifierade posterna innan de ändras, och Inserted, som innehåller de modifierade posterna efter att de modifierats.

I I kroppen av utlösaren hämtas först och främst värdet av studentens stipendium från tabellen Borttaget innan ändringarna görs, det vill säga det gamla stipendiet; sedan hämtas värdet av studentens stipendium från tabellen Insatt efter att förändringar görs, d.v.s. det nya stipendiet. Tillsammans med att ett nytt stipendium hämtas från tabellen Infogade, hämtas även studentidentifieraren. Student-ID kunde lika gärna hämtas från tabellen Borttaget.

Därefter, i utlösarens kropp, kontrolleras villkoret om storleken på ändringen i stipendiet. Om stipendiet har ändrats med mer än 5 %, gör utlösaren en justering av data - det ökar stipendiet med endast 5 % jämfört med studentens tidigare stipendievärde. Denna åtgärd utförs genom att anropa uppdateringsoperationen på tblStudent-tabellen för motsvarande elev.

Definitionen av en utlösare, omfattningen av dess användning, platsen och rollen för utlösaren för att säkerställa dataintegritet anges. Typer av triggers beskrivs. Operatörer för att skapa, ändra och ta bort en utlösare övervägs. Triggerprogrammering illustreras med exempel på att skapa triggers för att implementera integritetsbegränsningar och samla in statistisk data.

Trigger Definition i SQL Language Standard

Triggers är en typ av lagrad procedur. De exekveras när en DML-operator (Data manipulation language) exekveras på bordet. Triggers används för att kontrollera dataintegriteten och även för att återställa transaktioner.

En trigger är en kompilerad SQL-procedur, vars exekvering orsakas av förekomsten av vissa händelser i en relationsdatabas. Användningen av triggers är för det mesta mycket bekvämt för databasanvändare. Ändå innebär deras användning ofta extra resurskostnader för I/O-operationer. När samma resultat (med mycket mindre overhead) kan uppnås med hjälp av lagrade procedurer eller applikationsprogram, är det inte praktiskt att använda triggers.

Triggers är ett speciellt SQL-serververktyg som används för att upprätthålla dataintegriteten i en databas. Integritetsbegränsningar, regler och standardinställningar kanske inte alltid uppnår den önskade funktionalitetsnivån. Det är ofta nödvändigt att implementera komplexa dataverifieringsalgoritmer för att säkerställa deras tillförlitlighet och verklighet. Dessutom behöver du ibland övervaka förändringar i tabellvärden så att tillhörande data kan ändras efter behov. Triggers kan ses som ett slags filter som träder i kraft efter att alla operationer har genomförts i enlighet med regler, standardvärden etc.

En utlösare är en speciell typ av lagrad procedur som startas automatiskt av servern när ett försök görs att ändra data i de tabeller som utlösarna är kopplade till. Varje utlösare är associerad med en specifik tabell. Alla dataändringar som den gör betraktas som en transaktion. Om ett fel eller dataintegritetsintrång upptäcks återställs transaktionen. Ändringar är därför förbjudna. Alla ändringar som redan gjorts av utlösaren ångras också.

Endast databasägaren kan skapa en utlösare. Denna begränsning låter dig undvika oavsiktliga ändringar av tabellstrukturen, sätt att koppla andra objekt till dem, etc.

Avtryckaren är ett mycket användbart och samtidigt farligt verktyg. Så om logiken för dess operation är felaktig kan du enkelt förstöra en hel databas, så triggers måste felsökas mycket noggrant.

Till skillnad från en vanlig rutin exekveras en trigger implicit varje gång den inträffar. utlösa händelsen, dessutom har det inga argument. Att aktivera den kallas ibland att avfyra en avtryckare. Med hjälp av triggers uppnås följande mål:

Validera korrektheten av inmatade data och upprätthålla komplexa dataintegritetsbegränsningar som är svåra, för att inte säga omöjliga, att upprätthålla med hjälp av integritetsbegränsningar som ställts in i en tabell;
utfärda varningar som påminner dig om att utföra vissa åtgärder när du uppdaterar en tabell implementerad på ett visst sätt;
ackumulering av revisionsinformation genom att registrera information om de ändringar som gjorts och de personer som utförde dem;
replikeringsstöd.

Det grundläggande formatet för CREATE TRIGGER-kommandot visas nedan:

<Определение_триггера>::= SKAPA TRIGGER trigger_name INNAN | EFTER<триггерное_событие>PÅ<имя_таблицы> <тело_триггера>

utlösa händelser består av att infoga, ta bort och uppdatera rader i en tabell. I det senare fallet för utlösa händelsen Du kan ange specifika tabellkolumnnamn. Utlösningstiden bestäms med hjälp av nyckelord FÖRE (utlösaren aktiveras innan de händelser som är associerade med den exekveras) eller EFTER (efter att de exekveras).

Åtgärderna som utförs av utlösaren specificeras för varje rad (FÖR VARJE RAD) som täcks av en given händelse, eller endast en gång för varje händelse (FÖR VARJE UTTALANDE).

Beteckning <список_старых_или_новых_псевдонимов> syftar på komponenter som gamla eller ny linje(GAMMEL/NY) antingen gammal eller nytt bord(GAMMEL BORD / NYTT BORD). Det är tydligt att de gamla värdena inte gäller för att infoga händelser, och de nya värdena gäller inte för att radera händelser.

När de används på rätt sätt kan triggers vara en mycket kraftfull mekanism. Deras främsta fördel är att standardfunktioner lagras i databasen och aktiveras konsekvent varje gång den uppdateras. Detta kan avsevärt förenkla applikationer. Det är dock värt att nämna de inneboende nackdelarna med triggern:

komplexitet: när vissa funktioner flyttas till databasen blir uppgifterna för dess design, implementering och administration mer komplexa;
Dold funktionalitet: Att flytta viss funktionalitet till en databas och lagra den som en eller flera triggers resulterar ibland i att någon funktionalitet döljs för användaren. Även om detta förenklar driften till viss del, kan det tyvärr orsaka oavsiktliga, potentiellt oönskade och skadliga biverkningar, eftersom användaren i det här fallet inte kan kontrollera alla processer som sker i databasen;
prestandapåverkan: innan varje kommando utförs för att ändra tillståndet för databasen, måste DBMS kontrollera triggervillkoret för att avgöra om en trigger ska aktiveras för detta kommando. Att utföra sådana beräkningar påverkar den övergripande prestandan för DBMS, och vid tider med toppbelastning kan dess minskning bli särskilt märkbar. Uppenbarligen, när antalet triggers ökar, ökar också de omkostnader som är förknippade med sådana operationer.

Felaktigt skrivna triggers kan leda till allvarliga problem, som döda låsningar. Utlösare kan blockera många resurser under långa perioder, så särskild uppmärksamhet bör ägnas åt att minimera åtkomstkonflikter.

Implementering av triggers i MS SQL Server-miljö

MS SQL Server DBMS-implementeringen använder följande operatör för att skapa eller ändra utlösare:

En trigger kan bara skapas i den aktuella databasen, men det är möjligt att komma åt andra databaser inom triggern, inklusive de som finns på en fjärrserver.

Låt oss titta på syftet med argumenten från CREATE | ALTER TRIGGER.

Triggernamnet måste vara unikt i databasen. Dessutom kan du ange ägarens namn.

Triggertyper

Det finns två alternativ i SQL Server som bestämmer beteendet hos utlösare:

EFTER. Utlösaren exekveras efter att de kommandon som anropade den har slutförts. Om kommandona av någon anledning inte kan slutföras framgångsrikt, exekveras inte triggern. Det bör noteras att dataändringar som ett resultat av exekvering av en användarbegäran och triggerexekvering utförs i kroppen av en transaktion: om triggern rullas tillbaka, kommer användarändringar också att avvisas. Du kan definiera flera AFTER-utlösare för varje operation (INSERT, UPDATE, DELETE). Om du har flera AFTER-utlösare på en tabell kan du använda sp_settriggerorder-systemets lagrade procedur för att ange vilken utlösare som ska köras först och vilken som körs sist. Som standard, i SQL Server, är alla utlösare AFTER-utlösare.
ISTÄLLET FÖR . Utlösaren anropas istället för att utföra kommandon. Till skillnad från AFTER-utlösaren kan INSTEAD OF-utlösaren definieras för både en tabell och en vy. För varje INSERT-, UPDATE-, DELETE-operation kan endast en INSTEAD OF-utlösare definieras.

Triggers kännetecknas av den typ av kommandon som de svarar på.

Det finns tre typer av triggers:

INSERT TRIGGER – Utlöses när ett försök görs att infoga data med kommandot INSERT.
UPDATE TRIGGER – utlöses när ett försök görs att ändra data med kommandot UPDATE.
DELETE TRIGGER – utlöses när ett försök görs att radera data med kommandot DELETE.

WITH APPEND låter dig skapa flera triggers av varje typ.

På skapa en trigger med argumentet INTE FÖR REPLIKATION är det förbjudet att köras medan tabeller modifieras av replikeringsmekanismer.

AS sql_operator[...n]-konstruktionen definierar en uppsättning SQL-satser och kommandon som kommer att exekveras när triggern startas.

Observera att ett antal operationer inte är tillåtna i en utlösare, till exempel:

skapa, modifiera och ta bort en databas;
återställa en säkerhetskopia av en databas eller transaktionslogg.

Trigger programmering

INSERT kommando – den infogade tabellen innehåller alla rader som användaren försöker infoga i tabellen; det kommer inte att finnas en enda rad i den raderade tabellen; efter att triggern är klar kommer alla rader från den infogade tabellen att flyttas till källtabellen;
DELETE kommando – den raderade tabellen kommer att innehålla alla rader som användaren försöker ta bort; utlösaren kan kontrollera varje rad och avgöra om den är tillåten att raderas; det kommer inte att finnas några rader i den infogade tabellen;
UPDATE-kommandot - när det körs innehåller den borttagna tabellen gamla radvärden som kommer att raderas efter framgångsrikt slutförande av triggern. De nya radvärdena finns i den infogade tabellen. Dessa rader kommer att läggas till i källtabellen efter att triggern har utförts.

För att få information om antalet rader som kommer att ändras när en trigger slutförs framgångsrikt, kan du använda @@ROWCOUNT;-funktionen. det returnerar antalet rader som bearbetades av det sista kommandot. Det bör betonas att avtryckaren inte aktiveras när ett försök görs att ändra en specifik rad, utan i det ögonblick som ändringskommandot utförs. Ett sådant kommando påverkar många rader, så utlösaren måste bearbeta alla dessa rader.

Om utlösaren upptäcker att av 100 rader som infogas, ändras eller tas bort, är det bara en som inte uppfyller vissa villkor, då kommer ingen rad att infogas, ändras eller tas bort. Detta beteende beror på transaktionens krav - antingen måste alla ändringar utföras eller ingen.

En trigger körs som en implicit definierad transaktion, så transaktionskontrollkommandon kan användas inom triggern. Närmare bestämt, när en överträdelse av integritetsbegränsningen upptäcks, måste kommandot ROLLBACK TRANSACTION användas för att avbryta utlösaren och ångra alla ändringar som användaren försökte göra.

Du kan använda funktionen COLUMNS_UPDATED() för att få en lista över kolumner som modifierades av kommandona INSERT eller UPDATE som fick utlösaren att köras. Den returnerar ett binärt tal, där varje bit, som börjar med den minst signifikanta biten, motsvarar en kolumn i tabellen (i kolumnernas ordning när tabellen skapades). Om en bit är satt till "1", har motsvarande kolumn ändrats. Dessutom bestäms det faktum att en kolumn har ändrats av UPDATE-funktionen (kolumnnamn).

För ta bort avtryckaren kommandot används

SLAPP TRIGGER (trigger_name) [,...n]

Här är exempel på användning av triggers.

Exempel 14.1. Använder en trigger för att genomförande av värdebegränsningar. I posten som läggs till i Transaktionstabellen får mängden av den sålda produkten inte vara mindre än saldot från Lagertabellen.

Kommandot för att infoga en post i Deal-tabellen kan till exempel vara så här:

INFOGA I Handelsvärden (3,1,-299,"01/08/2002")

Den skapade utlösaren bör reagera på dess utförande på följande sätt: det är nödvändigt att avbryta kommandot om i lagertabellen produktbalansen är mindre än den sålda kvantiteten av produkten med den angivna koden (i exemplet, produktkod = 3 ). I den infogade posten anges produktens kvantitet med ett "+"-tecken om produkten levereras och med ett "-"-tecken om den säljs. Den presenterade utlösaren är konfigurerad att endast bearbeta en tillagd post.

SKAPA TRIGGER Trigger_ins PÅ Transaktion FÖR INFOGA SOM OM @@ROWCOUNT=1 BÖRJA OM INTE FINNS(VÄLJ * FRÅN infogat WHERE -inserted.quantity<=ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT "Отмена поставки: товара на складе нет" END END Exempel 14.1. Använda en utlösare för att implementera begränsningar på ett värde.

Exempel 14.2. Använda en trigger för att samla in statistisk data.

Skapa en utlösare för att bearbeta operationen att infoga en post i tabellen Deal, till exempel följande kommando:

INFOGA I Handelsvärden (3,1,200,"01/08/2002")

Produkt med kod 3 levereras från kund med kod 1 i mängden 200 enheter.

Vid försäljning eller mottagande av en vara måste lagermängden anpassas därefter. Om produkten ännu inte finns i lager måste du lägga till en motsvarande post i lagertabellen. Utlösaren bearbetar endast en tillagd rad.

ALTER TRIGGER Trigger_ins PÅ Transaktion FÖR INSERT AS DECLARE @x INT, @y INT IF @@ROWCOUNT=1 --en post läggs till i Transaktionstabellen om leverans av varor BEGIN --mängden sålda varor får inte vara -- mindre än saldot från Warehouse-tabellen OM INTE FINNS(SELECT * FROM inserted WHERE -inserted.quantity< =ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT "откат товара нет " END --если записи о поставленном товаре еще нет, --добавляется соответствующая запись --в таблицу Склад IF NOT EXISTS (SELECT * FROM Склад С, inserted i WHERE С.КодТовара=i.КодТовара) INSERT INTO Склад (КодТовара,Остаток) ELSE --если запись о товаре уже была в таблице --Склад, то определяется код и количество --товара издобавленной в таблицу Сделка записи BEGIN SELECT @y=i.КодТовара, @x=i.Количество FROM Сделка С, inserted i WHERE С.КодТовара=i.КодТовара --и производится изменения количества товара в --таблице Склад UPDATE Склад SET Остаток=остаток+@x WHERE КодТовара=@y END END Exempel 14.2. Använda en trigger för att samla in statistisk data.

Exempel 14.3. Skapa en utlösare för att bearbeta operationen att ta bort en post från tabellen Deal, till exempel följande kommando:

För produkten vars kod angavs när posten raderades är det nödvändigt att justera dess lagersaldo. Utlösaren bearbetar endast en post som ska raderas.

SKAPA TRIGGER Trigger_del PÅ Transaktion FÖR DELETE AS IF @@ROWCOUNT=1 -- en post raderas BÖRJA DECLARE @y INT,@x INT --koden och kvantiteten för produkten bestäms från --posten som raderats från Warehouse-tabellen VÄLJ @y=Produktkod, @ x=Antal FRÅN raderad --i lagertabellen justeras mängden --artikel UPPDATERA Lageruppsättning Återstående=Återstående-@x WHERE Produktkod=@y END Exempel 14.3. Trigger för att bearbeta operationen att ta bort en post från en tabell

Exempel 14.4. Skapa en utlösare för att bearbeta operationen att ändra en post i tabellen Deal, till exempel med följande kommando:

i alla transaktioner med varor med en kod lika med 3, minska mängden varor med 10 enheter.

Det angivna kommandot kan leda till ändringar i flera poster samtidigt i tabellen Deal. Därför kommer vi att visa hur man skapar en trigger som bearbetar mer än en post. För varje ändrad post är det nödvändigt för den gamla (före ändringen) produktkoden att reducera saldot av produkten i lagret med värdet av den gamla (före ändringen) kvantitet av produkten och för den nya (efter ändra) produktkod för att öka saldot i lagret med värdet av det nya (efter ändringen) värdet. För att bearbeta alla ändrade poster kommer vi att introducera markörer där vi sparar alla gamla (från den raderade tabellen) och alla nya värden (från den infogade tabellen).

SKAPA TRIGGER Trigger_upd PÅ Transaktion FÖR UPPDATERING SOM DECLARE @x INT, @x_old INT, @y INT, @y_old INT -- markör med nya värden DECLARE CUR1 CURSOR FOR SELECT Produktkod, Kvantitet FROM infogat -- markör med gamla värden DECLARE CUR2 CURSOR FÖR VALD produktkod, kvantitet FRÅN raderad OPEN CUR1 OPEN CUR2 -- flytta parallellt genom båda markörerna HÄMTA NÄSTA FRÅN CUR1 INTO @x, @y HÄMTA NÄSTA FRÅN CUR2 INTO @x_old, @y_old MEDAN @@FETCH_STATUS= BÖRJA -- för den gamla produktkoden minskar den -- kvantiteten i lagret UPPDATERA Lager SET Remaining=Remaining-@y_old WHERE Product Code=@x_old --för en ny produktkod, om en sådan produkt ännu inte finns i lager, en ny post läggs in OM INTE FINNS (VÄLJ * FRÅN Warehouse WHERE Product Code=@x) INSERT INTO Warehouse(Produktkod, Återstående) VÄRDEN (@x,@y) ANNAT -- annars för en ny produktkod ökar den -- dess kvantitet i lager UPPDATERA Lager SET Remaining=Remaining+@y WHERE Product Code=@x HÄMTA NÄSTA FRÅN CUR1 INTO @x, @y HÄMTA NÄSTA FRÅN CUR2 INTO @x_old, @y_old AVSLUTA STÄNG CUR1 STÄNG CUR2 AVALLOKERA CUR1 DEALLOCATE CUR2 Exempel 14.4. trigger för att bearbeta en operation för att ändra en post i en tabell

I den övervägda triggern finns det ingen jämförelse av mängden varor när en transaktionspost ändras med dess saldo i lagret.

Exempel 14.5. Låt oss rätta till denna brist. För att generera ett felmeddelande använder vi MS SQL Server RAISERROR-kommandot i utlösarens kropp, vars argument är meddelandetexten, allvarlighetsgrad och felstatus.

ÄNDRA TRIGGER Trigger_upd PÅ Transaktion FÖR UPPDATERING SOM DECLARE @x INT, @x_old INT, @y INT, @y_old INT ,@o INT DECLARE CUR1 CURSOR FÖR SELECT Artikelkod, Kvantitet FRÅN infogat DECLARE CUR2 CURSOR FÖR SELECT Artikelkod, raderad kvantitet ÖPPNA CUR1 ÖPPNA CUR2 HÄMTA NÄSTA FRÅN CUR1 INTO @x, @y HÄMTA NÄSTA FRÅN CUR2 INTO @x_old, @y_old MEDAN @@FETCH_STATUS=0 BÖRJA VÄLJ @o=återstående FRÅN lager VAR produktkod=@x IF @o<-@y BEGIN RAISERROR("откат",16,10) CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR22 ROLLBACK TRAN RETURN END UPDATE Склад SET Остаток=Остаток-@y_old WHERE КодТовара=@x_old IF NOT EXISTS (SELECT * FROM Склад WHERE КодТовара=@x) INSERT INTO Склад(КодТовара,Остаток) VALUES (@x,@y) ELSE UPDATE Склад SET Остаток=Остаток+@y WHERE КодТовара=@x FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old END CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 Exempel 14.5. Korrigerad version av utlösaren för att bearbeta operationen för att ändra en post i en tabell

Exempel 14.6. I exemplet avbryts alla ändringar om det är omöjligt att implementera minst en av dem. Låt oss skapa en utlösare som låter dig avbryta ändringar av endast vissa poster och ändra resten.

I det här fallet exekveras inte triggern efter att posterna har ändrats, utan istället för ändringskommandot.

ALTER TRIGGER Trigger_upd PÅ Transaktion I STÄLLET FÖR UPPDATERA SOM DECLARE @k INT, @k_old INT DECLARE @x INT, @x_old INT, @y INT DECLARE @y_old INT ,@o INT DECLARE CUR1 CURSOR FOR SELECT Transaction Code, Product Code, Quantity FROM infogat DECLARE CUR2 CURSOR FÖR VALD Transaktionskod, Produktkod, Kvantitet FRÅN raderad OPEN CUR1 ÖPPNA CUR2 HÄMTA NÄSTA FRÅN CUR1 INTO @k,@x, @y HÄMTA NÄSTA FRÅN CUR2 INTO @k_old,@x_old, @y_old WHILE @@TUS=FETCH 0 BÖRJA VÄLJ @ o=återstående FRÅN lagret VAR Produktkod=@x OM @o>=-@y BÖRJA HÖJNING("ändra",16,10) UPPDATERA Transaktions SET kvantitet=@y, Produktkod=@x VAR Transaktionskod =@k UPPDATERA Warehouse SET Remaining =Remaining-@y_old WHERE Artikelkod=@x_old OM INTE FINNS (VÄLJ * FRÅN Warehouse WHERE Artikelkod=@x) INFOGA I Warehouse(Artikelkod, Återstående) VÄRDEN (@x,@y) ANDERS UPPDATERA Warehouse SET Remaining=Remaining+@y WHERE Item Code=@x END ELSE RAISERROR("post ej ändrad",16,10) HÄMTA NÄSTA FRÅN CUR1 INTO @k,@x, @y HÄMTA NÄSTA FRÅN CUR2 INTO @k_old, @x_old, @y_old AVSLUTA STÄNG CUR1 STÄNG CUR2 AVALLOCERA CUR1 AVALLOCERA CUR2 Exempel 14.6. En utlösare som låter dig ångra ändringar i endast vissa poster och göra ändringar i resten.