SQL triggerid - Kirill Melnikov | PORTFOOLIO

SQL-триггеры
— это специальные объекты базы данных, которые автоматически срабатывают при определённом событии (например, INSERT, UPDATE или DELETE). Создание SQL-триггеров помогает автоматизировать процессы, обеспечивать целостность данных и реализовывать бизнес-логику прямо в базе данных.

SQL Server

Создание базы данных и двух таблиц:

Триггер для отслеживания добавленных записей в таблицу “linnad” – INSERT

Проверка работы триггера

Обновление (изменение) SQL-триггера
- Находим нужную таблицу в базе данных
- Открываем папку Triggers
- Кликаем правой кнопкой мыши на нужном триггере
- Выбираем пункт Modify (Изменить)

Откроется диалоговое окно, в котором мы можем внести необходимые изменения и сохранить их.

Выдели ALTER TRIGGER –> Выполнить (Execute) –> Команда выполнена

Вставка данных в таблицу “logi” была изменена.

Инструменты (Tools) → Параметры (Options) → Конструкторы (Designers) → Конструктор таблиц и баз данных (Table and Database Designers) → Снять галочку с опции “Предотвращать сохранение изменений, требующих пересоздания таблицы”

Добавим поле “kasutaja” (пользователь) в таблицу и сохраним.

Изменим триггер.
SUSER_NAME — это SQL-функция, которая возвращает имя текущего вошедшего пользователя.

Проверка: добавляется пользователь, который внёс данные в таблицу.

Триггер для отслеживания удалённых записей в таблице “linnad” – DELETE

Проверка

Триггер для отслеживания изменённых записей в таблице “linnad” – UPDATE

XAMPP

Создаём триггеры в XAMPP (использующем базу данных MySQL или MariaDB), следуя следующим шагам.

Триггер для отслеживания добавленных записей в таблицу “linnad” – INSERT

Заполнить таблицу Linnad и проверяем таблицу Logi

Триггер для отслеживания изменённых записей в таблице “linnad” – UPDATE

Проверка:

Триггер для отслеживания удалённых записей в таблице “linnad” – DELETE

Проверка:

Объединение триггеров INSERT и DELETE

Этот SQL-триггер linnaLisamineJaKustutamine записывает лог каждый раз, когда в таблицу городов добавляется новый город или удаляется существующий.

Самостоятельная работа — задание по триггерам

Задание с триггерами, которое находится на последнем слайде презентации “trigerid_localhost.pptx”

SQL Server

Создание базы данных и таблиц

Добавление автомобиля

Проверка:

Удаление автомобиля

Проверка:

Обновление автомобиля

Проверка:

XAMPP

Создание базы данных и таблиц

Добавление автомобиля

Проверка:

Удаление автомобиля

Проверка

Обновление автомобиля

Проверка:

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

--Создаём БД autodDB
CREATE DATABASE autodDB;

--Переходим в БД autodDB
USE autodDB;

--Создаём таблицу владельцев omanikud
CREATE TABLE omanikud (
    omanikID INT PRIMARY KEY IDENTITY(1,1),
    nimi VARCHAR(100),
    kontakt VARCHAR(100)
);

--Создаём таблицу autoregister с внешним ключом на omanikud
CREATE TABLE autoregister (
    autoID INT PRIMARY KEY IDENTITY(1,1),
    mark VARCHAR(50),
    mudel VARCHAR(50),
    aasta INT,
    registreerimiseKuupaev DATE,
    omanikID INT,
    FOREIGN KEY (omanikID) REFERENCES omanikud(omanikID)
);

--Таблица логов logitabel
CREATE TABLE logitabel (
    id INT PRIMARY KEY IDENTITY(1,1),
    toiming VARCHAR(100),
    aeg DATETIME,
    autoAndmed VARCHAR(1000),
	kasutajanimi VARCHAR(100)
);

--привяжем PK и FK
ALTER TABLE autoregister ADD omanikID int;
ALTER TABLE autoregister ADD CONSTRAINT fk_omanikud
FOREIGN KEY (omanikID) References omanikud(omanikID)


--Заполняем omanikud
INSERT INTO omanikud (nimi, kontakt)
VALUES
('Jaan Tamm', 'jaan.tamm@email.ee'),
('Mari Maasikas', 'mari.maasikas@email.ee'),
('Peeter Põld', 'peeter.pold@email.ee'),
('Kati Kask', 'kati.kask@email.ee'),
('Kirill Melnikov', 'dezxplay@gmail.com');

