Declanșatorii sunt entități de bază de date în SQL Server. Din punct de vedere tehnic, ele sunt o anumită clasă de apeluri de funcții care răspunde la operațiuni specifice ale bazei de date.
Acest ghid esențial vă va oferi informații aprofundate despre declanșatoarele SQL care pot fi destul de utile în profesia dvs. Să începem!
Cuprins
Ce sunt declanșatorii SQL?
Cuvântul „declanșator” descrie o declarație conform căreia un server execută automat interogarea de fiecare dată când conținutul din baza de date este modificat.
Un declanșator este un grup de interogări SQL special numite care sunt stocate în spațiul de memorie. Este un tip specific de apel de funcție care este invocat imediat ori de câte ori are loc orice eveniment de bază de date. Fiecare declanșator are un tabel alocat.
De exemplu, un declanșator ar putea fi activat ori de câte ori o nouă coloană este adăugată la un anumit tabel sau dacă anumite înregistrări sunt modificate.
Potrivit Microsoft Developer Network, declanșatorii sunt o anumită clasă de proceduri stocate. Într-o instrucțiune de declanșare, definim inițial când declanșatorul ar trebui să fie rulat și apoi furnizăm acțiunea care ar trebui întreprinsă după activarea declanșatorului.
Sintaxă:
CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tableName FOR EACH ROW SET operation [trigger_body];
Explicația fiecărui parametru
- CREATE TRIGGER trigger_name – Este folosit pentru a construi un declanșator sau pentru a schimba numele unui declanșator existent.
- ÎNAINTE/DUPĂ – Această interogare este utilizată pentru a defini timpul de execuție a declanșatorului (înainte sau după un anumit eveniment).
- INSERT/UPDATE/DELETE – Acesta descrie acțiunea pe care dorim să o întreprindem asupra meselor.
- ON tableName – Aici definim numele tabelului pentru a configura un declanșator.
- PENTRU FIECARE RÂND – Această instrucțiune se referă la declanșatorul rândului, ceea ce înseamnă că declanșatoarele vor fi executate ori de câte ori un rând este modificat.
- trigger_body – Specifică acțiunea care trebuie întreprinsă atunci când declanșatorul este activat.
Declanșatoarele sunt funcții stocate cu identități distincte care ne permit să reutilizam interogări care au fost deja executate și stocate în siguranță în memorie. Acum să încercăm să înțelegem de ce SQL are nevoie de ele.
Declanșatoarele sunt utilizate în principal pentru a reglementa implementarea codului ori de câte ori are loc un eveniment. Cu alte cuvinte, utilizarea declanșatorilor este alegerea ideală dacă aveți nevoie de un anumit fragment de cod care să fie rulat în mod constant ca răspuns la un eveniment specificat.
Următoarele sunt câteva beneficii ale utilizării declanșatorilor în operațiunile bazei de date SQL.
Integritatea referenţială este o proprietate cheie de bază a sistemelor de baze de date relaţionale. Aceasta înseamnă că datele păstrate în sistemul de baze de date trebuie să fie întotdeauna exacte pentru fiecare tranzacție și fiecare operațiune.
Dacă două tabele sunt situate pe baze de date sau sisteme separate, nu există o modalitate de a asigura validarea datelor în interiorul lor folosind valori de constrângere. Într-o astfel de situație, declanșatoarele sunt singura opțiune de execuție.
Combinație de argumente de declanșare
Pentru fiecare tabel, putem specifica șase tipuri diferite de declanșare. Acestea sunt combinația de argumente de declanșare incluse în declanșatoarele la nivel de rând SQL.
BEFORE INSERT: Acești declanșatori execută acțiunea pe rânduri înainte de a efectua orice operațiuni de INSERT în tabelul specificat sau în baza de date.
AFTER INSERT: Execută acțiunea pe rânduri imediat după orice activitate de INSERT în baza de date.
ÎNAINTE DE ACTUALIZARE: Cu aceste declanșatoare, o funcție pe rânduri este efectuată înainte ca o acțiune de ACTUALIZARE să fie efectuată pe baza de date.
DUPĂ ACTUALIZARE: execută acțiunea pe rânduri imediat după orice activitate de UPDATE de bază de date sau tabel specific.
BEFORE DELETE: Execută o anumită operație pe rânduri chiar înainte ca baza de date sau tabel să fie supusă unei acțiuni DELETE.
DUPĂ ȘTERGERE: Acești declanșatori execută acțiunea pe rândurile care urmează fiecărei tranzacții DELETE.
Tipuri de declanșatori SQL
Declanșatoarele SQL sunt funcții stocate care rulează imediat când apar anumite evenimente. Seamănă cu programarea bazată pe evenimente. Situațiile ulterioare pot începe execuția declanșatoarelor.
Declanșatoare DML – DML reprezintă limbajul de manipulare a datelor. Execuția codului ca reacție la modificarea datelor este posibilă folosind declanșatoarele DML. Acest declanșator este activat atunci când sunt executate comenzi DML precum INSERT, UPDATE și DELETE. Acestea sunt denumite și „Table Level Triggers”.
Declanșatoare DDL – DDL reprezintă limbajul de definire a datelor. Declanșatoarele DDL ne permit să rulăm cod ca reacție la modificările schemei bazei de date, cum ar fi adăugarea sau ștergerea tabelelor sau evenimentele de server, cum ar fi atunci când un utilizator se înregistrează. Acestea se numesc „Declanșatoare la nivel de bază de date”.
Aceste declanșatoare pot fi activate atunci când anumite instrucțiuni DDL precum CREATE, ALTER sau DROP sunt executate în baza de date activă. Acestea pot fi, de asemenea, utilizate pentru a urmări și gestiona activitățile care sunt executate.
Declanșatoare de LOGON – Ori de câte ori are loc orice eveniment LOGON (pornire, autentificare, deconectare, oprire), declanșatoarele de conectare sunt invocate imediat. Acestea sunt efectuate numai în urma unui proces de autentificare a utilizatorului chiar înainte de inițierea tranzacției utilizatorului. Declanșatoarele LOGON nu vor fi declanșate dacă autorizarea eșuează.
Acești declanșatori pot fi utilizați pentru a înregistra istoricul de conectare sau pentru a stabili o restricție de eveniment pentru o anumită autentificare, printre alte funcții de auditare și gestionare a identității pentru conexiunile la server.
CLR Triggers – CLR înseamnă Common Language Runtime. Declanșatoarele CLR sunt într-adevăr un subset unic de declanșatori construite în principal pe CLR în cadrul tehnologiei .NET. Aceste declanșatoare sunt utile dacă declanșatorul trebuie să efectueze o mulțime de calcule sau trebuie să se raporteze la o altă entitate decât SQL.
Declanșatoarele DML și DDL pot fi într-adevăr construite prin activarea codării declanșatorilor CLR acceptați în tehnologiile .NET, inclusiv Visual Basic, C# și F-sharp.
Exemplu de declanșare SQL Server
Să înțelegem aceste concepte declanșatoare cu un exemplu.
Mai întâi, să creăm o bază de date folosind instrucțiuni SQL.
CREATE DATABASE testdb; use testdb;
Aici, am dat un „testdb” ca nume al bazei de date. Iar următorul pas este crearea unui tabel.
CREATE TABLE student( name varchar(25), id int(2), maths int(2), physics int(2), biology int(2), social int(2), total int(2) );
Am creat un tabel pentru stocarea detaliilor studentului. Și aici este comanda pentru a descrie structura tabelului. Aici „student” este numele tabelului pe care l-am dat.
DESC student;
Mai jos este structura tabelului pe care l-am creat.
+---------+-------------+------+-----+---------+-------+ | 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)
După crearea unui tabel, următorul pas este configurarea unui declanșator. Să încercăm să folosim argumentul BEFORE INSERT.
Numele declanșatorului pe care l-am creat este „marke”. De îndată ce tabelul este modificat cu notele elevului, declanșatorul de mai jos încearcă să determine automat nota generală a elevului.
CREATE TRIGGER marks BEFORE INSERT ON student FOR EACH ROW set new.total=new.maths+new.physics+new.biology+new.social;
Deoarece ni se cere să înlocuim datele de rând în loc să lucrăm cu cele vechi, am definit „total” folosind un nou nume de clasă, iar toate expresiile ulterioare sunt prefixate cu cuvinte cheie noi după total folosind operatorul punct. Acum, vom adăuga valori la fiecare rând și vom vedea rezultatele. Inițial, nota totală este 0 pentru fiecare elev.
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);
Declarația de declanșare va fi declanșată automat atunci când datele sunt inserate în tabelul student în acest caz. Notele totale ale fiecărui elev vor fi calculate de declanșator. Acum, să vedem dacă declanșatorul este invocat sau nu folosind o instrucțiune SELECT.
SELECT * FROM table_name;
Și aici este rezultatul final.
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)
În rezultatul de mai sus, puteți vedea că toate notele de materii sunt adăugate automat pentru fiecare student. Deci, putem concluziona că declanșatorul a fost invocat cu succes.
Operații suplimentare de declanșare
Putem efectua multe operații folosind declanșatoare. Unele pot fi simple și altele puțin complexe, dar odată ce parcurgem interogările, este ușor de înțeles. Utilizând instrucțiuni Transact-SQL, puteți activa, dezactiva sau șterge declanșatoarele folosind următoarele comenzi.
Interogare pentru a verifica dacă există sau nu un anumit Trigger
Această comandă verifică declanșatorul specificat în întreaga bază de date.
SELECT * FROM [sys].[triggers] WHERE [name] = 'Trigger_name'
Interogare pentru afișarea declanșatorilor
Toate declanșatoarele care sunt disponibile în baza de date activă vor fi afișate prin următoarea instrucțiune.
SHOW TRIGGERS;
Interogare pentru a dezactiva declanșatorul
Comanda de mai jos dezactivează declanșatorul din baza de date de lucru.
DISABLE TRIGGER trigger_name ON DATABASE;
De asemenea, puteți specifica un anumit nume de tabel pentru a dezactiva un declanșator.
DISABLE TRIGGER trigger_name ON table_name;
Interogare pentru a activa declanșatorul
Următoarea comandă dezactivează mai întâi un declanșator specific care a fost definit pe tabelul specificat în baza de date activă înainte de a-l reactiva.
ALTER TABLE table_name DISABLE TRIGGER trigger_name ALTER TABLE table_name ENABLE TRIGGER trigger_name
Declanșatorul trebuie să fie dezactivat, înainte de a încerca să îl activați,
Interogare pentru a activa sau a dezactiva toate declanșatoarele dintr-un tabel
Folosind instrucțiunea SQL de mai sus, putem dezactiva sau activa toate declanșatoarele de tabel la un moment dat, înlocuind „ALL” în locul unui nume de declanșator specific.
ALTER TABLE table_name DISABLE TRIGGER ALL ALTER TABLE table_name ENABLE TRIGGER ALL
Interogare de ștergere sau de eliminare a declanșatorului
Un declanșator poate fi eliminat prin ștergerea lui sau a întregului tabel. Fiecare declanșator asociat este, de asemenea, șters atunci când un tabel este șters.
DROP TRIGGER [trigger_name];
Ori de câte ori un declanșator este șters, datele aferente sunt eliminate din tabelul de date sys.objects.
Avantajele declanșatorilor
- Este simplu să construiți declanșatoare, iar declanșatorul în sine poate invoca funcții și metode stocate.
- Utilizatorii pot implementa un audit simplu folosind declanșatoare.
- În mod tragic, nu puteți crea constrângeri între entitățile din sistemele de baze de date cu SQL Server, deși puteți emula funcționarea constrângerilor utilizând declanșatoare.
- Constrângerile de integritate pot fi implementate în bazele de date folosind declanșatoare.
- Când este necesară validarea grupului, mai degrabă decât verificarea rând cu rând a datelor nou introduse sau modificate, declanșatorii pot fi de ajutor.
Dezavantajele declanșatorilor
Declanșatoarele SQL pot să nu fie cea mai bună alegere în unele situații din cauza limitărilor lor.
- Declanșatorii trebuie să fie documentați cu acuratețe.
- Datorită execuției simultane a bazei de date care ar putea să nu fie accesibilă componentelor aplicației, declanșatorii pot fi dificil de depanat.
- Declarațiile DML devin mai complexe atunci când sunt utilizați declanșatorii.
- Chiar și o problemă minoră de declanșare are potențialul de a duce la erori logice în declarație.
Concluzie
Declanșatoarele sunt componente foarte utile ale Transact-SQL și SQL și le puteți folosi și în Oracle. Utilizarea declanșatorilor este crucială atunci când apelați metodele stocate. Aceste declanșatoare SQL ne permit să analizăm cronologia activității și să stabilim cum să răspundem la acestea dacă este necesar. De asemenea, putem verifica un anumit tabel care este conectat la un declanșator pentru a achiziționa date.
Recursiunea poate fi activată prin declanșatoare. Ori de câte ori un declanșator de pe o tabelă execută o comandă pe tabelul părinte, a doua iterație a declanșatorului este declanșată, iar aceasta este cunoscută ca declanșator recursiv. Acest lucru ajută în încercarea de a rezolva o corelație de identitate.
În plus, declanșatoarele reglementează modelul de actualizare pe care baza de date este permisă să îl accepte. Este foarte benefic să păstrați constrângerile de integritate a datelor în sistemul de baze de date dacă cheile de constrângere SQL nu există, în principal cheia primară și cheia externă.
Sper că ați găsit acest articol util pentru a afla despre declanșatoarele SQL.
Dacă doriți să învățați bazele de date în profunzime, iată câteva resurse excelente pentru a învăța SQL și NoSQL.