Hvordan bruke SQL Server -utløsere

Utløsere er spesiell type lagrede prosedyrer som utføres som et svar på aktiviteter i SQL Server -objekter. Utløsere lagres i systemets minne og blir bare utført når en spesifikk hendelse oppstår. I motsetning til normale lagrede prosedyrer, godtar ikke triggere parametere eller utføres manuelt.

I denne artikkelen vil vi utforske hvordan vi kan lage og jobbe med triggere i SQL Server.

Det er tre hovedtyper av triggere i SQL Server:

1. DML utløser
2. DDL utløser
3. Påloggingsutløsere

La oss utforske disse triggerne i denne guiden.

SQL Server DML utløser

DML- eller datamanipulasjonsspråkutløsere er type triggere som skyter som svar på et innlegg, oppdatering og sletter operasjonen på en tabell eller visning. DML -triggere vil kjøre når noen gyldig operasjon utføres, enten noen rader er berørt.

Lag DML etter trigger

I SQL Server kan du opprette en DML -trigger ved hjelp av Create Trigger -setningen.

Lag Trigger Schema_name.Trigger_name
På tabellnavn
Etter [sett inn, oppdater, slett]
SOM
-- Utløs kode for å kjøre

La oss bryte ned syntaksen ovenfor:

1. Skjemanavn - Navnet på skjemaet der avtrekkeren er lagret.
2. Trigger_name - Navn på utløseren.
3. Tabell_navn - Navnet på tabellen der den spesifiserte utløseren vil gjelde.
4. Etter - en etterklausul for å definere under hvilken betingelser utløseren vil gjelde.

For å lære å lage og bruke en DML -trigger, la oss ta et praktisk eksempel.

Opprett eksempeldatabase og sett inn dataene som angitt i settet med spørsmål vist nedenfor:

-- Opprett eksempeldatabase
Opprette databasesalg;
GÅ
-- bryter db;
Bruke salg;
-- Lag tabell
Lag bordsalg (
id int identitet (1,1) ikke null primærnøkkel,
produktnavn varchar (50),
pris penger,
Mengde int
);
-- Sett inn prøvedata
Sett inn salg (produktnavn, pris, mengde)
Verdier ('iPhone Charger', $ 9.99, 10),
('Google Chromecast', $ 59.25, 5),
('PlayStation DualSense Wireless Controller', $ 69.00, 100),
('Xbox Series S', $ 322.00, 3),
('Oculus Quest 2', $ 299.50, 7),
('Netgear Nighthawk', $ 236.30, 40),
('REDRAGON S101', $ 35.98, 100),
('Star Wars actionfigur', $ 17.50, 10),
('Mario Kart 8 Deluxe', $ 57.00, 5);

Når vi har prøvedataene, kan vi fortsette og lage en DML -utløser for å skyte på en oppdateringsoperasjon på tabellen.

Tenk på eksemplet som er vist nedenfor:

-- Lag tabell for å lagre oppdateringshistorikk
Opprett tabell ModifiedDate (ID Int, Date_ DateTime)
GÅ
-- Lag trigger
Lag trigger dbo.update_trigger
På salg
Etter oppdatering
Ikke for replikering
SOM
BEGYNNE
Sett inn ModifiedDate
Velg ID, GetDate ()
Fra satt inn
SLUTT

Ovennevnte spørring vil opprette en trigger som skyter når vi utfører en oppdatering på tabellen. For å teste trigger kan vi kjøre en oppdatering som:

-- Oppdateringstabell
Oppdater salgssett pris = $ 10.10
Hvor id = 1;

Etter utførelse kan vi sjekke om avtrekkeren fungerte ved å velge kolonnene i ModifiedDate -tabellen.

-- Kontroller ModifiedDate Table
Velg * fra ModifiedDate;

I SSM -er kan du se utløserne på et bord ved å utvide alternativet Triggers:

Lag i stedet for triggere

Den andre typen DML -utløsere i SQL Server er i stedet for utløsere. Dette er type triggere som utfører i stedet for DML -setningen. For eksempel, hvis vi spesifiserer en sletterklæring, kan vi bruke i stedet for triggere til å kjøre før operasjonen.

Syntaksen for å lage en i stedet for trigger er som vist:

Lag Trigger Schema_name.Trigger_name
På tabellnavn
I stedet for [sett inn, oppdater, slett]
SOM
-- utløser utsagn

For eksempel oppretter spørringen nedenfor en trigger som viser en melding når en innsatsoperasjon utføres på tabellen.