--Добавляем тестовые машины в autoregister
INSERT INTO autoregister (mark, mudel, aasta, registreerimiseKuupaev, omanikID)
VALUES
('Toyota', 'Corolla', 2015, '2023-06-15', 1),
('BMW', '320i', 2020, '2024-01-10', 2),
('Audi', 'A4', 2018, '2022-09-05', 3),
('Honda', 'Civic', 2017, '2021-12-20', 4),
('Ford', 'Mustang', 2025, '2025-03-01', 5);

SELECT * FROM autoregister;
SELECT * FROM logitabel;
SELECT * FROM omanikud;

Triggerid

--Триггер на INSERT
CREATE TRIGGER autoLisamine
ON autoregister
FOR INSERT
AS
INSERT INTO logitabel (toiming, aeg, autoAndmed, kasutajanimi)
SELECT
    'On tehtud INSERT käsk',
    GETDATE(),
    CONCAT(
        'Mark: ', i.mark,
        ', Mudel: ', i.mudel,
        ', Aasta: ', i.aasta,
        ', Omanik: ', o.nimi
    ),
    SYSTEM_USER
FROM inserted i
INNER JOIN omanikud o ON i.omanikID = o.omanikID;

--Триггер на DELETE
CREATE TRIGGER autoKustutamine
ON autoregister
FOR DELETE
AS
INSERT INTO logitabel (toiming, aeg, autoAndmed, kasutajanimi)
SELECT
    'On tehtud DELETE käsk',
    GETDATE(),
    CONCAT(
        'Mark: ', d.mark,
        ', Mudel: ', d.mudel,
        ', Aasta: ', d.aasta,
        ', Omanik: ', o.nimi
    ),
    SYSTEM_USER
FROM deleted d
INNER JOIN omanikud o ON d.omanikID = o.omanikID;

--Триггер на UPDATE
CREATE TRIGGER autoUuendamine
ON autoregister
FOR UPDATE
AS
BEGIN
    INSERT INTO logitabel (toiming, aeg, autoAndmed, kasutajanimi)
    SELECT
        'On tehtud UPDATE käsk',
        GETDATE(),
        CONCAT(
            'Vanad andmed - Mark: ', d.mark,
            ', Mudel: ', d.mudel,
            ', Aasta: ', d.aasta,
            ', Omanik: ', o1.nimi,
            ' ; Uued andmed - Mark: ', i.mark,
            ', Mudel: ', i.mudel,
            ', Aasta: ', i.aasta,
            ', Omanik: ', o2.nimi
        ),
        SYSTEM_USER
    FROM deleted d
    INNER JOIN inserted i ON d.autoID = i.autoID
    LEFT JOIN omanikud o1 ON d.omanikID = o1.omanikID
    LEFT JOIN omanikud o2 ON i.omanikID = o2.omanikID;
END;

--Проверка INSERT
INSERT INTO autoregister (mark, mudel, aasta, registreerimiseKuupaev, omanikID)
VALUES ('Mazda', '6', 2022, '2025-03-26', 1);
--Проверка UPDATE
UPDATE autoregister
SET mudel = 'Camry', aasta = 2019, omanikID = 2
WHERE mark = 'Toyota';
--Проверка DELETE
DELETE FROM autoregister
WHERE mark = 'Mazda';

SELECT * FROM autoregister;
SELECT * FROM logitabel;
SELECT * FROM omanikud;

--omanikLisamine (INSERT)
CREATE TRIGGER omanikLisamine
ON omanikud
FOR INSERT
AS
INSERT INTO logitabel (toiming, aeg, autoAndmed, kasutajanimi)
SELECT
    'Lisatud uus omanik',
    GETDATE(),
    CONCAT(
        'Nimi: ', nimi,
        ', Kontakt: ', kontakt
    ),
    SYSTEM_USER
FROM inserted;

--omanikUuendamine (UPDATE)
CREATE TRIGGER omanikUuendamine
ON omanikud
FOR UPDATE
AS
INSERT INTO logitabel (toiming, aeg, autoAndmed, kasutajanimi)
SELECT
    'Muudetud omaniku andmed',
    GETDATE(),
    CONCAT(
        'Vana - Nimi: ', d.nimi,
        ', Kontakt: ', d.kontakt,
        ' ; Uus - Nimi: ', i.nimi,
        ', Kontakt: ', i.kontakt
    ),
    SYSTEM_USER
FROM deleted d
JOIN inserted i ON d.omanikID = i.omanikID;

--omanikKustutamine (DELETE)
CREATE TRIGGER omanikKustutamine
ON omanikud
FOR DELETE
AS
INSERT INTO logitabel (toiming, aeg, autoAndmed, kasutajanimi)
SELECT
    'Kustutatud omanik',
    GETDATE(),
    CONCAT(
        'Nimi: ', nimi,
        ', Kontakt: ', kontakt
    ),
    SYSTEM_USER
