Trigger
Triger on protsess, mille abil selles salvestatud tegevused täidetakse automaatselt.
Töötamine vallandajaga SQLis:
Töötamine vallandajaga XAMPPis:
Luua andmebaas ja tabelid (meil on autod ja logi (kus salvestatakse tegevused tabelitega, mis täidetakse automaatselt trigerite abil)).
CREATE DATABASE AutoRegister
CREATE TABLE autod(
autoID int PRIMARY KEY IDENTITY(1,1),
registreerimismark varchar(6) NOT NULL,
mark varchar(30),
esmaneRegistreerimine DATE,
kere varchar(30),
varvus varchar(30),
voimsuskW int,
kutus varchar(30),
kaigukast varchar(30),
omanik varchar(30));
CREATE TABLE logi(
id int PRIMARY KEY IDENTITY(1,1),
aeg DATETIME,
toiming varchar(100),
andmed TEXT,
kasutaja varchar(30));
CREATE TABLE autod(
autoID int PRIMARY KEY AUTO_INCREMENT,
registreerimismark varchar(6) NOT NULL,
mark varchar(30),
esmaneRegistreerimine DATE,
kere varchar(30),
varvus varchar(30),
voimsuskW int,
kutus varchar(30),
kaigukast varchar(30),
omanik varchar(30));
CREATE TABLE logi(
id int PRIMARY KEY AUTO_INCREMENT,
aeg DATETIME,
toiming varchar(100),
andmed TEXT,
kasutaja varchar(30));
Loo triger lisatud andmete jälgimiseks:
CREATE TRIGGER autoLisamine
ON autod
FOR INSERT
AS
INSERT INTO logi(aeg, toiming, andmed, kasutaja)
SELECT GETDATE(),
'uus auto on lisatud',
CONCAT ('registreerimismark: ', inserted.registreerimismark, ' / mark: ', inserted.mark, ' / omanik: ', inserted.omanik),
SUSER_NAME()
FROM inserted;
Kontrollimine:
INSERT INTO autod (registreerimismark, mark, esmaneRegistreerimine, kere, varvus, voimsuskW, kutus, kaigukast, omanik)
VALUES
('123ABC', 'Toyota Corolla', '2018-05-12', 'Sedaan', 'Sinine', 85, 'Bensiin', 'Automaat', 'Jaan Tamm'),
('456DEF', 'Volkswagen Golf', '2020-08-25', 'Luukpära', 'Must', 96, 'Diisel', 'Manuaal', 'Mari Mets'),
('789GHI', 'BMW X5', '2019-03-17', 'Maastur', 'Valge', 195, 'Bensiin', 'Automaat', 'Peeter Põld'),
('321JKL', 'Audi A4', '2017-11-30', 'Sedaan', 'Hall', 110, 'Diisel', 'Automaat', 'Laura Kask'),
('654MNO', 'Honda Civic', '2021-06-14', 'Luukpära', 'Punane', 90, 'Bensiin', 'Manuaal', 'Karl Kivi'),
('987PQR', 'Ford Focus', '2016-09-05', 'Universaal', 'Sinine', 74, 'Bensiin', 'Automaat', 'Anna Saar'),
('135STU', 'Mercedes-Benz C200', '2019-12-20', 'Sedaan', 'Must', 150, 'Diisel', 'Automaat', 'Erik Leht'),
('246VWX', 'Škoda Octavia', '2015-04-08', 'Luukpära', 'Roheline', 77, 'Diisel', 'Manuaal', 'Piret Maasik'),
('357YZA', 'Nissan Qashqai', '2022-01-15', 'Maastur', 'Hall', 103, 'Hübriid', 'Automaat', 'Toomas Ruut'),
('468BCD', 'Tesla Model 3', '2023-07-10', 'Sedaan', 'Valge', 283, 'Elektri', 'Automaat', 'Kati Kask');
SELECT * FROM autod;
SELECT * FROM logi;
Lõpptulemus:
Kontrollimine:
Lõpptulemus:
Looge triger, et jälgida uuendatud andmeid:
CREATE TRIGGER autoUuendamine
ON autod
FOR UPDATE
AS
INSERT INTO logi(aeg, toiming, andmed, kasutaja)
SELECT
GETDATE(),
'andmete uuendamine',
CONCAT('Vanad andmed: registreerimismark:', deleted.registreerimismark, ', mark: ', deleted.mark, ', esmane registreerimine: ', deleted.esmaneRegistreerimine, ', kere: ', deleted.kere, ', varvus: ', deleted.varvus, ', voimsus (kW)', deleted.voimsuskW, ', kutus: ', deleted.kutus, ', kaigukast: ', deleted.kaigukast, ', omanik: ', deleted.omanik, ' / Uued andmed: registreerimismark: ', inserted.registreerimismark, ', mark: ', inserted.mark, ', esmane registreerimine: ', inserted.esmaneRegistreerimine, ', kere: ', inserted.kere, ', varvus: ', inserted.varvus, ', voimsus (kW)', inserted.voimsuskW, ', kutus: ', inserted.kutus, ', kaigukast: ', inserted.kaigukast, ', omanik: ', inserted.omanik),
SUSER_NAME()
FROM deleted
INNER JOIN inserted
ON deleted.autoID=inserted.autoID
Kontrollimine:
UPDATE autod
SET omanik='Anna Oleks'
WHERE autoID=3;
SELECT * FROM autod;
SELECT * FROM logi;
Lõpptulemus:
Kontrollimine:
UPDATE autod
SET omanik='Anna Oleks'
WHERE autoID=3;
Lõpptulemus:
Loo päästik kustutatud andmete jälgimiseks:
CREATE TRIGGER autoKustutamine
ON autod
FOR DELETE
AS
INSERT INTO logi(aeg, toiming, andmed, kasutaja)
SELECT
GETDATE(),
'andmete kustutamine',
CONCAT ('registreerimismark: ', deleted.registreerimismark, ' / mark: ', deleted.mark, ' / omanik: ', deleted.omanik),
SUSER_NAME()
FROM deleted;
Kontrollimine:
DELETE FROM autod
WHERE autoID=7;
SELECT * FROM autod;
SELECT * FROM logi;
Lõpptulemus:
Kontrollimine:
DELETE FROM autod
WHERE autoID=7;
Lõpptulemus:
Trigerid SQLis kahe seotud tabeli põhjal.
Lisame tabeli
create table trahvid(
trahvID int primary key identity(1,1),
autoID int,
trahviKuupaev date,
tyyp varchar(50),
summa decimal(10,2),
tasutud varchar(3) not null);
create table trahvid(
trahvID int primary key AUTO_INCREMENT,
autoID int,
trahviKuupaev date,
tyyp varchar(50),
summa decimal(10,2),
tasutud varchar(3) not null);
Lisame FOREIGN KEY
ALTER TABLE trahvid ADD CONSTRAINT fk_autod
FOREIGN KEY (autoID) References autod(autoID)
ALTER TABLE trahv ADD CONSTRAINT fk_autod
FOREIGN KEY (autoID) References autod(autoID)
Lisame andmed tabelisse
INSERT INTO trahvid (autoID, trahviKuupaev, tyyp, summa, tasutud)
VALUES
(1, '2024-01-15', 'Kiiruse ületamine', 120.50, 'ei'),
(2, '2024-02-05', 'Vale parkimine', 50.00, 'jah'),
(3, '2024-03-10', 'Punase tule eiramine', 200.00, 'ei'),
(4, '2024-03-20', 'Turvavöö mittekasutamine', 40.00, 'jah'),
(5, '2024-04-01', 'Mobiili kasutamine sõidu ajal', 80.00, 'ei'),
(1, '2024-04-05', 'Vale möödasõit', 150.00, 'ei'),
(2, '2024-04-10', 'Dokumentide puudumine', 60.00, 'jah'),
(3, '2024-04-15', 'Alkoholi joove', 500.00, 'ei'),
(4, '2024-04-20', 'Kiiruse ületamine', 90.00, 'jah'),
(5, '2024-04-25', 'Jalgratturi mitteandmine teed', 70.00, 'ei');
SELECT * FROM autod;
SELECT * FROM trahvid;
INSERT INTO trahv (autoID, trahviKuupaev, tyyp, summa, tasutud)
VALUES
(1, '2024-01-15', 'Kiiruse ületamine', 120.50, 'ei'),
(2, '2024-02-05', 'Vale parkimine', 50.00, 'jah'),
(3, '2024-03-10', 'Punase tule eiramine', 200.00, 'ei'),
(4, '2024-03-20', 'Turvavöö mittekasutamine', 40.00, 'jah'),
(5, '2024-04-01', 'Mobiili kasutamine sõidu ajal', 80.00, 'ei'),
(1, '2024-04-05', 'Vale möödasõit', 150.00, 'ei'),
(2, '2024-04-10', 'Dokumentide puudumine', 60.00, 'jah'),
(3, '2024-04-15', 'Alkoholi joove', 500.00, 'ei'),
(4, '2024-04-20', 'Kiiruse ületamine', 90.00, 'jah'),
(5, '2024-04-25', 'Jalgratturi mitteandmine teed', 70.00, 'ei');
Triger INSERT
CREATE TRIGGER trahvilisamine
ON trahvid
FOR INSERT
AS INSERT INTO logi(aeg, toiming, andmed, kasutaja)
SELECT
GETDATE(),
'on tehtud INSERT',
CONCAT('Trahvide tabel: Auto: ', a.registreerimismark, ', Kuupäev: ', inserted.trahviKuupaev, ', Tüüp: ', inserted.tyyp, ', Summa: ', inserted.summa, ', Tasutud: ', inserted.tasutud),
SUSER_NAME()
FROM inserted
INNER JOIN autod a
ON a.autoID=inserted.autoID;
Kontrollimine:
INSERT INTO trahvid (autoID, trahviKuupaev, tyyp, summa, tasutud)
VALUES
(6, '2024-05-05', 'Kiiruse ületamine', 110.00, 'ei'),
(2, '2024-05-12', 'Vale parkimine', 45.00, 'jah'),
(8, '2024-05-18', 'Punase tule eiramine', 180.00, 'ei'),
(9, '2024-05-22', 'Turvavöö mittekasutamine', 35.00, 'jah'),
(10, '2024-06-01', 'Mobiili kasutamine sõidu ajal', 75.00, 'ei');
SELECT * FROM autod;
SELECT * FROM trahvid;
SELECT * FROM logi;
Lõpptulemus:
Kontrollimine:
INSERT INTO trahv (autoID, trahviKuupaev, tyyp, summa, tasutud)
VALUES
(6, '2024-05-05', 'Kiiruse ületamine', 110.00, 'ei'),
(7, '2024-05-12', 'Vale parkimine', 45.00, 'jah')
Lõpptulemus:
Triger UPDATE
CREATE TRIGGER trahviuuendamine
ON trahvid
FOR UPDATE
AS INSERT INTO logi(aeg, toiming, andmed, kasutaja)
SELECT
GETDATE(),
'on tehtud UPDATE',
CONCAT('vanad andmed: ', a1.registreerimismark,', ', a1.omanik, ', ', deleted.trahviKuupaev, ', ', deleted.tyyp,', ', deleted.summa, ', ', deleted.tasutud, 'uued andmed: ', a2.registreerimismark,', ', a2.omanik, ', ', inserted.trahviKuupaev, ', ', inserted.tyyp,', ', inserted.summa, ', ', inserted.tasutud),
SUSER_NAME()
FROM deleted
INNER JOIN inserted ON deleted.trahvID=inserted.trahvID
INNER JOIN autod a1 ON a1.autoID=deleted.autoID
INNER JOIN autod a2 ON a2.autoID=inserted.autoID;
Kontrollimine:
UPDATE trahvid SET autoID=5
WHERE trahvID=1;
SELECT * FROM autod;
SELECT * FROM trahvid;
SELECT * FROM logi;
Lõpptulemus:
Kontrollimine:
UPDATE trahv SET autoID=5
WHERE trahvID=2;
Lõpptulemus:
Triger DELETE
CREATE TRIGGER trahvikustutamine
ON trahvid
FOR DELETE
AS INSERT INTO logi(aeg, toiming, andmed, kasutaja)
SELECT
GETDATE(),
'on tehtud DELETE',
CONCAT('Trahvide tabel: Auto: ', a.registreerimismark, ', Kuupäev: ', deleted.trahviKuupaev, ', Tüüp: ', deleted.tyyp, ', Summa: ', deleted.summa, ', Tasutud: ', deleted.tasutud),
SUSER_NAME()
FROM deleted
INNER JOIN autod a
ON a.autoID=deleted.autoID;
Kontrollimine:
DELETE FROM trahvid
WHERE trahvID=6;
SELECT * FROM trahvid;
SELECT * FROM logi;
Lõpptulemus:
Kontrollimine:
DELETE FROM trahv
WHERE trahvID=3;
Lõpptulemus:
Kasutaja loomine:
Õiguste andmine:
GRANT SELECT, INSERT, UPDATE, DELETE ON autod TO Kasutaja;
GRANT SELECT, INSERT, UPDATE, DELETE ON trahvid TO Kasutaja;
DENY ALTER ON autod TO Kasutaja;
DENY ALTER ON trahvid TO Kasutaja;
DENY SELECT, INSERT, UPDATE, DELETE ON logi TO Kasutaja;
Kasutaja kontost logi tabeli vaatamine:
select*from logi
Kasutaja kontost trahvide lisamine:
INSERT INTO trahvid (autoID, trahviKuupaev, tyyp, summa, tasutud)
VALUES
(3, '2024-03-10', 'Punase tule eiramine', 200.00, 'ei'),
(4, '2024-03-20', 'Turvavöö mittekasutamine', 40.00, 'jah'),
(5, '2024-04-01', 'Mobiili kasutamine sõidu ajal', 80.00, 'ei'),
(1, '2024-04-05', 'Vale möödasõit', 150.00, 'ei')
Kasutaja kontost trahvide uuendamine:
UPDATE trahvid SET autoID=9
WHERE trahvID=1;
Kasutaja kontost trahvide kustutamine:
DELETE FROM trahvid
WHERE trahvID=4;
Root kontost logi tabeli vaatamine:
SELECT*FROM logi
Õiguste andmine:
Kasutaja kontost tabelite vaatamine:
Kasutaja kontost trahvide lisamine:
Kasutaja kontost trahvide uuendamine:
Kasutaja kontost trahvide kustutamine:
Root kontost logi tabeli vaatamine:
