SQL triggerid on spetsiaalsed andmebaasi objektid, mis käivituvad automaatselt, kui toimub teatud sündmus (nt INSERT, UPDATE või DELETE). SQL Triggerite loomine aitab automatiseerida protsesse, tagada andmete terviklikkust ja rakendada äri loogikat otse andmebaasis.
SQL Server
Loome andmebaasi ja kaks tabelit
Create database trigerTARgv24;
USE trigerTARgv24;
Create table linnad(
linnID int PRIMARY KEY IDENTITY (1,1),
linnanimi varchar(15) NOT NULL,
rahvaarv int);
Create table logi(
id int PRIMARY KEY IDENTITY (1,1),
aeg DATETIME,
toiming varchar(100),
andmed TEXT
)
Trigger lisatud kirjeid jälgimiseks tabelis “linnad” – INSERT
Jälgib andmete sisestamine tabelis linnad ja teeb vastava kirje tabelis logi
CREATE TRIGGER linnaLisamine
ON linnad --tabelinimi, mis on vaja jälgida
FOR INSERT
AS
INSERT INTO logi(aeg, toiming, andmed)
SELECT
GETDATE(), --aeg
'on tehtud INSERT käsk', --toiming
inserted.linnanimi --andmed
FROM inserted;
Trigeri tegevuse kontroll
INSERT INTO linnad(linnanimi, rahvaarv)
VALUES ('Tallinn', 600000);
SELECT * FROM linnad;
SELECT * FROM logi;
- SQL Triggeri Uuendamine (Muutmine)
- Leiame vajaliku tabeli andmebaasis
- Avame Triggers kausta
- Paremklõpsame soovitud triggeril
- Valime Modify (Muuda).
See avab dialoogiboksi, kus me saame teha vajalikud muudatused ja salvestada need.
USE [trigerTARgv24]
GO
/****** Object: Trigger [dbo].[linnaLisamine] Script Date: 19.03.2025 09:04:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[linnaLisamine]
ON [dbo].[linnad] --tabelinimi, mis on vaja jälgida
FOR INSERT
AS
INSERT INTO logi(aeg, toiming, andmed)
SELECT
GETDATE(), --aeg
'on tehtud INSERT käsk', --toiming
CONCAT('linn: ', inserted.linnanimi, ', elanike arv: ', inserted.rahvaarv) --andmed
FROM inserted;
Tõsta esile ALTER TRIGGER –> Execute –> Käsk täidetud
Andmete sisestamine tabelisse “logi” on muutunud.
Tools –> Options –> Designers –> Table and Database Designers –> Eemaldada linnuke valikust “Prevent saving changes that require table re-creation”
Lisame välja “kasutaja” tabelisse ja salvestame.
Muudame triggeri.
SUSER_NAME on SQL funktsioon, mis tagastab hetkel sisse logitud kasutaja nime.
ALTER TRIGGER [dbo].[linnaLisamine]
ON [dbo].[linnad] --tabelinimi, mis on vaja jälgida
FOR INSERT
AS
INSERT INTO logi(kasutaja, aeg, toiming, andmed)
SELECT
SUSER_NAME(), --USER
GETDATE(), --aeg
'on tehtud INSERT käsk', --toiming
CONCAT('linn: ', inserted.linnanimi, ', elanike arv: ', inserted.rahvaarv) --andmed
FROM inserted;
Kontrollime: Lisab kasutaja, kes andmed tabelisse sisestas.
Trigger eemaldatud kirjeid jälgimiseks tabelis “linnad” – DELETE
CREATE TRIGGER linnaKustutamine
ON linnad --tabelinimi, mis on vaja jälgida
FOR DELETE
AS
INSERT INTO logi(kasutaja, aeg, toiming, andmed)
SELECT
SUSER_NAME(), --USER
GETDATE(), --aeg
'on tehtud DELETE käsk', --toiming
CONCAT('linn: ', deleted.linnanimi, ', elanike arv: ', deleted.rahvaarv) --andmed
FROM deleted;
Kontrollimine
--kontroll
DELETE FROM linnad
WHERE linnID=1;
SELECT * FROM linnad;
SELECT * FROM logi;
Trigger muudetud kirjeid jälgimiseks tabelis “linnad” – UPDATE
CREATE TRIGGER linnaUuendamine
ON linnad --tabelinimi, mis on vaja jälgida
FOR UPDATE
AS
INSERT INTO logi(kasutaja, aeg, toiming, andmed)
SELECT
SUSER_NAME(),
GETDATE(),
'on tehtud UPDATE käsk',
CONCAT('vanad andmed -linn: ', deleted.linnanimi,
', elanike arv: ', deleted.rahvaarv,
'uued andmed -linn: ', inserted.linnanimi,
', elanike arv: ', inserted.rahvaarv)
FROM deleted
INNER JOIN inserted
ON deleted.linnID=inserted.linnID;
Kontrollimine
XAMPP
Loome trigger’eid XAMPP-is (mis kasutab MySQL või MariaDB andmebaasi) järgides järgmisi samme. Näiteks teeme SQL triggeri, mis logib iga linnade lisamise või kustutamise andmed.
Create table linnad(
linnID int Auto_increment PRIMARY KEY,
linnanimi varchar(15),
rahvaarv int);
Create table logi(
id int Auto_increment PRIMARY KEY,
aeg DATETIME,
toiming varchar(100),
andmed TEXT,
kasutaja varchar(50)
)
Trigger lisatud kirjeid jälgimiseks tabelis “linnad” – INSERT
Заполнить таблицу Linnad и проверяем таблицу Logi
Trigger muudetud kirjeid jälgimiseks tabelis “linnad” – UPDATE
Проверка:
Trigger eemaldatud kirjeid jälgimiseks tabelis “linnad” – DELETE
Проверка:
Kombineerime INSERT ja DELETE triggerid
See SQL trigger linnaLisamineJaKustutamine salvestab logi iga kord, kui linnade tabelis lisatakse uus linn või kustutatakse olemasolev linn. Trigger käivitub pärast INSERT või DELETE toimingut ja salvestab logisse andmed.
Kood on tehtud selle allika baasil.
CREATE TRIGGER linnaLisamineJaKustutamine
ON linnad
AFTER INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO logi(kasutaja, aeg, toiming, andmed)
SELECT
SUSER_NAME(),
GETDATE(),
'INSERT',
CONCAT('linn: ', inserted.linnanimi,
', elanike arv: ', inserted.rahvaarv)
FROM inserted
UNION ALL
SELECT
SUSER_NAME(),
GETDATE(),
'DELETE',
CONCAT('linn: ', deleted.linnanimi,
', elanike arv: ', deleted.rahvaarv)
FROM deleted;
END
--Läbivaatus
INSERT INTO linnad(linnanimi, rahvaarv)
VALUES ('Tartu', 50000);
DELETE FROM
linnad
WHERE
linnID=2;
SELECT * FROM linnad;
SELECT * FROM logi;
--Diaktiveerib päästiku
DISABLE TRIGGER linnaLisamine
ON linnad
Triggerite loomine kahe tabeliga
Create database trigerTARgv24;
USE trigerTARgv24;
Create table linnad(
linnID int PRIMARY KEY IDENTITY (1,1),
linnanimi varchar(15) NOT NULL,
rahvaarv int);
Create table logi(
id int PRIMARY KEY IDENTITY (1,1),
aeg DATETIME,
toiming varchar(100),
andmed TEXT
)
Create table maakond(
maakondID int PRIMARY KEY IDENTITY (1,1),
maakond varchar(100) UNIQUE,
pindala int
);
INSERT INTO maakond(maakond)
VALUES ('Harjumaa');
INSERT INTO maakond(maakond)
VALUES ('Pärnumaa');
ALTER TABLE linnad ADD maakondID int;
ALTER TABLE linnad ADD CONSTRAINT fk_maakond
FOREIGN KEY (maakondID) References maakond(maakondID)
SELECT * FROM linnad;
SELECT * FROM maakond;
INSERT INTO linnad(linnanimi, rahvaarv, maakondID)
VALUES ('Tallinn', 600000, 1);
Trigger lisamine
Create trigger linnaLisamine
ON linnad
FOR INSERT
AS INSERT INTO logi(aeg, toiming, andmed)
SELECT
GETDATE(),
'On tehtud INSERT',
CONCAT('Maakond: ', m.maakond, ', Linn: ', inserted.linnanimi, ', ', inserted.rahvaarv)
FROM inserted
INNER JOIN maakond m ON m.maakondID=inserted.maakondID;
Kontroll:
Trigger Kustutamine
Lisame kasutaja
ALTER TABLE logi ADD kasutaja varchar(25);
Create trigger linnaKustutamine
ON linnad
FOR DELETE
AS INSERT INTO logi(aeg, toiming, andmed, kasutaja)
SELECT
GETDATE(),
'On tehtud DELETE',
CONCAT('Maakond: ', m.maakond, ', Linn: ', deleted.linnanimi, ', ', deleted.rahvaarv)
SUSER_NAME()
FROM deleted
INNER JOIN maakond m ON m.maakondID=deleted.maakondID;
Kontroll
Trigger Uuendamine
CREATE TRIGGER linnaUuendamine
ON linnad
FOR UPDATE
AS
INSERT INTO logi(aeg, toiming, andmed, kasutaja)
SELECT
GETDATE(),
'On tehtud UPDATE käsk',
CONCAT('Vanad andmed - Maakond: ', m1.maakond, ', Pindala: ', m1.pindala, ', Linn: ', deleted.linnanimi,
', ', deleted.rahvaarv,
'/ Uued andmed - Maakond: ', m2.maakond, ', Pindala: ', m2.pindala, ', Linn: ', inserted.linnanimi,
', ', inserted.rahvaarv),
SUSER_NAME()
FROM deleted
INNER JOIN inserted ON deleted.linnID=inserted.linnID
INNER JOIN maakond m1 ON m1.maakondID=deleted.maakondID
INNER JOIN maakond m2 ON m2.maakondID=inserted.maakondID;
Kontroll
XAMPP
INSERT INTO logi(kasutaja, aeg, toiming, andmed)
SELECT
USER(),
NOW(),
'on tehtud UPDATE käsk',
CONCAT('Vanad andmed - Maakond: ', m1.maakond, ', Pindala: ', m1.pindala, '; Linn: ', OLD.linnanimi,
', elanike arv: ', OLD.rahvaarv,
'\n Uued andmed - Maakond: ', m2.maakond, ', Pindala: ', m2.pindala, ', Linn: ', NEW.linnanimi,
', elanike arv: ', NEW.rahvaarv)
FROM linnad l
INNER JOIN maakond m1 ON m1.maakondID=OLD.maakondID
INNER JOIN maakond m2 ON m2.maakondID=NEW.maakondID
WHERE NEW.linnID=l.linnID
Kontroll
Lisamine
INSERT INTO logi(kasutaja, aeg, toiming, andmed)
SELECT
USER(),
NOW(),
'On tehtud INSERT käsk',
CONCAT('Maakond: ', maakond, ', ', pindala, ', Linn: ', NEW.linnanimi, ', ', NEW.rahvaarv)
FROM linnad l
INNER JOIN maakond m ON l.maakondID=m.maakondID
WHERE l.linnID=NEW.linniD;
Kontroll
delete
INSERT INTO logi(kasutaja, aeg, toiming, andmed)
SELECT
USER(),
NOW(),
'On tehtud DELETE käsk',
CONCAT('Maakond: ', m.maakond, ', ', m.pindala, ', Linn: ', OLD.linnanimi, ', ', OLD.rahvaarv)
FROM linnad l
INNER JOIN maakond m ON l.maakondID=m.maakondID
Kontroll
Iseseisevtöö – trigerite ülesanne
Trigerite ülesanne, mis on esitluses “trigerid_localhost.pptx” viimasel slaidil
▶ Loo logitabel (id, toiming, aeg, kasutajanimi, autoAndmed) autorite registeri muudatuste fikseerimiseks.
▶ Mõtle ise välja väljad ja andmed autorite registeri tabelile.
▶ Loo triggerid, et fikseerida andmete lisamist, kustutamist ja muutmist autorite registeri tabelis, samal ajal salvestades muudatuste andmed logitabelisse.
SQL Server
Andmebaasi ja tabelite loomine
CREATE DATABASE autodDB;
USE autodDB;
CREATE TABLE autoregister(
autoID int PRIMARY KEY IDENTITY (1,1),
mark varchar(15) NOT NULL,
mudel varchar(15) NOT NULL,
aasta INT
);
CREATE TABLE logitabel(
id int PRIMARY KEY IDENTITY (1,1),
toiming varchar(100),
aeg DATETIME,
kasutajanimi varchar(25),
autoAndmed TEXT
);
Auto Lisamine
Create trigger autoLisamine
ON autoregister
FOR INSERT
AS INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud INSERT',
GETDATE(),
SUSER_NAME(),
CONCAT('Mark: ', inserted.mark, ', Mudel: ', inserted.mudel, ', Aasta: ', inserted.aasta)
FROM inserted;
Kontrollime:
Auto Kustutamine
CREATE TRIGGER autoKustutamine
ON autoregister
FOR DELETE
AS
INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud DELETE käsk', --toiming
GETDATE(), --aeg
SUSER_NAME(),
CONCAT('Mark: ', deleted.mark, ', Mudel: ', deleted.mudel, ', Aasta: ', deleted.aasta) --andmed
FROM deleted;
Kontrollime:
Auto Uuendamine
CREATE TRIGGER autoUuendamine
ON autoregister
FOR UPDATE
AS
INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud UPDATE käsk', --toiming
GETDATE(), --aeg
SUSER_NAME(),
CONCAT('Vanad andmed - Mark: ', deleted.mark, ', Mudel: ', deleted.mudel, ', Aasta: ', deleted.aasta,
'/ Uued andmed - Mark: ', inserted.mark, ', Mudel: ', inserted.mudel, ', Aasta: ', inserted.aasta) --andmed
FROM deleted
INNER JOIN inserted
ON deleted.autoID=inserted.autoID;
Kontrollime:
XAMPP
Andmebaasi ja tabelite loomine
CREATE TABLE autoregister(
autoID int Auto_increment PRIMARY KEY,
mark varchar(15) NOT NULL,
mudel varchar(15) NOT NULL,
aasta INT
);
CREATE TABLE logitabel(
id int Auto_increment PRIMARY KEY,
toiming varchar(100),
aeg DATETIME,
kasutajanimi varchar(25),
autoAndmed TEXT
);
Auto Lisamine
INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud INSERT käsk',
NOW(),
USER(),
CONCAT('Mark: ', NEW.mark, ', Mudel: ', NEW.mudel, ', Aasta: ', NEW.aasta);
Kontrollime:
Auto Kustutamine
INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud DELETE käsk',
NOW(),
USER(),
CONCAT('Mark: ', OLD.mark, ', Mudel: ', OLD.mudel, ', Aasta: ', OLD.aasta)
Kontrollime:
Auto Uuendamine
INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud UPDATE käsk',
NOW(),
USER(),
CONCAT('Vanad andmed - Mark: ', OLD.mark, ', Mudel: ', OLD.mudel, ', Aasta: ', OLD.aasta,
'\n\nUued andmed - Mark: ', NEW.mark, ', Mudel: ', NEW.mudel, ', Aasta: ', NEW.aasta)
FROM autoregister a
INNER JOIN autoregister ar
ON a.autoID=ar.autoID
WHERE NEW.autoID=a.autoID;
Kontrollime:
Teine ülesanne
Trigerite ülesanne, mis on esitluses “Triggerid_localhost_2tabelid.pptx” viimasel slaidil
▶ Lisada tabelile autoregister seotud tabel (omanikud)
▶ Muuta trügereid, et jälgida uuendusi ja lisamisi, nii et tabelis logitabel kuvatakse seotud tabelitest pärinevad andmed
SQL Server
CREATE TABLE omanikud (
omanikID int PRIMARY KEY IDENTITY (1,1),
nimi VARCHAR(50) NOT NULL,
aadress VARCHAR(50),
telefon VARCHAR(20)
);
ALTER TABLE autoregister ADD omanikID int;
ALTER TABLE autoregister ADD CONSTRAINT fk_omanikud
FOREIGN KEY (omanikID) References omanikud(omanikID)
Auto Lisamine
Create trigger autoLisamine
ON autoregister
FOR INSERT
AS INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud INSERT',
GETDATE(),
SUSER_NAME(),
CONCAT('Omanik: ', o.nimi, ', Mark: ', inserted.mark, ', Mudel: ', inserted.mudel, ', Aasta: ', inserted.aasta)
FROM inserted
INNER JOIN omanikud o ON o.omanikID=inserted.omanikID;
Kontrollime:
Auto Kustutamine
Create trigger autoKustutamine
ON autoregister
FOR DELETE
AS INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud DELETE',
GETDATE(),
SUSER_NAME(),
CONCAT('Omanik: ', o.nimi, ', Mark: ', deleted.mark, ', Mudel: ', deleted.mudel, ', Aasta: ', deleted.aasta)
FROM deleted
INNER JOIN omanikud o ON o.omanikID=deleted.omanikID;
Kontrollime:
Auto Uuendamine
CREATE TRIGGER autoUuendamine
ON autoregister
FOR UPDATE
AS
INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud UPDATE',
GETDATE(),
SUSER_NAME(),
CONCAT('Vanad andmed - Omanik: ', o1.nimi, ', Telefon: ', o1.telefon,
', Auto: ', deleted.mark, ', ', deleted.mudel, ', Aasta: ', deleted.aasta,
'/ Uued andmed - Omanik: ', o2.nimi, ', Telefon: ', o2.telefon,
', Auto: ', inserted.mark, ', ', inserted.mudel, ', Aasta: ', inserted.aasta)
FROM deleted
INNER JOIN inserted ON deleted.autoID=inserted.autoID
INNER JOIN omanikud o1 ON o1.omanikID=deleted.omanikID
INNER JOIN omanikud o2 ON o2.omanikID=inserted.omanikID;
Kontrollime:
XAMPP
CREATE TABLE autoregister(
autoID int Auto_increment PRIMARY KEY,
mark varchar(15) NOT NULL,
mudel varchar(15) NOT NULL,
aasta INT
);
CREATE TABLE logitabel(
id int Auto_increment PRIMARY KEY,
toiming varchar(100),
aeg DATETIME,
kasutajanimi varchar(25),
autoAndmed TEXT
);
CREATE TABLE omanikud (
omanikID int Auto_increment PRIMARY KEY,
nimi VARCHAR(50) NOT NULL,
aadress VARCHAR(50),
telefon VARCHAR(20)
);
ALTER TABLE autoregister ADD omanikID int;
ALTER TABLE autoregister ADD CONSTRAINT fk_omanikud
FOREIGN KEY (omanikID) References omanikud(omanikID)
Auto Lisamine
INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud INSERT käsk',
NOW(),
USER(),CONCAT('Omanik: ', nimi, ', ', telefon,
'; Auto: ', NEW.mark, ', ', NEW.mudel,
', Aasta: ', NEW.aasta)
FROM autoregister a
INNER JOIN omanikud o ON a.omanikID=o.omanikID
WHERE a.autoID=NEW.autoiD
Kontrollime:
Auto Kustutamine
INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud DELETE käsk',
NOW(),
USER(),
CONCAT('Omanik: ', o.nimi, ', ', o.telefon,
'; Auto: ', OLD.mark, ', ', OLD.mudel,
', Aasta: ', OLD.aasta)
FROM autoregister a
INNER JOIN omanikud o ON a.omanikID=o.omanikID
WHERE a.autoID=OLD.autoiD
Kontrollime:
Auto Uuendamine
INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'on tehtud UPDATE käsk',
NOW(),
USER(),
CONCAT('Vanad andmed - Omanik: ', o1.nimi, ', ', o1.telefon,
'/ Auto: ', OLD.mark, ', ', OLD.mudel, ', Aasta: ', OLD.aasta,
'\n\nUued andmed - Omanik: ', o2.nimi, ', ', o2.telefon,
'/ Auto: ', NEW.mark, ', ', NEW.mudel, ', Aasta: ', NEW.aasta)
FROM autoregister a
INNER JOIN omanikud o1 ON o1.omanikID=OLD.omanikID
INNER JOIN omanikud o2 ON o2.omanikID=NEW.omanikID
WHERE NEW.autoID=a.autoID
Kontrollime:
Kolmas ülesanne
Kasutaja loomine, kes ei näe (DENY) logi tabelit ja ei saa trigerit muuta.
SQL Server
Kasutaja loomine
Õiguste andmine
GRANT SELECT TO opilaneMariaSm;
-- DENY logi tabeli lugemise õigus
DENY SELECT, INSERT, UPDATE, DELETE ON logitabel TO opilaneMariaSm
-- DENY trigerite muutmise õigus
DENY ALTER ON OBJECT::dbo.autoregister TO opilaneMariaSm;
Kontrollime:
Ei näe ‘logitabel’
Ei saa trigerit muuta (‘Modify’ on hall)
Teeme lisamine, kustutamine ja uuendamine
Kontrollime:
XAMPP
Kasutaja loomine
Õiguste andmine
Kontrollime:
Ei näe ‘logitabel’
Ei saa trigerit muuta (päästikuid ei ole)
Teeme lisamine, kustutamine ja uuendamine
Kontrollime:
