SQL

Dezvoltare site

Există deja multe articole pe Internet despre declanșatorii SQL, dar voi adăuga încă unul cu exemple adecvate pentru a consolida materialul pentru cei care sunt „în cunoștință” și pentru a înțelege mai bine materialul pentru cei care tocmai au început să înțeleagă „zen de sql”. În același timp, voi crea o discuție pe această temă.

Permiteți-mi să spun imediat că părerea mea este doar părerea mea și, uneori, este foarte categoric. Din mai multe motive, trebuie să lucrați cu site-uri foarte încărcate și aplicații web complexe.

O experiență valoroasă învățată lucrând la ele a fost aceea de a urmări prioritățile și statisticile. Ce înseamnă? Este simplu: dacă ai un blog și are 2-3-4-10012 milioane de vizitatori pe zi, iar articolele sunt scrise doar de 1-2-3-3435 ori pe zi (un ordin de mărime mai mic decât numărul de vizualizări) , atunci viteza de salvare a articolului (și complexitatea acestuia) în raport cu viteza de afișare a articolului poate fi proporțional mai mică. Cu cât arătăm mai mult, cu atât mai critic este afișarea, și nu salvarea articolului/paginii/tabelului. Ceea ce nu înseamnă că te poți relaxa. Salvarea unui articol în 3-5-10 secunde pe un blog este în limitele adecvării, dar generarea unei pagini în mai mult de 2 secunde (+ în timp ce se încarcă scripturile și stilurile cu imagini) este în pragul „ce site lent. , voi citi altceva” , și chiar mai rău, „Mă duc să-l cumpăr în altă parte”.

Dacă luăm un site web mediu cu vot/karmă, comentarii, contor de afișări de pagină etc., atunci mulți dezvoltatori vin imediat în minte cu construcții precum SELECT count(*) FROM comment WHERE comment.page=page_id. Ei bine, gândiți-vă doar la calcularea numărului de evaluări și a numărului de comentarii pentru fiecare articol. Oh, avem 10 articole din fiecare secțiune de pe pagina principală. Cu un trafic de 10 persoane pe secundă, la un VPS mediu, îți poți permite 60-100 de interogări SQL pe pagină (bună ziua, Bitrix).

Dar la naiba cu versurile (probabil că m-am săturat deja). Date goale:

tabel blog

CREATE TABLE IF NU EXISTIS `blog` (`id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(128) NOT NULL, `text` text NOT NULL, `creation` datetime NOT NULL, `modification` datetime NOT NULL , `img` varchar(128) NOT NULL DEFAULT "default.png", `status` tinyint(4) NOT NULL DEFAULT "2", `user_id` int(11) NU NULL, `rate` int(11) NU NULL , `relax_type` tinyint(4) NOT NULL, `timers` timestamp NU NULL DEFAULT CURRENT_TIMESTAMP, `contest` tinyint(1) NU NULL DEFAULT "0", `views` int(11) NU NULL DEFAULT "0", `comment ` int(11) NOT NULL, `url` varchar(128) NOT NULL, CHEIE PRIMARĂ (`id`), CHEIE UNIQUE `url` (`url`), KEY `country_id` (`country_id`), KEY `user_id ` (`user_id`), KEY `status` (`starea`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1456435 ;

Tabel de comentarii

CREATE TABLE IF NU EXIST `comments` (`owner_name` varchar(50) NOT NULL, `owner_id` int(12) NOT NULL, `id` int(12) NOT NULL AUTO_INCREMENT, `parent_id` int(12) DEFAULT NULL, `user_id` int(12) DEFAULT NULL, `text` text, `creation` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `status` int(1) NOT NULL DEFAULT "0", CHEIA PRIMARĂ (`id`), KEY `owner_name` ( `owner_name`,`owner_id`), KEY `parent_id` (`parent_id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=243254252 ;

După cum puteți vedea, în tabelul blogului, fiecare articol are un contor de comentarii (câmp de comentarii).
Practică simplă:
1. A adăugat un comentariu - a crescut contorul pentru blog
2. A șters/ascuns comentariul - a scăzut contorul.
A face acest lucru în cod este convenabil și familiar, dar există un instrument mai convenabil - declanșatoarele.

Și așa, avem 2 evenimente (de fapt 3): crearea unui comentariu și ștergerea acestuia (al treilea eveniment este o schimbare a statutului său („ștergere”, interdicție etc.).
Să luăm în considerare doar crearea și ștergerea și lăsăm ca schimbarea stării să fie tema ta.

Există o caracteristică în exemplu: comentariile pot fi pentru mai multe tipuri de articole.

Crearea unui comentariu:

CREATE TRIGGER `add_count_comment` DUPĂ INSERT PE `comentarii` PENTRU FIECARE RÂND ÎNCEPE // pentru utilizatorul din cont personal să numărăm câte comentarii a scris UPDATE user SET user.countcomment= user.countcomment+1 WHERE user.id = NEW.user_id; // determină la ce se referă comentariul și mărește imediat contorul din aceste tabele CASE NEW.`owner_name` WHEN "Blog" THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`+1 WHERE `blog `.id = NOU.`owner_id` ; WHEN "Articol" THEN UPDATE `article` SET `article`.`comment` = `articol`.`comment`+1 WHERE `article`.`id` = NEW.`owner_id` ; WHEN "PopulatePlace" THEN UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`+1 WHERE `populate_place`.`id` = NEW.`owner_id` ; SFÂRȘIT CAZ; // aici ne este mai ușor să lucrăm cu fluxurile de știri // scriem imediat url-ul articolului, pentru ca APOI să nu fie nevoiți să facem selecții inutile CASE NEW.`owner_name` WHEN "Blog" THEN SET userurl = (SELECTAȚI adresa URL FROM `blog` WHERE `blog`. id= NEW.`owner_id`); WHEN "Articol" THEN SET userurl = (SELECTARE url FROM `article` WHERE article.id=NEW.`owner_id`); WHEN "PopulatePlace" THEN SET userurl = ``; SFÂRȘIT CAZ; // scrieți titlul articolului imediat pentru a nu face o selecție THEN CASE NEW.`owner_name` WHEN "Blog" THEN SET usertitle = (selectați titlul din `blog` unde blog.id=NEW.`owner_id`) ; WHEN "Articol" THEN SET usertitle = (selectați titlul din `articol` unde article.id=NEW.`owner_id`); CÂND „PopulatePlace” ATUNCI SETĂ usertitle = ` `; SFÂRȘIT CAZ; INSERT INTO user_has_events VALUES (NEW.user_id,NEW.id,"Comentarii", NOW(),userurl , usertitle); Sfârşit

Același lucru este valabil și pentru ștergerea unui comentariu:

CREATE TRIGGER `del_count_comment` DUPĂ ȘTERGERE PE `comentarii` PENTRU FIECARE RÂND ÎNCEPE UPDATE user SET user.countcomment= user.countcomment -1 WHERE user.id = OLD.user_id; CASE OLD.`owner_name` WHEN "Blog" THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`-1 WHERE `blog`.`id` = OLD.`owner_id` ; WHEN "Articol" THEN UPDATE `article` SET `article`.`comment` = `articol`.`comment`-1 WHERE `article`.`id` = OLD.`owner_id` ; WHEN "PopulatePlace" THEN UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`-1 WHERE `populate_place`.`id` = OLD.`owner_id` ; SFÂRȘIT CAZ; Sfârşit

Și așa am primit:
1. Când introduceți un comentariu, noi automat folosind SQL serverul a calculat cantitatea de comentarii pentru un anumit obiect de comentariu (articol, pagină, notă)
2. Am creat un flux de știri (bună ziua tuturor rețelelor sociale etc.)
3. Când ștergeți un comentariu, deducem toate datele.
4. Nu am folosit niciun instrument cadru.
5. Preluarea tuturor datelor necesare are loc rapid (doar 1 cerere la afișarea unei pagini, cu excepția altor date „stângate” de pe aceasta.)

Avem și sfinx, care face periodic selecții de articole care s-au schimbat în ultimul minut. În acest scop, blogul are un câmp de modificare.

Declanșator adăugat:

CREATE TRIGGER `ins_blog` ÎNAINTE DE INSERT ON `blog` // introduceți timpul până când stocarea informațiilor prin „înlocuirea” datelor. PENTRU FIECARE RÂND ÎNCEPE SET NEW.modification = NOW(); Sfârşit

Acum, făcând o selecție pentru ultimul minut, vom obține toate documentele care au fost adăugate în ultimul minut.

CREATE TRIGGER `ins_blog` ÎNAINTE DE ACTUALIZARE PE `blog` // introduceți ora înainte de a salva informațiile prin „înlocuirea” datelor. PENTRU FIECARE RÂND ÎNCEPE SET NEW.modification = NOW(); Sfârşit

Dacă datele se modifică, vom actualiza și indexul de căutare.

De obicei, într-un proiect mediu, tot ce poate fi transferat pe partea serverului sql este transferat. Serverul sql în sine efectuează astfel de operațiuni mai rapid și cu mai puține resurse decât se poate face prin limbajul de programare folosit.

UPD: Holivarul dedicat posibilității de a complica structura bazei de date este declarat deschis.

Adaptat după articolul lui Robert Marda de pe sqlservercentral.com: Auditing Through Triggers

În acest articol, Robert oferă exemple de cod pentru mai multe declanșatoare instalate pe tabele pentru a audita acțiunile utilizatorului pe înregistrările MS SQL Server 7.0/2000.

Pentru o explicație a modului în care funcționează declanșatoarele în general și a modului în care funcționează în SQL Server 7.0 și SQL Server 2000, puteți consulta următoarele articole scrise de Brian Kelley:

Primul articol explică scopul tabelelor speciale pentru tabelele inserate și șterse.
Următoarele exemple vor funcționa pe SQL Server 2000, dar au fost testate doar pe SQL Server 7.0.
Mai întâi trebuie să creăm tabelele necesare pentru lucrări ulterioare. Rulați scriptul de mai jos în Query Analyzer:

CREATE TABEL (
IDENTITATE (1, 1) NU NUL ,




NUL
(35) NUL
) PE
MERGE

CREATE TABEL (
NU NUL
(25) NUL
(25) NUL
(75) NUL
(50) NUL
NUL
(35) NUL
) PE
MERGE

Declanșator care urmărește operațiunile de ștergere

Dacă doriți să capturați ștergeri dintr-un tabel, puteți utiliza un exemplu de declanșare care va insera un rând în ComponentsDeleted ori de câte ori un rând este șters din tabel

Componente: CREATE TRIGGER șters de ON dbo.Components
PENTRU ȘTERGERE
LA FEL DE
INSERT INTO ComponentsDeleted(Iden, ComponentName, SerialNumber,
comentarii,
UserName, DeletedDate, DeletedBy)
SELECT Iden, ComponentName, SerialNumber, Comentarii, UserName, getdate(),
SYSTEM_USER
FROM șters

Eliminați unul sau două rânduri din tabelul Componente. Acum uitați-vă la tabelul ComponentsDeleted și veți vedea rândurile pe care le-ați șters acolo cu data și ora când au fost șterse.

Configurarea unui sistem de audit simplu folosind declanșatoare, dintre care exemple sunt prezentate în acest articol, poate fi utilă în cazurile în care trebuie să știți cine a efectuat acțiuni declanșate în baza de date și când.

1. Aflați tipurile de declanșatoare care pot fi create pe serverul MS SQL Server 2000.
2. Studiați operatorii pentru descrierea declanșatorilor de diferite tipuri și restricțiile impuse operatorilor validi în corpul declanșatorului.
3. Aflați cum să creați și să depanați declanșatoarele pe MS SQL Server 2000.
4. Dezvoltați cinci declanșatori pentru baza de date de formare „Biblioteca”, propusă de profesorul dumneavoastră din sarcinile descrise în lucrare.
5. Întocmește un raport cu privire la munca depusă în format electronic.

1. Creați un declanșator

Declanșatoare acestea sunt metode prin care un dezvoltator de aplicații pentru MS SQL Server poate asigura integritatea bazei de date. Acesta este un tip de procedură stocată care este invocată atunci când se încearcă modificarea datelor dintr-un tabel pentru care este definit un declanșator. SQL Server efectuează această procedură în timpul operațiunilor de inserare, actualizare și ștergere (INSERT, UPDATE, DELETE) pe un tabel dat. Deoarece declanșatorul este aplicat după finalizarea operației, acesta reprezintă ultimul cuvânt în modificare. Dacă un declanșator provoacă o eroare într-o interogare, SQL Server refuză să actualizeze informațiile și returnează un mesaj de eroare aplicației care efectuează acțiunea. Dacă este definit un declanșator pentru un tabel, acesta nu poate fi ocolit atunci când se efectuează operația corespunzătoare.

Deși un declanșator este un tip de procedură stocată, nu poate fi apelat direct: răspunde doar la evenimentele pentru care este definit.

Un nou tip de declanșator a apărut în MS SQL SERVER 2000 - ÎN LOC DE -trigger. Diferența sa fundamentală față de declanșatoarele obișnuite (DUPĂ) este că este executată nu după operația de inserare, modificare sau ștergere, ci în locul acesteia.

Cea mai comună utilizare a unui declanșator este menținerea integrității în bazele de date.

Declanșatorii au un impact minor asupra performanței serverului și sunt adesea utilizați pentru a îmbunătăți clauzele care efectuează operații în mai mulți pași pe tabele și rânduri.

Pentru a crea un declanșator, trebuie să fiți proprietarul tabelului pentru care este creat declanșatorul sau să fiți membru al rolului db_owner sau db_ddladmin, sau să fii administrator al unui server SQL, adică să fii membru al unui rol fix de server administratori de sistem. Când adăugați un declanșator la un tabel, acesta modifică tipul de acces, relația celorlalte obiecte cu acesta etc.

Crearea unui declanșator este similară cu declararea unei proceduri stocate și are următoarea sintaxă:

CREATE TRIGGER nume_declanșator
Pe masă
{
(PENTRU | DUPĂ | ÎN LOC DE) ( [,] [,] )

LA FEL DE
(DACĂ UPDATE(coloana_i)
[(ȘI | SAU) ACTUALIZARE (coloana_j)]
[...n]
| IF (COLUMNS_UPDATED() (operator_biți) mască_biți)
(operator_comparație) mască_biți_coloană [… n]
}
Instrucțiuni SQL [... n]
}
}

• trigger_name trebuie să urmeze convențiile standard de denumire a obiectelor SQL Server și să fie unic în baza de date;
• numele tabelului tabelului pentru care este creat declanșatorul;
• CU CRIPTARE Această opțiune oferă dezvoltatorilor posibilitatea de a împiedica utilizatorii să citească textul declanșator după ce acesta a fost încărcat pe server. Din nou, rețineți că, pentru a face textul declanșator cu adevărat irecuperabil, ar trebui să eliminați rândurile corespunzătoare din tabelul syscomments după criptare;
• FOR DELETE , INSERT , UPDATE cuvinte cheie care definesc operația de modificare a tabelului, la executarea căreia se va activa declanșatorul;
• CU APPEND această opțiune este necesară numai dacă nivelul stabilit compatibilitatea nu depășește 65 și este folosită pentru a crea declanșatoare suplimentare;
• NOT FOR REPLICATION indică faptul că declanșatorul nu este activat atunci când un tabel este modificat în timpul replicării;
• cuvânt cheie AS care specifică începutul definiției declanșatorului;
• Instrucțiuni SQL în declanșator T-SQL poate conține orice număr de instrucțiuni SQL atâta timp cât sunt incluse în BEGIN...END;
• IF UPDATE (coloană) pentru operațiunile de adăugare și actualizare a datelor, puteți defini condiții suplimentare pentru o anumită coloană de tabel; atunci când se specifică mai multe coloane, acestea sunt separate operatori logici;
• IF (COLUMNS_UPDATED()) Am arătat mai sus cum puteți utiliza constructul IF UPDATE (coloană) pentru a determina ce coloane sunt afectate de modificări. Dacă trebuie să verificați dacă o anumită coloană se schimbă, acest construct este foarte convenabil. Cu toate acestea, atunci când se construiește o stare complexă care include multe coloane, această construcție devine prea greoaie. Construcția IF (COLUMNS_UPDATED()) este destinată acestor cazuri. Rezultatul funcției COLUMNS_UPDATED() este un set de biți, fiecare dintre care corespunde unei coloane de tabel; Bitul cel mai puțin semnificativ corespunde primei coloană, bitul cel mai semnificativ corespunde ultimei. Dacă operația care a determinat declanșarea declanșatorului a încercat să schimbe o anumită coloană, atunci bitul corespunzător va fi setat la 1;
• bit_operator operator pe biți care definește operația de alocare a biților doriti obținuți folosind COLUMNS_UPDATED() . De obicei se folosește operatorul &;
• bit_mask în combinație cu operatorul bitmask, bitmask vă permite să evidențiați biții de interes pentru dezvoltator, adică să determinați dacă coloanele de interes au fost modificate în operația care a determinat declanșarea declanșatorului;
• operator_comparaţieȘi column_bitmask Funcția COLUMNS_UPDATED() oferă setul de biți corespunzător coloanelor care sunt actualizate. Folosind o mască de biți și un operator pe biți, se realizează o transformare pe acest set de biți și se obține un rezultat intermediar. Operatorul de comparare compară acest rezultat intermediar cu masca de biți a coloanei. Dacă rezultatul comparației este adevărat, atunci setul de instrucțiuni SQL care alcătuiesc corpul declanșatorului va fi executat, altfel nu.

Fie ca tabelul să aibă următoarea structură:

CREATE table mytable (a int, b int, c int, d int, e int)

Cinci coloane corespund la cinci biți, dintre care cel mai puțin semnificativ corespunde coloanei a, cel mai semnificativ corespunde coloanei e. Lăsați operația care declanșează declanșatorul să modifice coloanele a, b și e. Apoi funcția columns_updated va da valoarea 10011. Să nu ne intereseze să schimbăm coloanele b și d, dar ne interesează să schimbăm toate celelalte coloane (a, c și e), adică masca va fi 10101. Reamintim că la momentul scrierii declanșatorului nu știm ce coloane vor fi afectate de operația de modificare sau de inserare, adică ce rezultat va da funcția columns_updated. Prin specificarea operatorului de comparare pe biți în timpul execuției, obținem 10011 și 10101, care rezultă în 10001, care în notație zecimală este 17. Compararea acestei valori folosind operatorul de comparare și masca de biți a coloanei ne va spune dacă operația de modificare/inserare satisface cerințele necesare. conditii. Deci, de exemplu, dacă logica de afaceri cere ca declanșatorul să se declanșeze atunci când toate coloanele de care suntem interesați se schimbă (a, c, e), atunci, firește, parametrii bitmask și column_bitmask trebuie să aibă aceleași valori, iar operatorul de comparație trebuie să fie un semn egal. Astfel, pentru exemplul nostru, întreaga structură va arăta astfel:

IF(coloane_actualizate și 17) = 17

În cazul în care se impune modificarea a cel puțin una dintre coloanele care ne interesează, construcția va fi după cum urmează:

IF (columns_updated & 17) > 0

Folosind operațiuni cu biți, puteți obține o mare flexibilitate în compunerea unor astfel de construcții.

De asemenea, puteți crea declanșatoare folosind SQL Server Enterprise Manager.

1. Porniți SQL Server Enterprise Manager.
2. Faceți clic dreapta pe tabelul pentru care doriți să creați un declanșator și selectați Task > Manage Triggers din meniul contextual. Ca urmare a acestor acțiuni, va apărea o casetă de dialog în care puteți introduce textul de declanșare și îi puteți atribui un nume.
3. După ce ați finalizat introducerea, puteți verifica sintaxa și faceți clic pe OK pentru a salva declanșatorul în baza de date.

Limitări la crearea declanșatorilor

• Instrucțiunea CREATE TRIGGER poate fi folosită doar pe un singur tabel.
• Un declanșator poate fi creat numai în baza de date curentă, dar poate face referire la obiecte externe.
• Într-o declarație de creare a declanșatorului, puteți specifica mai multe acțiuni la care va reacționa.
• Nu poate fi folosit în textul declanșator urmând instrucțiunile: ALTERARE BAZĂ DE DATE, ALTER PROCEDURA, ALTER TABLE, CREATE DEFAULT, CREATE PROCEDURE, ALTER TRIGGER, ALTER VIEW, CREATE DATABASE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, CREATE VIEW, DISK INIT, DISK RESIZE, DROP DATABASE, DROP DATABASE PROCEDURĂ DE CĂDERARE, REGULĂ DE CĂDERARE, DECLICARE DE CĂDERARE, VEDERE DE CĂDERARE, RESTAURARE BAZĂ DE DATE, RESTAURARE Jurnal, RECONFIGURARE, ACTUALIZARE STATISTICI.
• Orice operație SET validă funcționează numai când declanșatorul este activ.
• Nu puteți executa un declanșator care examinează starea unui obiect binar mare (BLOB) de tip de date text sau imagine în coloanele tabelului INSERTED și DELETED, indiferent dacă procedura este înregistrată sau nu.
• Nu ar trebui să utilizați instrucțiuni SELECT care returnează seturi de rezultate de la un declanșator pentru o aplicație client care necesită o gestionare specială a setului de rezultate, indiferent dacă se face într-o procedură stocată sau nu.
• Nu puteți crea declanșatoare ÎN LOC DE ACTUALIZARE și DELETE pe tabele care au chei externe cu opțiunile de actualizare în cascadă sau, respectiv, de ștergere setate.

2. Exemple de utilizare a declanșatorilor

Exemplul 1: Inserați și actualizați declanșatoarele

Acești declanșatori sunt folositori deoarece pot impune condiții de integritate referențială și pot asigura că datele sunt corecte înainte de a fi introduse în tabel. Declanșatorii sunt de obicei utilizați pentru a actualiza coloanele de timp sau pentru a verifica datele din anumite coloane în funcție de un criteriu necesar. Declanșatorii trebuie utilizați atunci când criteriul de testare este mai complex decât condiția de integritate declarativă.

În exemplul de mai jos, declanșatorul rulează ori de câte ori un rând este inserat sau modificat în tabelul Vânzări. Dacă data comenzii nu este în primele 15 zile ale lunii, rândul nu este introdus în tabel.

CREATE TRIGGER Tri_Ins_Sales
De vânzare
PENTRU INSERT, UPDATE
LA FEL DE
/* Declarați variabilele locale necesare */
DECLARE @nDayOfMonth TINYINT
/* Găsiți informații despre intrarea adăugată */
SELECT @nDayOfMonth = DatePart(zi, i.ord_date)
DIN Vânzări, introdus i
UNDE s.stor_id = i.stor_id
AND s.ord_num = i.ord_num
AND s.title_id = i.title_id
/* Verificați criteriul de eșec și, dacă este necesar,
trimite mesaj de eroare */
DACĂ @nDayOfMonth > 15
ÎNCEPE
/* Notă: derulați întotdeauna mai întâi. Poate nu știi
ce fel de eroare de procesare a apărut care ar putea cauza
timp de blocare nerezonabil de lung */
ROLLBACK TRAN
RAISERROR("Numai comenzile trimise la prima
15 zile ale lunii", 16, 10)
Sfârşit

Dacă încercăm acum să inserăm sau să actualizăm o înregistrare în tabel, dacă condiția specificată nu este îndeplinită, vom primi un mesaj de eroare corespunzător.

Rețineți că fragmentul de cod accesează un tabel nou, iar acest tabel nu se află în lista tabelelor bazei de date. În acest caz, tabelul Inserat conține o copie a fiecărui rând, care va fi adăugată numai dacă tranzacția se finalizează cu succes. Acest tabel și valorile sale sunt folosite la efectuarea oricărei operațiuni de comparare pentru a verifica validitatea tranzacției.

Coloanele tabelului inserate sunt exact aceleași cu coloanele din foaia de lucru. Comparația se poate face pe coloane, așa cum se face în în acest exemplu, unde coloanele din tabelul Vânzări sunt comparate pentru a verifica dacă datele vânzărilor sunt corecte.

De asemenea, puteți crea declanșatoare care funcționează numai atunci când o anumită coloană este actualizată. Pentru a decide dacă se continuă procesarea într-un declanșator, poate fi utilizată instrucțiunea IF UPDATE:

IF UPDATE(au_lname)
ȘI (@@ROWCOUNT=1)
ÎNCEPE
…
Sfârşit

Codul din interiorul blocului este executat numai dacă coloana au_lname este actualizată. Rețineți întotdeauna că coloana care este actualizată nu se modifică în toate cazurile. Dacă sunt necesare modificări, multe aplicații, inclusiv cele mai multe sisteme corporative, actualizați întregul rând.

Operația UPDATE afectează ambele tabele de sistem. Tabelul Inserat stochează valori noi, iar tabelul Șters stochează valori vechi. Prin urmare, puteți utiliza ambele tabele atunci când analizați modificările.

Adesea este necesară înlocuirea unor valori cu unele nedefinite. Acest lucru se face cu o operațiune simplă de atribuire, de exemplu:

NUM_READER = NULL

Exemplul 2: Ștergeți declanșatoarele

Ștergeți declanșatoarele ( ștergeți declanșatorii) sunt utilizate în mod obișnuit în două cazuri: prevenirea ștergerii rândurilor care ar putea cauza probleme de integritate a datelor, cum ar fi un rând folosit ca cheie străină pentru alte tabele și efectuarea de operațiuni de ștergere în cascadă pe copil ( copii) linii principale ( maestru) linii. Acest declanșator poate fi folosit pentru a elimina toate informațiile despre comandă din linia principală vânzări

Declanșatorii iau în considerare suma totală a tuturor rândurilor afectate de operațiunea solicitată. Astfel, ei trebuie să poată lucra cu diferite combinații de informații din tabel și să returneze datele necesare. De exemplu, atunci când se execută o instrucțiune DELETE FROM Authors, declanșatorul trebuie să ia în considerare faptul că instrucțiunea va șterge toate rândurile din tabel.

În exemplul următor, utilizarea variabilei @@ROWCOUNT împiedică ștergerea mai multor rânduri. Acest declanșator rulează ori de câte ori un utilizator încearcă să șterge un rând din tabelul Magazine. Dacă informațiile sunt legate de vânzări, atunci declanșatorul împiedică finalizarea cererii.

CREATE TRIGGER Tri_Del_Stores
Magazine ON
PENTRU ȘTERGERE
LA FEL DE
/* Verificați numărul de linii modificate și interziceți ștergerea mai multor rânduri o dată */
DACĂ @@ ROWCOUNT > 1
ÎNCEPE
ROLLBACK TRAN
RAISERROR ("Doar un rând poate fi șters la un moment dat.", 16, 10)
Sfârşit
/* Declarația unei variabile temporare pentru a salva informațiile distruse */
DECLARE @ StorID caracter (4)
/* Obține valoarea rândului de șters */
SELECT @StorID = d.stor_id
DIN Magazine, Șters d
WHERE s.stor_id *= d.stor_id
DACĂ EXISTĂ (SELECTARE *
DIN Vânzări
WHERE stor_id = @storID)
ÎNCEPE
ROLLBACK TRAN
RAISERROR ("Această informație nu poate fi ștearsă deoarece există o intrare corespunzătoare în tabelul de vânzări.", 16, 10)
Sfârşit

Notă: Utilizarea RAISERROR este cea mai simplă modalitate de a trimite informații detaliate și specifice despre eroare către procesul de apelare sau utilizator. RAISERROR face posibilă specificarea textului mesajului, nivelul de pericol, starea informațiilor și combinarea tuturor acestor lucruri pentru utilizator într-un mesaj descriptiv. Această instrucțiune facilitează, de asemenea, scrierea blocurilor comune de tratare a erorilor în aplicațiile client.

Acest exemplu utilizează, de asemenea, mai multe instrucțiuni de control al tranzacțiilor pentru a opri rularea operațiunilor. Rețineți că fragmentul de cod accesează un nou tabel. Acest tabel nu se află în lista tabelelor bazei de date. În acest caz, tabelul Șters conține o copie a fiecărui rând, care va fi adăugată doar dacă tranzacția se finalizează cu succes. Acest tabel și valorile sale sunt utilizate atunci când se efectuează orice comparație pentru a verifica validitatea tranzacției.

Coloanele din tabelul șterse sunt exact aceleași cu coloanele din foaia de lucru. Comparația se poate face coloană cu coloană, așa cum se arată în exemplul în care coloanele din tabelul Șterse sunt comparate cu coloanele din baza de date Vânzări. Acest lucru va asigura că informațiile care urmează să fie șterse nu includ date de vânzări.

Exemplul 3. ÎN LOC DE declanșatoare

DEclanșatoarele ÎN LOC DE DIFERĂ DE DEclanșatoarele obișnuite (DUPĂ) prin aceea că sunt executate nu după operația care a dus la declanșarea acesteia, ci în schimb, cu toate consecințele care decurg, de exemplu, posibilitatea utilizării lor împreună cu constrângerile de integritate. Tabelele de sistem Insert și Deleted sunt utilizate în ele în același mod ca în declanșatoarele AFTER. Corpul declanșatorului poate duplica operația care a provocat declanșarea, dar acest lucru nu este necesar. Cu alte cuvinte, dacă definim un declanșator INSTEAD OF DELETE, atunci nimic nu ne împiedică să efectuăm o operație DELETE asupra acestuia, care șterge toate rândurile care ar fi trebuit șterse conform operației care a numit declanșatorul, dar nu avem pentru a face acest lucru.

Să dăm un exemplu de utilizare a declanșatorului ÎN LOC DE.

Tabelul de locuri de muncă are o relație 1:M cu tabelul de angajați, deci nu este posibil să ștergeți un loc de muncă dacă are deja angajați alocați. Să creăm un declanșator care, atunci când un loc de muncă este șters, va verifica dacă angajații îi sunt alocați sau nu. Dacă este atribuită, lucrarea nu va fi ștearsă. Datorită faptului că există o constrângere de integritate (DRI), declanșatorul AFTER nu poate funcționa împreună cu acesta. Adică, puteți crea un declanșator ca acesta:

PENTRU ȘTERGERE
LA FEL DE
DACĂ EXISTĂ (SELECT * FROM Employee e JOIN Șters d ON e.job_id=d.job_id)
ÎNCEPE
ROLLBACK TRAN
Sfârşit

Apropo, rețineți că, spre deosebire de exemplul 2, acest declanșator vă permite să ștergeți mai multe rânduri simultan. Cu toate acestea, un astfel de declanșator poate funcționa corect numai dacă conexiunea dintre tabelele Angajați și Jobs este întreruptă, astfel încât DRI să nu fie procesate înainte ca declanșatorul să fie executat.

Dar puteți crea un declanșator ÎN LOC DE:

CREATE TRIGGER Check_Job ON Jobs
ÎN LOC DE ȘTERGERE
LA FEL DE
DELETE FROM Jobs FROM Jobs j JOIN șters d pe d.job_id = j.job_id
WHERE j.job_id NOT IN (SELECT DISTINCT Job_id FROM Employee)

Un astfel de declanșator nu va avea conflicte cu DRI și va fi executat.

Verificarea DRI se efectuează imediat după executarea operației, adică înainte de executarea declanșării AFTER. Când se folosește un flip-flop, operațiunea în esență nu este efectuată și controlul este transferat către flip-flop, astfel încât DRI nu va fi executat.

După cum sa menționat deja, tabelul inserat conține rânduri adăugate, iar tabelul șters conține rânduri șterse. Este ușor de ghicit că atunci când se efectuează o operațiune de actualizare, vor fi utilizate atât tabelul Inserat, cât și tabelul șters. În acest caz, valorile vechi vor ajunge în tabelul șterse, iar cele noi în tabelul inserat. Combinându-le după coloanele cheie, nu este dificil să se determine care valori au fost modificate.

3. Utilizarea declanșatoare imbricate

Declanșatoarele pot fi încorporate unul în celălalt. Sunt permise 32 de niveluri de cuibărit. Dacă nu sunt dorite operațiuni de declanșare imbricate, SQL Server poate fi configurat pentru a le dezactiva.

Notă: Nivelul de imbricare al unui declanșator poate fi verificat în orice moment prin sondajul valorii stabilite în variabila @@NESTLEVEL. Ar trebui să fie în intervalul de la 0 la 32.

Declanșatoarele imbricate pot duce la recursivitate. Există două tipuri de recursivitate: directă și indirectă. Recursiunea directă apare dacă declanșarea unui declanșator duce la modificări care provoacă din nou același declanșator. Recursiunea indirectă apare atunci când declanșarea unui declanșator provoacă modificări care provoacă declanșarea unui alt declanșator, care, la rândul său, duce la modificări care provoacă declanșarea primului declanșator. Desigur, acest lanț poate consta și dintr-un număr mai mare de declanșatori.

Recursiunea directă poate fi dezactivată (și activată) folosind opțiunea de bază de date RECURSIVE_TRIGGERS. Puteți dezactiva (și activa) recursiunea indirectă, precum și imbricarea declanșatorilor în general, folosind opțiunea serverului de declanșatoare imbricate. Această opțiune determină posibilitatea de imbricare a declanșatorilor nu pentru o anumită bază de date, ci pentru întregul server.

Trebuie remarcat faptul că declanșatoarele ÎN LOC DE, prin natura lor, nu sunt supuse recursiunii directe.

Când creați un declanșator, SQL Server nu poate recunoaște în mod independent că o construcție imbricată provoacă o buclă infinită. Un astfel de fapt nu poate fi stabilit decât în ​​timpul executării acestui declanșator.

Să presupunem că Table_A include un declanșator, trigger_A , care este executat atunci când are loc o actualizare a Table_A. Când este executat, trigger_a determină actualizarea Tabelului_B. Acest tabel include un trigger_b care rulează atunci când Table_B este actualizat și determină actualizarea Table_A. Astfel, dacă utilizatorul actualizează oricare dintre aceste două tabele, cele două declanșatoare continuă să se facă reciproc să se execute pe termen nelimitat. Când apare această situație, SQL Server închide sau anulează execuția declanșatorului.

Să ne imaginăm că tabelul Vânzări include un declanșator, iar tabelul Magazine include altul. Următoarele arată definiția a două declanșatoare imbricate care sunt executate atunci când are loc o operațiune de ștergere în tabelul Vânzări:

/* Primul declanșator distruge rândurile din tabelul Stores,
dacă rândurile din tabelul de vânzări sunt distruse */
CREATE TRIGGER Tri_Del_Sales
De vânzare
PENTRU ȘTERGERE
LA FEL DE

PRINT „Un declanșator de ștergere rulează pentru tabelul de vânzări...”
/* Declarația unei variabile temporare pentru stocarea informațiilor șterse */
DECLARE @sStorID char(4), @sMsg varchar(40)
/* Obține valoarea ID-ului rândului de șters */

DE Șters


/* Șterge o linie */
SELECT @sMsg = "Magazin " + @sStorID + " șters"
PRINT @sMsg
ȘTERGEȚI DIN Magazine
WHERE stor_id = @sStorID
PRINT „Sfârșitul execuției declanșatorului pentru tabelul de vânzări”
MERGE
/* Al doilea declanșator distruge rândurile unui tabel,
dacă rândurile altuia sunt distruse */

Magazine ON
PENTRU ȘTERGERE
LA FEL DE
/* Declarația declanșatorului care urmează să fie executat */
PRINT „Un declanșator de ștergere rulează pentru tabelul Magazine...”
/* Declarația unei variabile temporare pentru stocarea informațiilor
distrus de pe masă */
DECLARE @sStorID char(4), @sMsg varchar (200)
/* Obține valoarea distrusă */
SELECTARE TOP 1 @sStorID = stor_id
DE Șters
/* Șters este un tabel auxiliar pe care SQL Server
folosit pentru a stoca înregistrările distruse */
DACĂ @@ROWCOUNT = 0
ÎNCEPE
PRINT „Nu există rânduri care se potrivesc în tabelul Magazine”
ÎNTOARCERE
Sfârşit
/* Ștergeți o intrare */
SELECT @sMsg = „Ștergerea reducerilor specifice magazinului” + @sStorID
PRINT @sMsg
DELETE Reduceri
WHERE Stor_id = @sStorID
PRINT „Numărul de reduceri eliminate: „ + CONVERT(VARCHAR(4), @@ROWCOUNT)
PRINT „Sfârșitul execuției declanșatorului pentru tabelul Stores”

Dacă o instrucțiune DELETE este executată pe tabelul de vânzări, așa cum se arată în exemplul următor, declanșatorul se declanșează, ceea ce la rândul său determină executarea declanșatorului tabelului Stores.

Hai sa facem:

DELETE FROM Sales WHERE stor_id = "8042"

Rezultat:

Un declanșator de ștergere rulează pe tabelul de vânzări...
Magazinul 8042 a fost eliminat
Un declanșator de ștergere rulează pe tabelul Magazine...
Eliminarea reducerilor aferente magazinului 8042
(1 rând(e) afectat)
Numărul de reduceri eliminate: 1
Sfârșitul execuției declanșatorului pentru tabelul Stores
(1 rând(e) afectat)
(4 rânduri afectate)
Sfârșitul execuției declanșatorului pentru tabelul de vânzări

Atenție la ordinea mesajelor afișate. Mai întâi, declanșatorul este declanșat pe tabelul de vânzări. Îndepărtează un rând din tabelul Magazine, declanșând astfel un declanșator. Mai mult, de fapt, nici tabelul Vânzări, nici tabelul Stroes nu au fost încă șterse (ștergerea este în curs), acest lucru este evidențiat de absența unui mesaj automat de server (N rânduri afectate), care apare la ștergerea de pe orice tabel și arată câte rânduri au fost eliminate.

După lansare, declanșatorul de pe tabelul Magazine se șterge șiruri aferente din tabelul de reduceri (Reduceri), despre care este afișat mesajul (1 rând(e) afectat(e). Apoi tipărește mesajele corespunzătoare și își termină munca. Imediat ce și-a terminat activitatea, rândul din tabelul Magazine este șters, a cărui ștergere a făcut ca acesta să funcționeze. Apoi, deoarece acest rând este șters, declanșatorul din tabelul Magazine revine la lucru. Acest declanșator emite ultimul mesaj de ieșire și iese. Odată ce s-a finalizat, este afișat mesajul (1 rând(e) afectat(e) care indică faptul că un rând a fost șters din tabelul Magazine. Și numai după aceasta rândurile sunt în cele din urmă șterse din tabelul Vânzări.

Notă: Declanșatorii și integritatea referențială declarativă, în general, nu pot funcționa împreună. De exemplu, exemplul anterior arată că înainte de a executa o instrucțiune DELETE, trebuie mai întâi să ștergeți condiția de pe valoarea FOREIGN KEY din tabelul Reduceri. Ori de câte ori este posibil, ar trebui utilizată fie o condiție de declanșare, fie o condiție de integritate referențială. Cu toate acestea, așa cum sa menționat deja, în MS SQL Server 2000 au apărut declanșatorii. Ele pot fi utilizate împreună cu mecanismele de integritate declarativă, dar nu puteți utiliza operațiuni în cascadă în conexiuni pentru aceeași operațiune pentru care a fost creat declanșatorul ÎN LOC DE. De exemplu, dacă este creat un declanșator ÎN LOC DE ȘTERGERE, atunci nu puteți utiliza construcția ON DELETE CASCADE în relațiile în care acest tabel este un tabel subordonat.

Exemplul 2

Acum să dăm un exemplu de recursivitate directă în declanșatoare. Să creăm un declanșator care, la ștergerea unui angajat, ar șterge și acei angajați care au același nume de familie sau prenume ca și cel șters. Mai mult, atunci când angajații sunt șterși de un declanșator, același declanșator este declanșat din nou pentru această ștergere și din nou șterge persoanele cu același nume de familie sau prenume și așa mai departe.

CREATE TRIGGER Del_Empl_Tr ON Angajat
PENTRU ȘTERGERE
LA FEL DE
DACĂ EXISTĂ (SELECTARE * FROM Angajat e
JOIN Șters d pe e.lname = d.lname SAU e.Fname = d.fname)
DELETE FROM Angajat
FROM Employee e JOIN Șters d pe e.lname = d.lname SAU e.Fname = d.fname

Nu există angajați cu același nume sau prenume în baza de date a pub-urilor, dar puteți adăuga singuri astfel de angajați și verificați ce se întâmplă dacă eliminați unul dintre ei. De exemplu, lăsați următorii angajați să fie în baza de date:

Dacă urmați acum instrucțiunile:

DELETE FROM Employee WHERE Fname = „Ivan” AND Lname = „Ivanov”

apoi declanșatorul care va fi lansat la ștergere, pe lângă Ivan Ivanov, va șterge și Ivan Sergeev și Mihail Ivanov. După această eliminare, declanșatorul va fi lansat din nou și va căuta toți Ivanov și Mihail, precum și Ivanov și Sergeev. Ca urmare a muncii sale, Petr Sergeev va fi înlăturat. Apoi același declanșator îl va șterge pe Peter Vasiliev. După aceasta, declanșatorul îi va căuta pe Petrov și Vasilyev, dar din moment ce nu mai sunt în tabel, execuția se va termina acolo.

Rețineți că verificarea IF EXISTS trebuie efectuată aici. Dacă nu se face acest lucru, atunci când vine vorba de ștergerea lui Peter Vasiliev, instrucțiunea DELETE va fi executată și, deși nu va șterge de fapt pe nimeni, declanșatorul nou apelat se va apela din nou (din nou fără a șterge efectiv pe nimeni), etc., până când se depășește nivelul maxim de imbricare 32. După atingerea nivelului de imbricare 32, va apărea o eroare și toate acțiunile vor fi anulate.

Exemplul 3. Recursie indirectă

Să schimbăm exemplul 1, astfel încât dacă un rând este șters din tabelul Vânzări, atunci magazinul în care s-a făcut vânzarea ștearsă să fie și el șters. Deoarece relația dintre aceste tabele este 1:M, pot exista multe vânzări în magazin care sunt șterse, și nu doar cea pe care încercăm să o ștergem. Prin urmare, lanțul ar trebui să fie după cum urmează: ștergeți vânzarea → ștergeți magazinul în care a fost făcută, → ștergeți toate celelalte vânzări efectuate în acest magazin, → ștergeți toate reducerile asociate acestui magazin. În plus, vom implementa acești declanșatori sub formă de declanșatoare ÎN LOC DE, astfel încât să nu fie nevoie să întrerupem conexiunile dintre tabele.

CREATE TRIGGER Tri_Del_Sales
De vânzare
ÎN LOC DE ȘTERGERE
LA FEL DE
DELETE FROM Sales FROM Sales s JOIN Deleted d on d.ord_num = s.ord_num
DACĂ EXISTĂ (SELECT * FROM Stores s JOIN Deleted d ON d.stor_id = s.stor_id)
DELETE FROM Magazine FROM s JOIN Șters d ON d.stor_id = s.stor_id
MERGE

CREATE TRIGGER Tri_Del_Stores
Magazine ON
ÎN LOC DE ȘTERGERE
LA FEL DE
DELETE FROM Reduceri FROM Discounts di JOIN Deleted de pe di.stor_id=de.stor_id
DACĂ EXISTĂ (SELECT * FROM Vânzări s JOIN Șters d pe d.stor_id = s.stor_id)
DELETE FROM Sales FROM Sales s JOIN Deleted d on d.stor_id = s.stor_id
DELETE FROM Magazine FROM s JOIN Șters d pe d.stor_id = s.stor_id

Pentru a verifica, puteți rula comanda:

DELETE FROM Sales WHERE ord_num = "P723"

Ca urmare, nu numai rândul cu codul de comandă „P723” va fi șters din tabelul Vânzări, ci și alte trei rânduri aferente aceluiași magazin (cod 8042). Magazinul 8042 însuși și reducerea asociată acestuia vor fi, de asemenea, șterse.

În exemplul de mai sus, printre altele, toate ieșirile de mesaje sunt eliminate și apelurile la instrucțiunile DELETE sunt modificate, deoarece nu există ieșiri de mesaje, nu este nevoie să generați valoarea variabilei locale @sStroID . Utilizarea acestei variabile în instrucțiunea DELETE a limitat oarecum aplicabilitatea declanșatorilor. Astfel, declanșatoarele din exemplul 2 au fost concepute pentru a șterge înregistrările pentru un singur magazin, iar la ștergerea înregistrărilor legate de mai multe magazine simultan, acestea nu au funcționat corect. Acum nu există o astfel de restricție, deoarece toate înregistrările asociate cu înregistrările din tabelul șterse sunt șterse (adică cu toate rândurile care sunt efectiv șterse).

S-ar putea întreba: de ce să folosiți recursiunea? Nu ar fi mai ușor, atunci când ștergeți din tabelul de vânzări, să ștergeți într-un declanșator pe acesta toate înregistrările din sine care se referă la același magazin ca și rândul de vânzări șters, apoi ștergeți rândul din tabelul Magazine și într-un declanșator pe tabelul Magazine ștergeți înregistrările aferente doar din tabelul Reduceri? Da, acest lucru se poate face, dar numai dacă dăm întotdeauna comanda de ștergere specific din tabelul de vânzări (cum s-a făcut mai sus în timpul verificării). Cu toate acestea, putem lansa o comandă de ștergere din tabelul Magazine, de exemplu:

DELETE FROM magazinele WHERE stor_id = 8042

Și în acest caz, dorim și ca comanda să funcționeze corect. Dacă declanșatorul din tabelul Magazine, așa cum este sugerat în întrebare, nu include ștergerea din Vânzări, atunci dacă există vânzări pentru magazinul care este șters, atunci o astfel de ștergere va duce la o eroare. Exemplul nostru ne permite să rezolvăm această problemă. Ei bine, dacă declanșatorul de pe Magazine include o comandă de ștergere din Vânzări, atunci în declanșatorul de pe Vânzări nu este nevoie să includeți ștergerea vânzărilor din același magazin cu cel care este șters, deoarece aceasta se va efectua automat prin recursivitate.

Nota 1: Pentru a preveni interferența declanșatorilor deja creați în exemplele anterioare, trebuie să le ștergeți folosind instrucțiunea DROP TRIGGER nume_declanșator.

Nota 2: Încă o dată, vă rugăm să rețineți că, pentru ca recursiunea să funcționeze, trebuie setate baza de date și opțiunile de server corespunzătoare.

Exemplul 4

În ultimul exemplu, luați în considerare cazul definirii mai multor declanșatori pentru o operație de modificare a tabelului:

CREATE TRIGGER trig_del_l ON Authors FOR DELETE AS
PRINT „Ștergeți declanșatorul #1”
MERGE

CREATE TRIGGER trig_del_2 ON Authors FOR DELETE AS
PRINT „Ștergeți declanșatorul #2”
MERGE

CREATE TRIGGER trig_upd_l ON Authors FOR UPDATE AS
PRINT „Actualizare declanșator #1”
MERGE

CREATE TRIGGER trig_upd_3 ON Authors FOR UPDATE AS
PRINT „Actualizare declanșator #3” „
MERGE

CREATE TRIGGER trig_upd_2 ON Authors FOR UPDATE AS
PRINT „Actualizare declanșator #2”
MERGE

Acum să încercăm să schimbăm o intrare în masa:

UPDATE Autori
SET au_fname = "Yuri" WHERE au_lname = "Tikhomirov";

Toate cele trei declanșatoare de actualizare vor funcționa:

Actualizați declanșatorul #1

Actualizați declanșatorul #3

Actualizați declanșatorul #2

Acordați atenție secvenței de execuție a declanșatorilor: este determinată de ordinea în care sunt create. Dacă acum ștergem declanșatorul trig_upd_3 și apoi îl creăm din nou, atunci când actualizăm tabelul vom obține următorul rezultat:

Actualizați declanșatorul #1

Actualizați declanșatorul #2

Actualizați declanșatorul #3

Declanșatoarele multiple sunt utilizate destul de activ în timpul replicării.

4. Afișați informațiile declanșatorului și modificați declanșatorul

Pentru a afla scopul declanșatorului unui tabel, trebuie să afișați informații care descriu orice declanșator pe care îl deține tabelul. Există mai multe moduri de a obține informații despre declanșatorul unui anumit tabel. Unul dintre ele este SQL Server Enterprise Manager, celălalt este procedurile de sistem sp_help și sp_depends. Pentru a vizualiza textul de declanșare prin Enterprise Manager, urmați acești pași:

1. În Enterprise Manager, selectați serverul și baza de date cu care doriți să lucrați.
2. Deschideți tabelul în modul proiectare cu comanda Design Table și, în fereastra acestuia, faceți clic pe butonul Triggers din bara de instrumente.
3. Va apărea caseta de dialog Creare declanșator, unde puteți vizualiza textul oricăruia dintre declanșatoarele instalate.

Procedurile stocate de sistem sp_help și sp_depends au fost deja descrise în subiectul „Proceduri stocate”.

Pentru a modifica funcționalitatea unui declanșator, puteți fie să-l ștergeți și să creați unul nou cu modificările corespunzătoare, fie să modificați una existentă. Pentru a schimba un declanșator existent în T-SQL, există comanda ALTER TRIGGER. Sintaxa sa este similară cu cea a comenzii CREATE TRIGGER, cu excepția faptului că folosește cuvântul cheie ALTER în loc de CREATE.

De asemenea, puteți modifica declanșatorul utilizând Enterprise Manager. Pentru a face acest lucru, după ce vă conectați la Enterprise Manager, trebuie doar să faceți modificări și să le aplicați.

5. Eliminarea declanșatorilor

Uneori trebuie să eliminați declanșatorii dintr-un tabel sau mese. De exemplu, atunci când mutați o aplicație în producție, este posibil să doriți să eliminați declanșatorii care au furnizat procesare de înaltă calitate, dar performanțe reduse foarte mult. Puteți elimina pur și simplu declanșatoarele pentru a le înlocui cu o versiune mai nouă. Pentru a elimina un declanșator, utilizați instrucțiunea DROP TRIGGER:

DROP TRIGGER [proprietar.]nume_declanșator [, n]

Ștergerea unui declanșator este opțională dacă noul declanșator îl înlocuiește pe unul existent. Când aruncați un tabel, toate obiectele asociate cu acesta sunt automat distruse, inclusiv declanșatoarele.

Exemplu de eliminare a declanșatorului Tri_Dei_Autnors:

DROP TRIGGER Tri_Del_Authors

6. Întreruperea și reluarea declanșatorilor

Este adesea necesar să dezactivați un declanșator pentru o perioadă de timp fără a-l șterge efectiv. Acest lucru poate fi realizat folosind designul ALTER TABLE<имя_таблицы>DEZACTIVAȚI DEclanșatorul<имя триггера> pentru a dezactiva declanșatorul și ALTER TABLE<имя_таблицы>ACTIVARE TRIGGER<имя триггера> să-și reia activitatea.

Sarcini pentru munca independentă

Înainte de a începe să executăm sarcini, permiteți-ne să vă reamintim că declanșatorii sunt proceduri stocate în sistem care sunt asociate cu un anumit tabel. Pentru a apela editorul de declanșare, trebuie să selectați tabelul folosind butonul din dreapta meniul contextual mergi la sectiune Toate sarcinile > Gestionați declanșatoarele, și sunteți dus la editorul de declanșare (Fig. 1).

Orez. 1. Starea inițială a editorului de declanșare la crearea unui nou declanșator

Sarcina 1. Dezvoltați un declanșator care ar șterge o înregistrare despre o carte dacă ultima copie a acestei cărți este șters. Pentru ce tabel vei scrie acest declanșator? Când scrieți un declanșator, amintiți-vă că tabelul „Cărți” este asociat cu „Autori” și „ Catalog de sistem" Cu toate acestea, ele sunt legate printr-o relație multi-la-mulți, pentru care sunt folosite tabele de legătură. Nu puteți șterge date despre o carte dacă are legături în aceste tabele de legături. Luați în considerare mai întâi ștergerea datelor din tabelele de legătură. Verificați funcționarea acestui declanșator.

Sarcina 2. Dezvoltați un declanșator care să nu permită ștergerea unei copii a unei cărți dacă această copie este în prezent în mâinile cititorului. Pentru a anula comanda de ștergere, utilizați comanda ROLLBACK de returnare a tranzacției.

Verificați funcționarea declanșatorului în modul independent încercând să ștergeți nu ultima copie a cărții care are un semn care indică faptul că se află în posesia cititorului.

Încercați să eliminați o copie a cărții care nu se află în mâinile cititorului.

Verificați funcționarea celor două declanșatoare încercând să eliminați ultimul exemplar al cărții care se află în mâinile cititorului.

Sarcina 3. Dezvoltați un declanșator care să controleze emiterea cărților către un cititor și, dacă numărul de cărți nelivrate la îndemână depășește trei, nu ar permite emiterea unei alte cărți acestui cititor.

Sarcina 4. Dezvoltați un declanșator care ar adăuga o instanță atunci când este introdusă o nouă carte. Într-adevăr, am stabilit că cărțile sunt prezente în catalogul nostru numai dacă se află în biblioteca noastră, prin urmare, atunci când introduceți o carte nouă, un exemplar al acestei cărți ar trebui adăugat la tabelul „Copiere”.

Sarcina 5. Dezvoltați un declanșator de tipul ÎN LOC DE pentru tabelul „Cititori”. Acest declanșator ar trebui să verifice dacă există informații despre cel puțin unul dintre telefoane pentru o comunicare rapidă cu cititorul, iar dacă nu există astfel de informații, atunci nu introduceți date despre cititor.

Sarcina 6. Dezvoltați un declanșator care, atunci când valoarea unui câmp care simbolizează prezența unei copii de carte în bibliotecă, de exemplu YES_NU, se schimbă de la „1” la „0”, să înlocuiască automat valorile din „Problemă câmpurile data”, „Data returnării” și „Numărul cardului de bibliotecă” „la termen nedeterminat.

Sarcina 7. Dezvoltați un declanșator care nu vă permite să ștergeți un cititor dacă are cel puțin o carte din bibliotecă.

Sarcina 8. Dezvoltați un declanșator care, atunci când o copie a unei cărți este ștearsă, ar verifica câte copii ale acestei cărți au rămas în bibliotecă și, dacă rămâne doar un exemplar, atunci ar crește prețul acestei cărți cu 15% la fel de rar şi valoros.

versiune tipărită

Trigger baza de date este un bloc PL/SQL special conceput, stocat într-o bază de date. Fiecare declanșator este asociat cu tabel specific iar ORACLE este lansat automat atunci când una dintre instrucțiunile DML (INSERT, DELETE, UPDATE) sau o combinație a acestora este executată pe acest tabel.

Scopul declanșatorilor. Declanșatoarele pot fi utilizate:

1) să implementeze constrângeri complexe de integritate a datelor care nu pot fi aplicate într-un mod standard la crearea unui tabel;

2) prevenirea tranzacțiilor incorecte;

3) efectuarea de proceduri pentru verificarea cuprinzătoare a drepturilor de acces și a secretului datelor;

4) generarea unor expresii pe baza valorilor disponibile în coloanele de tabele;

5) implementarea unor reguli de afaceri complexe pentru prelucrarea datelor (abilitatea de a urmări „ecou”, adică capacitatea de a actualiza datele tabelelor aferente atunci când un tabel se schimbă).

Crearea și activarea declanșatorilor. Pentru a crea și declanșa automat un declanșator, se utilizează următoarea sintaxă generală:

CREATE TRIGGER nume_declanșator

(ÎNAINTE | DUPĂ)

(INSERTARE | ȘTERGERE | ACTUALIZARE)

ON table_name

< PL/SQL_блок >

Dacă sunt prezente cuvintele cheie OR REPLACE, declanșatorul este recreat dacă există deja.

ÎNAINTE de proiectare | AFTER indică când declanșatorul este declanșat. Opțiunea BEFORE înseamnă că declanșatorul se va declanșa înainte de executarea instrucțiunii DML de declanșare; opțiunea AFTER înseamnă că declanșatorul se va declanșa după executarea instrucțiunii DML de activare.

INSERT | ȘTERGE | UPDATE specifică tipul de instrucțiune DML care declanșează declanșatorul. Este permisă, folosind operația logică OR, să se specifice un set de operatori de activare, de exemplu: INSERT OR DELETE. Când utilizați opțiunea UPDATE, dacă este specificată o listă de coloane, declanșatorul se va declanșa atunci când una dintre coloanele specificate este modificată; dacă nu există o listă de coloane, atunci declanșatorul va fi declanșat atunci când oricare dintre coloanele tabelului asociate declanșatorului se schimbă.

Construcția FOR EACH ROW indică natura acțiunii de declanșare: șir sau operator. Dacă clauza FOR EACH ROW este prezentă, atunci declanșatorul este un șir; în lipsa acestuia, declanșatorul este operator. Un declanșator de instrucțiune se declanșează o dată înainte sau după executarea instrucțiunii DML care declanșează declanșatorul, indiferent de câte rânduri din tabelul asociat declanșatorului sunt modificate. Un declanșator de rând este declanșat o dată pentru fiecare rând care este modificat de instrucțiunea DML care declanșează declanșatorul.

Puteți utiliza cuvântul cheie WHEN pentru a specifica restricție suplimentară la rândurile tabelului asociat declanșatorului, la modificarea cărora declanșatorul poate fi declanșat.

Construcția PL/SQL_block reprezintă un bloc PL/SQL pe care ORACLE îl rulează atunci când declanșatorul este declanșat.

Clasificarea declanșatorilor. Există în principiu douăsprezece tipuri de declanșatori. Tipul de declanșare este determinat de o combinație a următorilor trei parametri:

1) natura impactului declanșatorului asupra rândurilor tabelului asociat cu acesta (șir sau operator);

2) momentul lansării declanșatorului: înainte (ÎNAINTE) sau după (DUPĂ) execuția instrucțiunii DML care activează declanșatorul;

3) tipul de operator DML care activează declanșatorul (INSERT, DELETE, UPDATE);

Ordinea în care sunt activate declanșatoarele. Dacă un tabel are mai multe tipuri de declanșatori, acestea sunt activate conform următoarei scheme:

1) se execută declanșarea operatorului BEFORE (dacă sunt mai multe, atunci nu se poate spune nimic despre ordinea executării lor);

2) declanșarea șirului BEFORE este executată;

3) se execută operatorul DML care activează declanșatorul, urmat de verificarea tuturor constrângerilor de integritate a datelor;

4) declanșarea șirului AFTER este executată, urmată de verificarea tuturor constrângerilor de integritate a datelor;

5) declanșarea operatorului AFTER este executată.

Predicate de declanșare. Dacă un declanșator specifică un set de operatori DML care activează declanșatorul (de exemplu, INSERT OR DELETE), atunci pentru a recunoaște ce operator DML specific este executat pe tabelul asociat cu declanșatorul, sunt folosite predicate de declanșare: INSERARE, ȘTERGERE, ACTUALIZARE. Ei reprezintă funcții logice, returnând TRUE dacă tipul operatorului de activare se potrivește cu tipul predicatului și FALSE în caz contrar. Pentru a specifica aceleași acțiuni atunci când executați diferite instrucțiuni DML în operator condițional predicatele de declanșare sunt combinate folosind operații logice.

Pseudo-înregistrări. Pentru declanșatoarele șir, există constructe speciale care permit, la executarea operațiunilor DML pe un rând de tabel, să acceseze atât valorile vechi care se aflau în el înainte de modificare, cât și cele noi care vor apărea în rând după modificarea acestuia. Aceste constructe se numesc pseudo-înregistrări și sunt notate vechi și noi. Structura acestor pseudo-înregistrări este identică cu structura rândului tabelului care se modifică, dar puteți opera numai pe câmpuri individuale ale pseudo-înregistrării. Câmpurile unei pseudo-înregistrări sunt accesate după următoarea schemă: înainte de vechi sau nou este plasat simbolul „:”, apoi numele câmpului este indicat printr-un punct. Valorile pe care le iau câmpurile de pseudo-înregistrare la activarea instrucțiunilor DML sunt definite după cum urmează.

Instrucțiunea INSERT - pseudo-record:new este echivalent cu rândul care este inserat, iar pseudo-record:old este NULL în toate câmpurile.

Instrucțiunea DELETE - pseudo-record:old este echivalent cu rândul care este șters, iar pseudo-record:new este NULL în toate câmpurile.

Instrucțiunea UPDATE - pseudo-record:new este echivalent cu rândul rezultat din modificare, iar pseudo-record:old în toate câmpurile este valoarea inițială a rândului.

Activarea și dezactivarea declanșatorilor. Un declanșator stocat în baza de date poate fi dezactivat temporar fără a-l elimina din baza de date. Pentru a face acest lucru, utilizați următoarea comandă:

ALTER TRIGGER trigger_name DISABLE;

Puteți activa un declanșator după o anumită perioadă de timp folosind comanda

ALTER TRIGGER trigger_name ENABLE;

Puteți dezactiva sau permite declanșarea tuturor declanșatoarelor asociate cu o anumită tabelă folosind comanda

ALTER TABLE nume_tabelă (DEZACTIVARE | ACTIVARE) TOATE DEclanșatoarele;

unde opțiunea DISABLE este folosită pentru a dezactiva, iar opțiunea ENABLE este folosită pentru a activa toți declanșatorii unui anumit tabel.

Eliminarea declanșatorilor din baza de date. Distrugerea unui declanșator, adică ștergerea unui declanșator din baza de date, se face folosind următoarea comandă:

DROP TRIGGER nume_declanșator;

Obținerea de informații despre declanșatoare. Declanșatoarele sunt stocate într-o bază de date, astfel încât informațiile despre ei pot fi preluate din vizualizarea dicționarului de date USER_TRIGGERS, de exemplu cu următoarea comandă:

SELECTAȚI * FROM USER_TRIGGERS;

Exemple.

1. Creați un declanșator care, înainte de a introduce următorul rând în tabelul KNIGA_POSTAVKA, verifică prezența codului de carte specificat în tabelul KNIGA. Dacă codul de carte specificat nu este prezent în tabelul KNIGA, ar trebui generată o excepție cu mesajul corespunzător.

Noi rânduri sunt adăugate la tabelul KNIGA_POSTAVKA folosind instrucțiunea INSERT. Deoarece declanșatorul trebuie să se declanșeze înainte de a executa fiecare instrucțiune INSERT, acesta trebuie, prin urmare, să fie un șir ÎNAINTE de declanșare. Pentru a menține integritatea datelor, este necesar să verificați dacă codurile de carte introduse se află și în tabelul KNIGA. Pentru a face acest lucru, utilizați o căptușeală instrucțiunea SELECT informațiile sunt preluate din tabelul KNIGA, unde câmpul BOOK_CODE al pseudo-înregistrării este utilizat în condiția de selecție: nou. Dacă numărul de rânduri cu un anumit cod de carte din tabelul KNIGA este egal cu zero, va fi lansată o excepție și va fi emis un mesaj corespunzător.

Declanșatorul TR1 este creat prin introducerea următoarei instrucțiuni:

CREAȚI SAU ÎNLOCUȚIȚI TRIGGERUL TR1

ÎNAINTE DE INSERT PE KNIGA_POSTAVKA

SELECTAȚI COUNT(*) ÎN KOL DIN KNIGA

WHERE COD_CARTE = :NOU.COD_CARTE;

DACĂ KOL = 0 atunci RAISE_APPLICATION_ERROR

(–20212, „Tabelul KNIGA nu conține informații despre această carte”);

Acțiunea declanșatorului TR1 poate fi verificată prin executarea următoarei instrucțiuni, care inserează un rând în tabelul KNIGA_POSTAVKA și, prin urmare, determină activarea declanșatorului TR2:

