I SQL Server er triggere definert som databaseobjekter. Rent teknisk sett er de spesifikke typer funksjonsanrop som reagerer på bestemte operasjoner i databasen.
Denne dyptgående guiden gir deg en omfattende forståelse av SQL-triggere, som kan være svært nyttige i ditt arbeid. La oss begynne!
Hva er SQL-triggere?
Begrepet «trigger» beskriver en mekanisme som automatisk utfører en spørring når data i databasen endres.
En trigger er en gruppe med spesifikt navngitte SQL-spørringer lagret i minnet. Det er en type funksjonsanrop som aktiveres umiddelbart ved en bestemt hendelse i databasen. Hver trigger er knyttet til en bestemt tabell.
For eksempel kan en trigger aktiveres når en ny kolonne legges til i en tabell, eller når spesifikke poster blir endret.
Ifølge Microsoft Developer Network, er triggere en særskilt form for lagrede prosedyrer. I en triggerdefinisjon angir vi først når triggeren skal aktiveres, og deretter definerer vi handlingen som skal utføres når triggeren utløses.
Syntaks:
CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tableName FOR EACH ROW SET operation [trigger_body];
Forklaring av hver parameter:
CREATE TRIGGER trigger_name– Brukes for å opprette en ny trigger eller endre navnet på en eksisterende.BEFORE/AFTER– Denne angir tidspunktet for utførelse, om det skal skje før eller etter en bestemt hendelse.INSERT/UPDATE/DELETE– Dette definerer handlingen vi ønsker å overvåke i tabellene.ON tableName– Her spesifiseres tabellen triggeren skal knyttes til.FOR EACH ROW– Denne setningen indikerer at triggeren skal utføres for hver enkelt endring i en rad.trigger_body– Definerer handlingen som skal utføres når triggeren aktiveres.
Triggere er lagrede funksjoner med unike identiteter som gir oss mulighet til å gjenbruke spørringer som allerede er utført og lagret sikkert i minnet. La oss nå undersøke hvorfor SQL benytter seg av triggere.
Triggere benyttes for det meste for å kontrollere utførelsen av kode når en spesifikk hendelse inntreffer. Anvendelse av triggere er et ideelt valg når man trenger at en bestemt kode skal kjøres konsekvent som et svar på en definert hendelse.
Nedenfor er noen fordeler ved å bruke triggere i SQL-databaseoperasjoner:
Referanseintegritet er en sentral egenskap i relasjonsdatabaser. Dette innebærer at dataene i databasesystemet alltid må være korrekte for hver transaksjon og operasjon.
Dersom to tabeller er plassert i separate databaser eller systemer, finnes det ingen garanti for datavalidering ved bruk av begrensninger. I slike tilfeller er triggere det eneste alternativet for å sikre dette.
Kombinasjon av triggerargumenter
For hver tabell kan vi definere seks forskjellige triggertyper. Disse er kombinasjoner av triggerargumenter som inngår i SQL-triggere på radnivå.
BEFORE INSERT: Disse triggerne utfører en handling på radene før en INSERT-operasjon utføres i tabellen eller databasen.
AFTER INSERT: Utfører handlingen på rader umiddelbart etter en datainnsetting (INSERT).
BEFORE UPDATE: Med disse triggerne utføres en funksjon på radene før en UPDATE-operasjon utføres.
AFTER UPDATE: Utfører handlingen på rader rett etter en UPDATE-operasjon i databasen eller tabellen.
BEFORE DELETE: Utfører en operasjon på rader før en DELETE-operasjon utføres i databasen eller tabellen.
AFTER DELETE: Disse triggerne utfører handlingen på radene etter en DELETE-transaksjon.
Typer SQL-triggere
SQL-triggere er lagrede funksjoner som kjøres automatisk når visse hendelser skjer. De fungerer som en form for hendelsesdrevet planlegging. De følgende situasjonene kan aktivere triggere:
DML Triggers – DML står for Data Manipulation Language. DML-triggere muliggjør utførelse av kode som respons på datamodifisering. Denne triggeren aktiveres ved utførelse av DML-kommandoer som INSERT, UPDATE og DELETE. De kalles også «Table Level Triggers».
DDL Triggers – DDL står for Data Definition Language. DDL-triggere gjør det mulig å kjøre kode som respons på endringer i databaseskjemaer, som å legge til eller slette tabeller, eller serverhendelser som når en bruker logger inn. Disse kalles «Database Level Triggers».
Disse triggerne kan aktiveres når visse DDL-setninger som CREATE, ALTER eller DROP utføres i den aktive databasen. De kan også brukes til å overvåke og administrere aktiviteter.
LOGON-triggere – Når en LOGON-hendelse (oppstart, pålogging, utlogging, avslutning) inntreffer, aktiveres LOGON-triggere umiddelbart. De utføres først etter en brukerautentiseringsprosess, og før selve brukertransaksjonen starter. LOGON-triggerne vil ikke utløses dersom autorisasjonen mislykkes.
Disse triggerne kan brukes til å loggføre påloggingshistorikk eller etablere hendelsesbegrensninger for en bestemt pålogging, blant andre funksjoner for revisjon og identitetsadministrasjon.
CLR Triggers – CLR står for Common Language Runtime. CLR-triggere er en spesiell type triggere bygget på CLR innenfor .NET-teknologien. Disse triggerne er nyttige når triggeren trenger å utføre mange beregninger eller samhandle med andre enheter enn SQL.
DML- og DDL-triggere kan konstrueres ved å aktivere CLR-triggere i .NET-teknologier, inkludert Visual Basic, C# og F-sharp.
Eksempel på SQL Server Trigger
La oss forstå disse triggerkonseptene med et praktisk eksempel.
Først oppretter vi en database ved hjelp av SQL-setninger.
CREATE DATABASE testdb; use testdb;
Her har vi navngitt databasen «testdb». Neste steg er å opprette en tabell.
CREATE TABLE student(
name varchar(25),
id int(2),
maths int(2),
physics int(2),
biology int(2),
social int(2),
total int(2)
);
Vi har laget en tabell for å lagre elevdetaljer. Under er kommandoen for å beskrive strukturen til tabellen. Tabellnavnet er «student».
DESC student;
Nedenfor er strukturen til tabellen vi har opprettet.
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(25) | YES | | NULL | | | id | int | YES | | NULL | | | maths | int | YES | | NULL | | | physics | int | YES | | NULL | | | biology | int | YES | | NULL | | | social | int | YES | | NULL | | | total | int | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)
Etter å ha opprettet en tabell, er neste steg å definere en trigger. La oss prøve å bruke BEFORE INSERT-argumentet.
Navnet på triggeren er «merker». Så snart tabellen endres med elevens karakterer, vil triggeren automatisk beregne elevens totale karakter.
CREATE TRIGGER marks BEFORE INSERT ON student FOR EACH ROW set new.total=new.maths+new.physics+new.biology+new.social;
Siden vi må erstatte raddata i stedet for å arbeide med de gamle, har vi definert «total» ved å bruke et nytt klassenavn, og alle påfølgende uttrykk er prefikset med nye nøkkelord etter total ved bruk av punktoperatoren. Nå legger vi til verdier for hver rad og ser resultatene. I utgangspunktet er totalkarakteren 0 for hver student.
INSERT INTO student VALUES("George",02,99,87,92,91,0);
INSERT INTO student VALUES("James",03,91,81,94,90,0);
INSERT INTO student VALUES("Harry",04,86,70,73,88,0);
INSERT INTO student VALUES("John",05,73,89,78,92,0);
INSERT INTO student VALUES("Lisa",01,94,75,69,79,0);
Triggersetningen aktiveres automatisk når data legges inn i elevtabellen. Triggeren vil beregne total karakter for hver elev. La oss se om triggeren utløses ved hjelp av en SELECT-setning.
SELECT * FROM table_name;
Og her er det endelige resultatet:
mysql> select * from student; +--------+------+-------+---------+---------+--------+-------+ | name | id | maths | physics | biology | social | total | +--------+------+-------+---------+---------+--------+-------+ | George | 2 | 91 | 81 | 94 | 90 | 356 | | James | 3 | 86 | 70 | 73 | 88 | 317 | | Harry | 4 | 73 | 89 | 78 | 92 | 332 | | John | 5 | 94 | 75 | 69 | 79 | 317 | | Lisa | 1 | 99 | 87 | 92 | 91 | 369 | +--------+------+-------+---------+---------+--------+-------+ 5 rows in set (0.00 sec)
I resultatet over ser vi at alle fagkarakterer legges sammen automatisk for hver elev. Det betyr at triggeren har fungert som den skulle.
Ytterligere triggeroperasjoner
Vi kan utføre mange operasjoner ved hjelp av triggere. Noen kan være enkle, mens andre kan være mer komplekse. Ved å se på spørringene er det lett å forstå. Med Transact-SQL-setninger kan du aktivere, deaktivere eller slette triggere ved hjelp av følgende kommandoer.
Spørring for å sjekke om en spesifikk trigger eksisterer
Denne kommandoen søker etter den angitte triggeren i hele databasen.
SELECT * FROM [sys].[triggers] WHERE [name] = 'Trigger_name'
Spørring for å vise triggere
Alle triggere som er tilgjengelige i den aktive databasen vil vises ved hjelp av følgende setning.
SHOW TRIGGERS;
Spørring for å deaktivere trigger
Kommandoen nedenfor deaktiverer triggeren i den aktuelle databasen.
DISABLE TRIGGER trigger_name ON DATABASE;
Du kan også spesifisere et bestemt tabellnavn for å deaktivere en trigger.
DISABLE TRIGGER trigger_name ON table_name;
Spørring for å aktivere trigger
Følgende kommando deaktiverer først en bestemt trigger definert på en gitt tabell i den aktive databasen før den aktiveres på nytt.
ALTER TABLE table_name DISABLE TRIGGER trigger_name ALTER TABLE table_name ENABLE TRIGGER trigger_name
Triggeren må deaktiveres før den kan aktiveres.
Spørring for å aktivere eller deaktivere alle triggere i en tabell
Ved å bruke SQL-setningen ovenfor kan vi deaktivere eller aktivere alle tabelltriggere samtidig ved å erstatte «ALL» i stedet for et spesifikt triggernavn.
ALTER TABLE table_name DISABLE TRIGGER ALL ALTER TABLE table_name ENABLE TRIGGER ALL
Spørring for å slette eller fjerne en trigger
En trigger kan fjernes ved å slette den eller hele tabellen. Alle relaterte triggere blir også slettet når en tabell slettes.
DROP TRIGGER [trigger_name];
Når en trigger slettes, fjernes de tilhørende dataene fra sys.objects-datatabellen.
Fordeler med triggere
- Det er enkelt å bygge triggere, og en trigger kan kalle lagrede funksjoner og metoder.
- Brukere kan implementere enkel revisjon ved hjelp av triggere.
- Dessverre kan du ikke opprette begrensninger på tvers av enheter i databasesystemer med SQL Server. Du kan imidlertid simulere effekten av begrensninger ved hjelp av triggere.
- Integritetsbegrensninger kan implementeres på tvers av databaser ved bruk av triggere.
- Triggere kan være nyttige når det er behov for gruppevalidering i stedet for rad-for-rad-verifisering av nylig innsatte eller endrede data.
Ulemper med triggere
SQL-triggere er ikke alltid det beste valget på grunn av visse begrensninger.
- Triggere må dokumenteres nøye.
- Det kan være vanskelig å feilsøke triggere på grunn av samtidig databasekjøring, som kanskje ikke er tilgjengelig for applikasjonskomponenter.
- DML-setninger kan bli mer komplekse når triggere brukes.
- Selv et lite problem med en trigger kan føre til logiske feil i uttalelsen.
Konklusjon
Triggere er svært nyttige komponenter i Transact-SQL og SQL, og de kan også brukes i Oracle. Bruk av triggere er viktig ved kall av lagrede metoder. Disse SQL-triggerne lar oss analysere aktivitetsloggene og avgjøre hvordan vi skal reagere på dem ved behov. Vi kan også søke etter spesifikke tabeller som er knyttet til en trigger for å hente data.
Rekursjon kan aktiveres av triggere. Når en trigger på en tabell utfører en kommando på den overordnede tabellen, vil den andre iterasjonen av triggeren aktiveres, noe som er kjent som en rekursiv trigger. Dette er nyttig når du prøver å løse identitetskorrelasjoner.
I tillegg kontrollerer triggere oppdateringsmønsteret databasen tillater. Det er svært gunstig å opprettholde begrensninger for dataintegritet i databasesystemet dersom SQL-begrensningsnøkler ikke eksisterer, særlig primærnøkkelen og fremmednøkkelen.
Jeg håper denne artikkelen har vært nyttig for å lære mer om SQL-triggere.
Hvis du ønsker å lære mer om databaser, finnes det gode ressurser for å lære SQL og NoSQL.