FROM deleted;

-- Добавим нового владельца
INSERT INTO omanikud (nimi, kontakt)
VALUES ('Test Testija', 'test@test.ee');

-- Обновим контакт
UPDATE omanikud
SET kontakt = 'test@muudetud.ee'
WHERE nimi = 'Test Testija';

-- Удалим его
DELETE FROM omanikud
WHERE nimi = 'Test Testija';

SELECT * FROM logitabel ORDER BY aeg DESC;

*LISAHINNE kasutaja loomine, kes ei näe (DENY) logi tabelit ja ei saa trigerit muuta.

-- Создаём логин с паролем
CREATE LOGIN piiratudKasutaja WITH PASSWORD = 'TugevParool123!';

-- Создаём пользователя в базе данных autodDB
USE autodDB;
CREATE USER piiratudKasutaja FOR LOGIN piiratudKasutaja;

-- Чтобы он мог вставлять, обновлять или удалять (если надо)
GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.autoregister TO piiratudKasutaja;
GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.omanikud TO piiratudKasutaja;

-- Запретим читать лог-таблицу:
DENY SELECT ON logitabel TO piiratudKasutaja;

Ограничим ALTER на таблицы:
DENY ALTER ON OBJECT::dbo.autoregister TO piiratudKasutaja;
DENY ALTER ON OBJECT::dbo.omanikud TO piiratudKasutaja;

--или чтобы не логинится можно использовать этот метод
USE autodDB;
EXECUTE AS USER = 'piiratudKasutaja'; --от лица урезанного пользователя запустить команды
SELECT * FROM autoregister;
SELECT * FROM omanikud
SELECT * FROM logitabel --⛔ не сработает но для проверки можно использовать
REVERT; -- возвращение к суперюзеру

--проверка piiratudKasutaja
USE autodDB;
SELECT * FROM autoregister;
SELECT * FROM omanikud

SELECT * FROM logitabel --⛔ не сработала и выдала ошибку прав

--Проверка INSERT
INSERT INTO autoregister (mark, mudel, aasta, registreerimiseKuupaev, omanikID)
VALUES ('Mazda', '6', 2022, '2025-03-26', 1);

--Проверка UPDATE
UPDATE autoregister
SET mudel = 'Camry', aasta = 2019, omanikID = 2
WHERE mark = 'Toyota';

--Проверка DELETE
DELETE FROM autoregister
WHERE mark = 'Mazda';

XAMMP

DROP DATABASE IF EXISTS autodDB;
CREATE DATABASE autodDB;
USE autodDB;

-- Таблица владельцев
CREATE TABLE omanikud (
    omanikID INT AUTO_INCREMENT PRIMARY KEY,
    nimi VARCHAR(100),
    kontakt VARCHAR(100)
);

-- Таблица автомобилей
CREATE TABLE autoregister (
    autoID INT AUTO_INCREMENT PRIMARY KEY,
    mark VARCHAR(50),
    mudel VARCHAR(50),
    aasta INT,
    registreerimiseKuupaev DATE,
    omanikID INT,
    FOREIGN KEY (omanikID) REFERENCES omanikud(omanikID)
);

-- Таблица логов
CREATE TABLE logitabel (
    id INT AUTO_INCREMENT PRIMARY KEY,
    toiming VARCHAR(100),
    aeg DATETIME,
    autoAndmed TEXT,
    kasutajanimi VARCHAR(100)
);

ALTER TABLE autoregister ADD CONSTRAINT fk_omanikud
FOREIGN KEY (omanikID) References omanikud(omanikID)

Triggers

Auto Lisamine

INSERT INTO logitabel (toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud INSERT käsk',
NOW(),
USER(),
CONCAT('Omanik: ', o.nimi, ', ', o.kontakt,
        '; 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

Проверка:

Auto Kustutamine

INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud DELETE käsk',
NOW(),
USER(),
CONCAT('Omanik: ', o.nimi, ', ', o.kontakt,
              '; 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

Проверка:

Auto Uuendamine

INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud UPDATE käsk',
NOW(),
USER(),
CONCAT('Vanad andmed - Omanik: ', o1.nimi, ', ', o1.kontakt, '; Auto: ', OLD.mark, ', ', OLD.mudel, ', Aasta: ', OLD.aasta, '\n\nUued andmed - Omanik: ', o2.nimi, ', ', o2.kontakt, '; 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

Проверка: