Trigerid kahe seotud tabelite põhjal

1. Tabeli Loomine: Linnad

Esimese sammuna loome tabeli linnad, kuhu salvestame linnade andmed. See tabel sisaldab kolme peamist veergu: linnID, mis on unikaalne identifikaator, linnanimi, mis näitab linna nime, ja rahvaarv, mis näitab, kui palju inimesi linnas elab.

SQL
--Loome tabeli linnad
Create table linnad(
linnID int identity(1,1) PRIMARY KEY,
linnanimi varchar(15),
rahvaarv int);

2. Tabeli Loomine: Logi

Teiseks loome tabeli logi, kuhu salvestame kõik toimingud, mis on seotud linnadega, sealhulgas lisamine, kustutamine ja uuendamine. Logimise kaudu saame jälgida, kes ja millal tegi teatud muudatusi andmebaasis.

SQL
--Loome tabeli logi
Create table logi(
id int identity(1,1) PRIMARY KEY,
aeg DATETIME,
toiming  varchar(100),
andmed text,
kasutaja varchar(200));

3. Tabeli Loomine: Maakond

Järgmiseks loome tabeli maakond, mis sisaldab maakondade nimesid ja nende unikaalseid identifikaatoreid. See aitab meil siduda linnad õigete maakondadega.

SQL
--loome tabeli maakond
CREATE TABLE maakond(
    maakondID int Primary KEY identity(1,1),
    maakond varchar(100) UNIQUE);
    
INSERT INTO maakond(maakond)
VALUES ('Harjumaa');
INSERT INTO maakond(maakond)
VALUES ('Pärnumaa');

4. Seoste Loomine: Linnad ja Maakond

Seejärel lisame linnad tabelisse maakondID, et luua seos linnade ja maakondade vahel. See seos võimaldab meil teha päringuid, mis seovad linnad vastava maakonnaga.

SQL
--seoste loomine linnad--maakonad
ALTER TABLE linnad ADD maakondID int;
ALTER TABLE linnad ADD CONSTRAINT fk_maakond
FOREIGN KEY (maakondID) References maakond(maakondID)

5. Triger: Linnade Lisamine

Loome triki linnaLisamine, mis automaatselt logib kõik linnade lisamise toimingud. Kui uus linn lisatakse, salvestatakse selle nimi, maakond ja lisamise aeg logi tabelisse.

SQL
--trigger linnade lisamiseks
Create trigger linnaLisamine
On linnad 
FOR insert
As 
INSERT into logi(kasutaja, aeg, toiming,andmed)
SELECT USER, GETDATE(), 'linn on lisatud',
CONCAT(l.linnanimi, ', ', m.maakond)
FROM linnad l
INNER JOIN maakond m
ON m.maakondID=l.maakondID;

--kontrollimiseks
INSERT INTO linnad(linnanimi, rahvaarv, maakondID)
values('Tallinn', 436863, 1);

select * from linnad;
select* from logi;

6. Triger: Linnade Kustutamine

Loome triki linnaKustutamine, mis logib linnade kustutamise toimingud.

SQL
--trigger linnade kustutamiseks
Create trigger linnaKustutamine
On linnad 
FOR DELETE
As 
INSERT into logi(kasutaja, aeg, toiming,andmed)
SELECT USER, GETDATE(), 'linn on Kustutatud',
CONCAT(deleted.linnanimi, ', ', m.maakond)
FROM deleted
INNER JOIN maakond m
ON deleted.maakondID=m.maakondID;

--kontrollimiseks
delete from linnad
WHERE linnID = 1;

select * from linnad
select * from logi

7. Triger: Linnade Uuendamine

Loome triki linnaUuendamine, mis logib linnade uuendamise toimingud.

SQL
--Trigger linnade uuendamiseks
Create trigger linnaUuendamine
On linnad 
FOR UPDATE
As 
INSERT into logi(kasutaja, aeg, toiming,andmed)
SELECT USER, GETDATE(), 'linn on uuendatud',
CONCAT(
'Vanad andmed - ',deleted.linnanimi, ', ', m1.maakond,
'Uuendatud andmed - ', inserted.linnanimi, ', ', m2.maakond
)
FROM deleted
Inner join inserted ON deleted.linnID=inserted.linnID
INNER JOIN maakond m1 ON deleted.maakondID=m1.maakondID
INNER JOIN maakond m2 ON inserted.maakondID=m2.maakondID

--kontroll
update linnad set linnanimi='Tallinn-Väike', maakondID=2
WHERE linnID = 2;

select * from linnad
select * from logi

Kokkuvõte

SQL-i trikkide kasutamine võimaldab meil automaatselt logida andmebaasis toimuvaid muudatusi, mis on hädavajalik andmete jälgimiseks ja haldamiseks. Trikid aitavad vähendada vigade arvu ning suurendavad andmebaasi usaldusväärsust. Nende näidete abil saad alustada oma SQL-i projektiga ja luua oma andmebaasi struktuur, mis on hästi dokumenteeritud ja hallatav.

  1. SQL Dokumentatsioon:

This post is also available in ru_RU.