-- Lag i stedet for trigger
Lag trigger i stedet_Insert
På salg
i stedet for å sette inn
SOM
BEGYNNE
Velg 'Du kan ikke sette inn i denne tabellen' som feil
SLUTT
-- Kjør i stedet_Insert Trigger
Sett inn salg (produktnavn, pris, mengde)
Verdier ('iPhone Charger', $ 9.99, 10);

Når vi har kjørt spørringen ovenfor, bør vi få en melding som indikerer at vi ikke kan utføre et innlegg på bordet.

Feil
-------------------------------

Du kan ikke sette inn i denne tabellen

SQL DDL utløser

DDL- eller datadefinisjonsspråk er triggere som svarer på hendelser på serveren eller databasen i stedet for en tabell. DDL -triggere vil svare på hendelser som Drop, Grant, NEny, Revok, oppdatere statistikk, opprette og endre.

Lag DDL -triggere

Syntaksen for å lage en DDL -trigger er som vist:

Lag Trigger Trigger_name
På databasen | Alle serveren
Med DDL_TRIGGER_PARAMETERS
For Event_Type | event_group
SOM
-- utløser utsagn

Vi kan bryte ned syntaksen som:

1. trigger_name - unikt navn på utløseren.
2. database eller all server - spesifiser hvor avtrekkeren er utført. Database hvis den gjelder på databasen eller alle serveren hvis den gjelder serveromfanget.
3. DDL_TRIGGER_PARAMETER - DDL -parametere som utfør som, eller krypterte som.
4. Event_type - DDL -hendelsen som skyter utløseren.

Eksemplet spørring nedenfor Opprett en DDL -trigger som avfyrer når en drop tabellerklæring er utstedt.

-- Lag DDL -trigger
Lag trigger drop_ddl_trigger
På databasen
For drop_table
SOM
BEGYNNE
Velg EventData ();
SLUTT

Når vi har kjørt en drop -arrangement i databasen, vil utløseren vise hendelsesinformasjon ved hjelp av EventData () -funksjonen.

Vi kan teste utløseren:

-- Testutløser
Drop Table Sales;

Spørringen skal returnere XML -informasjon om hendelsen som:

I SSMS kan du se utløserne ved å utvide databasen utløser under programmerbarhet i måldatabasen.

Aktiver/deaktiver utløsere

SQL Server lar deg aktivere og deaktivere triggere. For å aktivere en trigger på et bord, bruk spørringen som:

Aktiver Trigger Update_Trigger ved salg;

Hvor oppdatering_trigger representerer triggernavn og salg representerer tabellnavnet.

Du kan også aktivere alle triggere på et bord som:

Aktiver utløser alle på tabellnavn;

For å aktivere databaseutløser, bruk spørringen:

Aktiver trigger drop_ddl_trigger ved salg;

Her representerer drop_ddl_trigger triggernavnet og salget representerer databasen.

For å aktivere alle databaseutløsere, bruk spørringen:

Aktiver utløser alt på salg;

For å deaktivere en tabell eller en databaseutløser, må du bytte ut nøkkelordet med deaktiver ☺.

Slett trigger

For å fjerne en trigger, kan du bruke drop -setningen som vist:

Slipp trigger hvis eksisterer triggernavn;

SQL Server viser alle utløsere

For å se alle utløserne i en SQL Server -forekomst, bruk spørringen som vist:

Velg navn, type_desc, is_disabled, is_instead_of_trigger fra sys.utløser hvor type = 'tr'

Spørringen skal returnere alle utløserne i SQL Server -forekomsten som:

SQL Server -påloggingsutløsere

Inntredelsesutløsere er type triggere som kjøres når en påloggingsaktivitet oppstår på serveren. Disse typer triggere kjøres etter vellykket autentisering, men før du oppretter en brukerøkt. Siden de brukes til å håndtere påloggingsaktivitet, oppretter vi dem på servernivå, som vist i eksemplet nedenfor:

FORSIKTIGHET: Utløseren nedenfor kan forhindre fremtidige pålogginger på serveren. Sørg for å slette før innlogging.

FORSIKTIGHET - ☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️.

-- Lag påloggingsutløser
Lag trigger LOGIN_TG
På alle serveren
For pålogging som
BEGYNNE
Velg 'en trigger etter innlogging' som [melding]
SLUTT

Utløseren vil vise en melding når brukeren logger inn på serveren.

Konklusjon

I denne guiden forsto du forskjellige typer triggere, hvordan du oppretter, aktiverer, deaktiverer, sletter og ser utløsere i SQL Server.