INSERT INTO KNIGA_POSTAVKA VALUES(21,15,’Ivanov’,15,

Deoarece codul cărții 15 nu se află în tabelul KNIGA, se va arunca o excepție și va fi emis un mesaj corespunzător.

2. Creați un declanșator care interzice intrarea în rândurile tabelului KNIGA cu o valoare a câmpului PREț mai mare de 5.000 de ruble, precum și creșterea prețului cărților, informații despre care sunt stocate în tabelul KNIGA, cu mai mult de 20%. Dacă această cerință este încălcată, trebuie generată o excepție cu un mesaj corespunzător.

Deoarece noi rânduri sunt adăugate în tabelul KNIGA ca urmare a executării instrucțiunii INSERT, iar valoarea câmpului PRICE din tabelul KNIGA, care conține prețul cărții, poate fi modificată ca urmare a executării instrucțiunii UPDATE, trigger specifică un set de instrucțiuni DML de declanșare. Deoarece declanșatorul trebuie să se declanșeze înainte de a executa fiecare dintre instrucțiunile DML specificate, este deci un șir ÎNAINTE de declanșare. Deoarece acțiunile efectuate de declanșator sunt diferite pentru fiecare dintre instrucțiunile DML de declanșare care modifică tabelul KNIGA, predicatele de declanșare corespunzătoare INSERTING și UPDAITING sunt folosite pentru a recunoaște tipul de instrucțiune DML. Datorita faptului ca la introducerea de noi randuri trebuie verificata noua valoare a campului PRET, iar la modificarea valorii campului PRET, noua valoare trebuie comparata cu valoarea veche, este necesara folosirea de pseudo-inregistrari. : nou și: vechi.

Crearea unui declanșator TR2 se face prin introducerea următoarei instrucțiuni:

CREAȚI SAU ÎNLOCUIȚI TRIGGER TR2

ÎNAINTE DE INSERAREA SAU ACTUALIZAREA PREȚULUI PE KNIGA

DACĂ INSERARE ATUNCI

DACĂ:NOU.PRĂT > 5000 ATUNCI

RAISE_APPLICATION_ERROR

(–20102, „În tabelul KNIGA nu se pot face înregistrări cu un preț de carte > 5000”);

DACĂ ACTUALIZAȚI ATUNCI

DACĂ:NEW.PRICE > :OLD.PRICE*1.2 ATUNCI

RAISE_APPLICATION_ERROR

(–20103, „În tabelul KNIGA, nu puteți modifica prețul unei cărți cu mai mult de 20%”);

Acțiunea declanșatorului TR2 poate fi testată prin executarea următoarelor instrucțiuni, care, prin inserarea de rânduri în tabelul KNIGA și actualizarea rândurilor în tabelul KNIGA, determină astfel activarea acestuia.

Operator pentru inserarea rândurilor în tabelul KNIGA, determinând activarea declanșatorului TR2:

INSERT INTO KNIGA VALUES(21, „Dune”, „Herbert”, 5268, „Ast”,

"Fantastic");

Operatorul de actualizare a rândurilor din tabelul KNIGA, determinând activarea declanșatorului TR2:

UPDATE KNIGA SET PRET=6000;

Deoarece aceste declarații încalcă cerințele privind valoarea și modificarea prețului cărților, se va genera o excepție în toate cazurile și se va emite un mesaj corespunzător.

3. Creați un declanșator care intră în tabelul STAT creat care conține coloanele:

numele editorului – IZD,

numărul de cărți din genul „Roman” – KOL_ROM,

numărul de cărți din genul Fantasy – KOL_FAN,

cu fiecare modificare a tabelului, KNIGA generează și introduce în coloanele corespunzătoare din tabelul STAT numărul total de cărți pentru fiecare dintre edituri în contextul temelor specificate: „Roman” și „Ficțiune”.

Modificarea tabelului KNIGA se realizează prin executarea următoarelor instrucțiuni DML: INSERT, DELETE sau operatorul UPDATE, care modifică valoarea coloanei GENRE din tabelul KNIGA. Deoarece acțiunile de generare a informațiilor în tabelul STAT sunt efectuate după execuția fiecărui operator care modifică tabelul KNIGA, acesta este un operator AFTER declanșator după tip. Deoarece acțiunile efectuate de un declanșator sunt aceleași pentru toate tipurile de operatori care îl activează, predicatele de declanșare nu sunt utilizate. Înainte de a crea un declanșator, trebuie creat tabelul STAT.

Crearea unui tabel STAT poate fi realizată prin introducerea următorului set de instrucțiuni:

DROP TABLE STAT;

CREATE TABLE STAT

(IZD VARCHAR2(15),

KOL_ROM NUMBER(7),

KOL_FAN NUMBER(7)

Crearea unui declanșator TR3 se face prin introducerea următoarei instrucțiuni:

CREAȚI SAU ÎNLOCUIȚI TRIGGER TR3

DUPĂ INSEREREA SAU ȘTERGEREA SAU ACTUALIZAREA GENULUI

CURSOR V1 ESTE EDITURA SELECT,

COUNT(NUME) KOL1

FROM KNIGA WHERE GEN = „Roman”

GRUPĂ PE EDITURĂ;

CURSOR V2 ESTE EDITURA SELECT,

COUNT(NUME) KOL2

FROM KNIGA WHERE GEN = „Ficțiune”

GRUPĂ PE EDITURĂ;

ȘTERGERE DIN STAT;

PENTRU Z1 IN V1 LOOP

INSERT INTO STAT VALUES(Z1.PUBLISHER,

PENTRU Z1 IN V2 LOOP

UPDATE STAT SET KOL_FAN = Z1.KOL2

UNDE IZD = Z1.EDITOR;

DACĂ SQL%NEGĂSIT, atunci

INSERT INTO STAT VALUES(Z1.PUBLISHER, 0,

Acțiunea declanșatorului poate fi testată prin executarea următoarelor instrucțiuni, care, prin inserarea de rânduri în tabelul KNIGA, ștergerea rândurilor și actualizarea rândurilor în tabelul KNIGA, determinând astfel activarea declanșatorului TR3.

Operatori pentru inserarea de rânduri în tabelul KNIGA, determinând activarea declanșatorului TR3:

INSERT INTO KNIGA VALUES(46, „Ereticii lui Dune”, „Herbert”, 368,

„Ast”, „Fantastic”);

INSERT INTO KNIGA VALUES(42, „Ingvar și Alder”,

„Nikitin”, 168, „Ast”, „Roman”);

Operatori pentru ștergerea rândurilor din tabelul KNIGA, determinând activarea declanșatorului TR3:

DELETE KNIGA WHERE NAME = "Cazaci";

Operatori de modificare a rândurilor din tabelul KNIGA care provoacă activarea declanșatorului TR3:

UPDATE KNIGA SET GEN="Fiction" WHERE NAME =

„Ingvar și arin”;

Puteți vizualiza informații în tabelul STAT cu următoarea declarație.

163

Sintaxa precedentă se aplică numai declanșatorilor DML. Declanșatoarele DDL au o formă ușor diferită de sintaxă, care va fi afișată mai târziu.

Aici, parametrul schema_name specifică numele schemei căreia îi aparține declanșatorul, iar parametrul trigger_name specifică numele declanșatorului. Parametrul table_name specifică numele tabelului pentru care este creat declanșatorul. (Declanșatoarele pentru vizualizări sunt, de asemenea, acceptate, așa cum este indicat de prezența parametrului view_name.)

De asemenea, puteți seta tipul de declanșare folosind două parametri suplimentari: DUPA si INLOC DE. (Parametrul FOR este un sinonim pentru parametrul AFTER.) DUPĂ tipul declanșează sunt apelate după ce acțiunea care declanșează declanșatorul este executată și ÎN LOC DE tip declanșatoare sunt executate în locul acțiunii care declanșează declanșatorul. Declanșatoarele AFTER pot fi create numai pe tabele, în timp ce declanșatoarele ÎNLOCUIT DE pot fi create atât pe tabele, cât și pe vizualizări.

Parametrii INSERT, UPDATE și DELETE specifică acțiunea de declanșare. Acțiunea de declanșare se referă la instrucțiunea Transact-SQL care declanșează declanșatorul. Orice combinație a acestor trei instrucțiuni este permisă. Declarația DELETE nu este permisă dacă este utilizată opțiunea IF UPDATE.

După cum puteți vedea în sintaxa instrucțiunii CREATE TRIGGER, acțiunea (sau acțiunile) de declanșare sunt specificate în specificația AS sql_statement.

Motorul de baze de date vă permite să creați mai mulți declanșatori per tabel și per acțiune (INSERT, UPDATE și DELETE). În mod implicit, nu există o ordine specifică de execuție a mai multor declanșatori pentru o anumită acțiune de modificare.

Numai proprietarul bazei de date, administratorii DDL și proprietarul tabelului pe care este definit declanșatorul au permisiunea de a crea declanșatori pentru baza de date curentă. (Spre deosebire de permisiunile pentru alte tipuri de instrucțiuni CREATE, această permisiune nu este transferabilă.)

Modificarea structurii declanșatorului

Transact-SQL acceptă, de asemenea, declarația ALTER TRIGGER, care modifică structura declanșatorului. Această instrucțiune este de obicei folosită pentru a schimba corpul unui declanșator. Toate clauzele și parametrii instrucțiunii ALTER TRIGGER au același sens ca aceleași clauze și parametri ai instrucțiunii CREATE TRIGGER.

Pentru a elimina declanșatoarele din baza de date curentă, utilizați instrucțiunea DROP TRIGGER.

Utilizarea tabelelor virtuale șterse și inserate

Când creați o acțiune de declanșare, de obicei trebuie să specificați dacă se referă la o valoare de coloană înainte sau după ce aceasta este modificată de acțiunea care declanșează declanșatorul. Din acest motiv, două tabele virtuale denumite special sunt folosite pentru a testa efectul declarației care declanșează declanșatorul:

deleted - conține copii ale rândurilor șterse din tabel;

inserat - conține copii ale rândurilor inserate în tabel.

Structura acestor tabele este echivalentă cu structura tabelului pentru care este definit declanșatorul.

Tabelul a fost șters este utilizat dacă instrucțiunea CREATE TRIGGER specifică o clauză DELETE sau UPDATE și dacă instrucțiunea specifică o clauză INSERT sau UPDATE, atunci tabel introdus. Aceasta înseamnă că pentru fiecare instrucțiune DELETE executată într-o acțiune de declanșare, este creat un tabel șters. În mod similar, pentru fiecare instrucțiune INSERT executată într-o acțiune de declanșare, este creat un tabel inserat.

Instrucțiunea UPDATE este tratată ca o instrucțiune DELETE urmată de o instrucțiune INSERT. Prin urmare, pentru fiecare instrucțiune UPDATE executată într-o acțiune de declanșare, sunt create atât un tabel șters, cât și un tabel inserat (în această ordine).

Tabelele inserate și șterse sunt implementate folosind versiunea de rând, care a fost discutată în articolul anterior. Când o instrucțiune DML (INSERT, UPDATE sau DELETE) este executată pe un tabel cu declanșatoare adecvate, versiunile de rând sunt întotdeauna create pentru toate modificările aduse tabelului respectiv. Când un declanșator are nevoie de informații din tabelul șters, acesta accesează datele din magazinul de versiuni de rând. În cazul unui tabel inserat, declanșatorul accesează cele mai recente versiuni ale rândurilor.

Motorul de versiune de rând folosește baza de date a sistemului tempdb ca magazin de versiuni de rând. Din acest motiv, dacă o bază de date conține un număr mare de declanșatori utilizați frecvent, vă puteți aștepta la o creștere semnificativă a dimensiunii acelei baze de date de sistem.

Domenii de aplicare a declanșatorilor DML

Astfel de declanșatori sunt utilizați pentru a rezolva o varietate de probleme. În această secțiune, vom analiza mai multe utilizări ale declanșatorilor DML, în special declanșatoarele AFTER și INSTEAD OF.

DUPĂ declanșează

După cum știți deja, declanșatoarele AFTER sunt declanșate după ce acțiunea care declanșează declanșatorul este executată. Un declanșator AFTER este specificat folosind cuvântul cheie AFTER sau FOR. Declanșatoarele AFTER pot fi create numai pe tabele de bază. Declanșatoarele de acest tip pot fi utilizate pentru a efectua, printre altele, următoarele operații:

crearea unui jurnal al jurnalelor de acțiuni în tabelele bazei de date;

implementarea regulilor de afaceri;

aplicarea integrității referențiale.

Crearea unui jurnal de jurnal

În SQL Server, puteți efectua captarea datelor de modificare utilizând sistemul de capturare a datelor de modificare CDC. Această problemă poate fi rezolvată și folosind declanșatoarele DML. Exemplul de mai jos arată cum pot fi utilizați declanșatorii pentru a crea un jurnal al jurnalelor de activitate în tabelele bazei de date:

UTILIZAȚI SampleDb; /* Tabelul AuditBudget este folosit ca jurnal al jurnalelor de activitate în tabelul Proiect */ GO CREATE TABLE AuditBudget (ProjectNumber CHAR(4) NULL, UserName CHAR(16) NULL, Date DATETIME NULL, BudgetOld FLOAT NULL, BudgetNew FLOAT NULL) ; GO CREATE TRIGGER trigger_ModifyBudget ON Project AFTER UPDATE AS IF UPDATE(buget) BEGIN DECLARE @budgetOld FLOAT DECLARE @budgetNew FLOAT DECLARE @projectNumber CHAR(4) SELECT @budgetOld = (SELECT New Budget FROM deleted) =SELECT New FROM Buget inserat (SELECT) SELECT @projectNumber = (SELECT Number FROM deleted) INSERT INTO AuditBudget VALUES (@projectNumber, USER_NAME(), GETDATE(), @budgetOld, @budgetNew) END

Acest exemplu creează un tabel AuditBudget care stochează toate modificările în coloana Buget a tabelului Proiect. Modificările aduse acestei coloane vor fi scrise în acest tabel utilizând declanșatorul trigger_ModifyBudget.

Acest declanșator este declanșat pentru fiecare modificare a coloanei Buget folosind o instrucțiune UPDATE. Când acest declanșator este executat, valorile rândurilor de tabel șterse și inserate sunt alocate variabilelor corespunzătoare @budgetOld, @budgetNew și @projectNumber. Aceste valori atribuite, împreună cu numele de utilizator și data curenta, va fi apoi inserat în tabelul AuditBudget.

Acest exemplu presupune că doar un rând va fi actualizat la un moment dat. Prin urmare, acest exemplu este o simplificare a cazului obișnuit în care un declanșator gestionează actualizările pe mai multe rânduri. Dacă rulați următoarele instrucțiuni Transact-SQL:

atunci conținutul tabelului AuditBudget va fi astfel:

Implementarea regulilor de afaceri

Folosind declanșatoare, puteți crea reguli de afaceri pentru aplicații. Crearea unui astfel de declanșator este prezentată în exemplul de mai jos:

UTILIZAȚI SampleDb; -- Declanșatorul trigger_TotalBudget este un exemplu de utilizare a -- un declanșator pentru a implementa o regulă de afaceri GO CREATE TRIGGER trigger_TotalBudget ON Project AFTER UPDATE AS IF UPDATE (Buget) BEGIN DECLARE @sum_old1 FLOAT DECLARE @sum_old2 FLOAT DECLARE @sum_new FLOAT SELECT @sum_new = New (SELECT SUM(Buget) FROM introdus) SELECT @sum_old1 = (SELECT SUM(p.Buget) FROM proiect p WHERE p.Number NOT IN (SELECT d.Number FROM deleted d)) SELECT @sum_old2 = (SELECT SUM(Buget) FROM deleted) IF @sum_new > (@sum_old1 + @sum_old2) * 1.5 BEGIN PRINT „Buget neschimbat” ROLLBACK TRANZACȚIE END ELSE PRINT „Modificarea bugetului finalizată” END

Aici creați o regulă pentru a controla modificarea bugetelor proiectelor. Trigger_TotalBudget verifică fiecare modificare a bugetului și execută numai acele instrucțiuni UPDATE care măresc suma tuturor bugetelor cu cel mult 50%. În caz contrar, instrucțiunea UPDATE este anulată folosind o instrucțiune ROLLBACK TRANSACTION.

Aplicarea constrângerilor de integritate

Sistemele de management al bazelor de date utilizează două tipuri de constrângeri pentru a asigura integritatea datelor: constrângeri declarative, care sunt definite folosind instrucțiunile de limbaj CREATE TABLE și ALTER TABLE; constrângerile de integritate procedurală care sunt implementate prin intermediul declanșatorilor.

În situații normale, constrângerile declarative ar trebui utilizate pentru a asigura integritatea deoarece sunt susținute de sistem și nu necesită implementare de către utilizator. Utilizarea declanșatorilor este recomandată numai în cazurile în care nu există constrângeri declarative pentru asigurarea integrității.

Exemplul de mai jos arată cum să impuneți integritatea referențială folosind declanșatoare în tabelele Employee și Works_on:

UTILIZAȚI SampleDb; GO CREATE TRIGGER trigger_WorksonIntegrity ON Works_on AFTER INSERT, UPDATE AS IF UPDATE(EmpId) BEGIN IF (SELECT Employee.Id FROM Employee, inserat WHERE Employee.Id = inserted.EmpId) IS NULL BEGIN ROLLINTBACK/modificare Rândul nu a fost introdus ROLLBACK/modificare. " END ELSE PRINT "Rândul a fost inserat/modificat" END

Trigger_WorksonIntegrity din acest exemplu verifică integritatea referențială pentru tabelele Employee și Works_on. Aceasta înseamnă că fiecare modificare a coloanei Id din tabelul de referință Works_on este verificată și orice încălcare a acestei constrângeri nu va permite operațiunii să continue. (Același lucru este valabil și pentru inserarea de noi valori în coloana Id.) Instrucțiunea ROLLBACK TRANSACTION din al doilea bloc BEGIN derulează înapoi instrucțiunea INSERT sau UPDATE dacă o constrângere este încălcată pentru a asigura integritatea referențială.

În acest exemplu, declanșatorul verifică problemele de integritate referențială din primul și al doilea caz între tabelele Employee și Works_on. Și exemplul de mai jos arată un declanșator care verifică probleme de integritate referențială în al treilea și al patrulea caz între aceleași tabele (aceste cazuri au fost discutate în articolul „Transact-SQL - crearea de tabele”):

UTILIZAȚI SampleDb; GO CREATE TRIGGER trigger_RefintWorkson2 ON Employee DUPĂ ȘTERGERE, UPDATE AS IF UPDATE (Id) BEGIN IF (SELECT COUNT(*) FROM Works_on, șters WHERE Works_on.EmpId = deleted.Id) > 0 BEGIN ROLLBACK/TRANZACȚIA nu a fost inserat modificat " END ELSE PRINT "Rândul a fost inserat/modificat" END

Declanșatoare ÎN LOC DE

Declanșatorul ofertei ÎN LOC DEînlocuiește acțiunea corespunzătoare care a declanșat-o. Acest declanșator rulează după ce tabelele corespunzătoare introduse și șterse au fost create, dar înainte ca verificările de integritate sau orice alte acțiuni să fie efectuate.

În locul declanșatorilor pot fi creați atât pentru tabele, cât și pentru vizualizări. Când o instrucțiune Transact-SQL face referire la o vizualizare care are definit un declanșator ÎN LOC DE, sistemul de bază de date execută acel declanșator în loc să ia orice acțiune asupra oricărei tabele. Acest tip Declanșatorul folosește întotdeauna informațiile din tabelele inserate și șterse create pentru vizualizare pentru a genera orice instrucțiuni necesare pentru a crea evenimentul solicitat.

Valorile coloanei furnizate de declanșatorul ÎN LOC DE trebuie să îndeplinească anumite cerințe:

valorile nu pot fi setate pentru coloanele calculate;

valorile nu pot fi setate pentru coloanele tipului de date marca temporală;

valorile nu pot fi setate pentru coloanele cu proprietatea IDENTITY decât dacă IDENTITY_INSERT este setat la ON.

Aceste cerințe se aplică numai instrucțiunilor INSERT și UPDATE care fac referire la tabele de bază. O instrucțiune INSERT care face referire la vederi cu un declanșator INSTEAD OF trebuie să furnizeze valori pentru toate coloanele acelei vizualizări care nu permit valori nule. (Același lucru este valabil și pentru o instrucțiune UPDATE. O instrucțiune UPDATE care face referire la o vizualizare cu un declanșator INSTEAD OF trebuie să furnizeze valori pentru toate coloanele unei vederi care nu permite valori goaleși care este menționat în clauza SET.)

Exemplul de mai jos arată diferența de comportament la inserarea valorilor în coloanele calculate folosind un tabel și vizualizarea corespunzătoare:

UTILIZAȚI SampleDb; CREATE TABLE Comenzi (OrderId INT NOT NULL, Price MONEY NOT NULL, Quantity INT NOT NULL, OrderDate DATETIME NOT NULL, Total AS Preț * Cantitate, ShippedDate AS DATEADD (DAY, 7, orderdate)); GO CREATE VIEW view_AllOrders AS SELECT * FROM Orders; GO CREATE TRIGGER trigger_orders ON view_AllOrders ÎNCĂ A INSERĂ LA ÎNCEPT INSERT INTO Orders SELECTAȚI OrderId, Price, Quantity, OrderDate FROM inserted END

Acest exemplu folosește un tabel Comenzi care conține două coloane calculate. View_AllOrders conține toate rândurile acestui tabel. Această vizualizare este utilizată pentru a seta o valoare în coloana sa care se mapează la o coloană calculată din tabelul de bază pe care este creată vizualizarea. Acest lucru vă permite să utilizați un declanșator INSTEAD OF, care, în cazul unei instrucțiuni INSERT, este înlocuit cu un lot care inserează valori în tabelul de bază prin vizualizarea view_AllOrders. (O instrucțiune INSERT care accesează direct tabelul de bază nu poate seta o valoare unei coloane calculate.)

Declanșează primul și ultimul

Motorul de baze de date vă permite să creați mai mulți declanșatori pentru fiecare tabel sau vizualizare și pentru fiecare operație (INSERT, UPDATE și DELETE) asupra acestora. În plus, puteți specifica ordinea de execuție pentru mai multe declanșatoare definite pentru o anumită operație. Utilizarea unei proceduri de sistem sp_settriggerorder Puteți specifica că unul dintre declanșatoarele AFTER definite pentru un tabel va fi executat primul sau ultimul pentru fiecare acțiune procesată. Această procedură de sistem are un parametru @order căruia i se poate atribui una dintre cele trei valori:

first - indică faptul că declanșatorul este primul declanșator AFTER executat pentru a modifica acțiunea;

last - indică faptul că acest declanșator este ultimul declanșator AFTER executat pentru a iniția o acțiune;

none - indică faptul că nu există un ordin de execuție specific pentru declanșator. (Această valoare este de obicei folosită pentru a reseta ordinea de execuție setată anterior a declanșatorului ca prima sau ultima.)

Modificarea structurii unui declanșator folosind instrucțiunea ALTER TRIGGER inversează ordinea de execuție a declanșatorului (primul sau ultimul). Următorul exemplu arată utilizarea procedurii de sistem sp_settriggerorder:

UTILIZAȚI SampleDb; EXEC sp_settriggerorder @triggername = "trigger_ModifyBudget", @order = "primul", @stmttype="update"

Puteți defini doar un prim și numai un ultim declanșator AFTER pentru un tabel. Declanșatoarele AFTER rămase sunt executate într-o ordine nespecificată. Puteți afla ordinea în care este executat un declanșator folosind procedura de sistem sp_helptrigger sau funcțiile OBJECTPROPERTY.

Setul de rezultate returnat de procedura de sistem sp_helptrigger conține o coloană de ordine care specifică ordinea în care este executat declanșatorul specificat. Când apelați funcția objectproperty, al doilea parametru al acesteia este ExeclsFirstTrigger sau ExeclsLastTrigger, iar primul său parametru este întotdeauna numărul ID al obiectului bazei de date. Dacă proprietatea specificată în al doilea parametru este adevărată, funcția returnează valoarea 1.

Deoarece un declanșator INSTEAD OF se execută înainte ca modificările să fie aduse tabelului său, declanșatoarele de acest tip nu pot fi specificate să ruleze în prima sau ultima ordine.

Declanșatoarele DDL și aplicațiile acestora

Anterior, ne-am uitat la declanșatoarele DML, care specifică acțiunea pe care o întreprinde serverul atunci când un tabel este modificat printr-o instrucțiune INSERT, UPDATE sau DELETE. Motorul de baze de date vă permite, de asemenea, să definiți declanșatori pentru instrucțiunile DDL, cum ar fi CREATE DATABASE, DROP TABLE și ALTER TABLE. Declanșatoarele pentru instrucțiunile DDL au următoarea sintaxă:

CREATE TRIGGER trigger_name ON (ALL SERVER | DATABASE ) (FOR | AFTER ) ( event_group | event_type | LOGON) AS (lot | EXTERNAL NAME method_name) Convenții de sintaxă

După cum puteți vedea din sintaxa lor, declanșatorii DDL sunt creați în același mod ca declanșatorii DML. Și pentru a modifica și șterge aceste declanșatoare, se folosesc aceleași instrucțiuni ALTER TRIGGER și DROP TRIGGER ca și pentru declanșatoarele DML. Prin urmare, această secțiune discută numai acei parametri ai instrucțiunii CREATE TRIGGER care sunt noi pentru sintaxa declanșatorului DDL.

Primul pas atunci când definiți un declanșator DDL este să specificați domeniul de aplicare al acestuia. clauza DATABASE specifică baza de date curentă ca domeniul de aplicare al declanșatorului DDL și Oferta TOATE SERVERUL- serverul curent.

După specificarea domeniului unui declanșator DDL, trebuie să specificați cum se va declanșa declanșatorul ca răspuns la execuția uneia sau mai multor instrucțiuni DDL. Parametrul event_type specifică instrucțiunea DDL care, atunci când este executată, declanșează declanșatorul, iar parametrul alternativ event_group specifică grupul de evenimente Transact-SQL. Un declanșator DDL se declanșează după executarea oricărui eveniment Transact-SQL specificat în parametrul event_group. Cuvânt cheie LOGIN indică declanșatorul de intrare.

Pe lângă asemănările dintre declanșatorii DML și DDL, există și câteva diferențe între ele. Principala diferență dintre aceste două tipuri de declanșatoare este că un declanșator DDL poate fi acoperit la întreaga bază de date sau chiar la întregul server, mai degrabă decât la un singur obiect. În plus, declanșatoarele DDL nu acceptă declanșatoarele ÎN LOC DE. După cum probabil ați ghicit, declanșatoarele DDL nu necesită tabele inserate și șterse, deoarece acești declanșatori nu modifică conținutul tabelelor.

Următoarele subsecțiuni discută în detaliu cele două forme de declanșatori DDL: declanșatori la nivel de bază de date și declanșatori la nivel de server.

Declanșatoare DDL la nivel de bază de date

Exemplul de mai jos arată cum puteți implementa un declanșator DDL al cărui domeniu de aplicare se extinde la baza de date curentă:

UTILIZAȚI SampleDb; GO CREATE TRIGGER trigger_PreventDrop PE BAZĂ DE DATE PENTRU DROP_TRIGGER AS PRINT „Înainte de a putea șterge un declanșator, trebuie să dezactivați „trigger_PreventDrop”” ROLLBACK

Declanșatorul din acest exemplu împiedică orice utilizator să ștergă orice declanșator din baza de date SampleDb. Clauza DATABASE specifică faptul că trigger_PreventDrop este un declanșator la nivel de bază de date. Cuvânt cheie DROP_TRIGGER specifică un tip de eveniment predefinit care împiedică ștergerea oricărui declanșator.

Declanșatoare DDL la nivel de server

Declanșatoarele la nivel de server răspund la evenimentele de la nivelul serverului. Un declanșator la nivel de server este creat prin utilizarea clauzei ALL SERVER din instrucțiunea CREATE TRIGGER. În funcție de acțiunea pe care o efectuează declanșatorul, există două tipuri diferite de declanșatoare la nivel de server: declanșatoare DDL obișnuite și declanșatoare de conectare. Declanșarea declanșatorilor DDL obișnuiți se bazează pe evenimente de instrucțiuni DDL, în timp ce declanșarea declanșatorilor de intrare se bazează pe evenimente de intrare.

Exemplul de mai jos demonstrează crearea unui declanșator la nivel de server care este un declanșator de conectare:

USE master; GO CREATE LOGIN loginTest WITH PASSWORD = "12345!", CHECK_EXPIRATION = ON; ACORDAȚI VEZI STAREA SERVERULUI PENTRU LoginTest; CREATE TRIGGER trigger_ConnectionLimit PE TOATE SERVERUL CU EXECUTE CA „loginTest” PENTRU LOGIN AS BEGIN IF ORIGINAL_LOGIN()= „loginTest” ȘI (SELECTARE COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process WHERE is_user_process = original_login TACK_BOLL) ; SFÂRŞIT;

Aici, este creat mai întâi testul de conectare SQL Server, care este apoi utilizat într-un declanșator la nivel de server. Din acest motiv, această autentificare necesită permisiunea VIEW SERVER STATE, care îi este acordată prin instrucțiunea GRANT. După aceasta, trigger_ConnectionLimit este creat. Acest declanșator este un declanșator de conectare, așa cum este indicat de cuvântul cheie LOGON.

Folosind View sys.dm_exec_sessions se face o verificare pentru a vedea dacă o sesiune a fost deja stabilită folosind loginTest login. Dacă sesiunea a fost deja stabilită, instrucțiunea ROLLBACK este executată. Prin urmare, autentificarea loginTest poate stabili doar o sesiune la un moment dat.

Declanșatoarele și CLR

La fel ca procedurile stocate și funcțiile definite de utilizator, declanșatoarele pot fi implementate folosind Common Language Runtime (CLR). Declanșatoarele din CLR sunt create în trei pași:

Codul sursă de declanșare este generat în C# sau Visual Basic, care este apoi compilat folosind compilatorul corespunzător în cod obiect.

Codul obiect este procesat cu instrucțiunea CREATE ASSEMBLY, creând un fișier executabil corespunzător.

Instrucțiunea CREATE TRIGGER creează un declanșator.

Următoarele exemple demonstrează toți cei trei pași ai creării unui declanșator CLR. Mai jos este un exemplu de cod sursă al programului C# pentru declanșatorul din primul exemplu din articol. Înainte de a crea un declanșator CLR în următoarele exemple, trebuie să eliminați mai întâi declanșatorul trigger_PreventDrop și apoi să eliminați declanșatorul trigger_ModifyBudget, folosind instrucțiunea DROP TRIGGER în ambele cazuri.

Utilizarea sistemului; folosind System.Data.SqlClient; folosind Microsoft.SqlServer.Server; Declanșatoare de clasă publică ( public static void ModifyBudget() ( SqlTriggerContext context = SqlContext.TriggerContext; if (context.IsUpdatedColumn(2))) // Coloana buget ( float budget_old; float budget_new; string project_number; SqlConnection conexiune = new SqlConnection conexiune =true"); conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECTARE Buget FROM DELETED"; budget_old = (float)Convert.ToDouble(cmd.ExecuteScalar()); cmd.CommandText = "SELECT Budget FROM INSERTED"; budget_new = (float)Convert.ToDouble(cmd.ExecuteScalar() = "SELECT Number FROM DELETED" = Convert.ToString(cmd.ExecuteScalar()); @"INSERT INTO AuditBudget (@projectNumber, USER_NAME(), GETDATE(), @budgetOld, @budgetNew)"; cmd.Parameters.AddWithValue("@projectNumber", cmd.Parameters.AddWithValue("@budgetOld)); , buget_vechi); cmd.Parameters.AddWithValue("@budgetNew", cmd.ExecuteNonQuery());

Spațiul de nume Microsoft.SQLServer.Server conține toate clasele de client de care ar putea avea nevoie un program C#. Clase SqlTriggerContextȘi SqlFunction sunt membri ai acestui spațiu de nume. În plus, spațiul de nume System.Data.SqlClient conține clasele SqlConnection și SqlCommand, care sunt utilizate pentru a stabili conexiunea și interacțiunea între client și serverul bazei de date. Conexiunea se stabilește folosind șirul de conexiune „context connection = true”.

În continuare, este definită clasa Triggers, care este folosită pentru implementarea declanșatorilor. Metoda ModifyBudget() implementează un declanșator cu același nume. Instanța de context a clasei SqlTriggerContext permite unei aplicații să acceseze tabelul virtual creat atunci când se execută declanșatorul. Acest tabel stochează datele care au determinat declanșarea declanșării. Metoda IsUpdatedColumn() a clasei SqlTriggerContext vă permite să aflați dacă coloana specificată a tabelului a fost actualizată.

Acest program conține alte două clase importante: SqlConnection și SqlCommand. O instanță a clasei SqlConnection este de obicei utilizată pentru a stabili o conexiune la o bază de date, iar o instanță a clasei SqlCommand vă permite să executați instrucțiuni SQL.

Acest exemplu de program poate fi compilat folosind compilatorul csc, care este încorporat în Visual Studio. Următorul pas este să adăugați o referință la ansamblul compilat în baza de date:

UTILIZAȚI SampleDb; CREAȚI ANSABLUL CLRStoredProcedures DIN „D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll” CU PERMISSION_SET = SAFE

Instrucțiunea CREATE ASSEMBLY ia cod gestionat ca intrare și creează un obiect corespunzător din care să creeze un declanșator CLR. Clauza WITH PERMISSION_SET din exemplu specifică că permisiunile de acces sunt setate la SAFE.

În cele din urmă, în exemplul de mai jos, un trigger_modify_budget este creat folosind instrucțiunea CREATE TRIGGER:

UTILIZAȚI SampleDb; CREATE TRIGGER trigger_modify_budget PE Proiect DUPĂ ACTUALIZARE CA NUME EXTERN CLRStoredProcedures.Triggers.ModifyBudget

Instrucțiunea CREATE TRIGGER din exemplu diferă de aceeași instrucțiune din exemplele anterioare prin faptul că conține Parametrul NUME EXTERN. Acest parametru specifică faptul că codul este generat Mediul CLR. Numele din acest parametru este format din trei părți. Prima parte specifică numele ansamblului corespunzător (CLRStoredProcedures), a doua specifică numele clasei publice definite în exemplul de mai sus (Triggers), iar a treia specifică numele metodei definite în această clasă (ModifyBudget).