Тригери sql являють собою спеціальний тип процедур, що зберігаються, що запускаються сервером автоматично при зміні даних (DML) в таблиці, з якою він пов'язаний. Тригери підключаються до певної таблиці. Усі вироблені тригером зміни даних розглядаються як транзакція.
На відміну від звичайної процедури, що зберігається/функції, тригер викликається сервером неявно при виникненні певної тригерної події. Крім цього, тригер SQL не має аргументів. За допомогою тригера вирішуються такі завдання:
- перевірка коректності змінюваних даних та перевірка складних обмежень цілісності даних, які необхідно підтримувати;
- накопичення статистичної інформаціїза допомогою фіксації відомостей про внесімі зміни;
- підтримка реплікації.
За допомогою обмежень цілісності, встановлених правилі значень не завжди можна досягти потрібного рівня цілісності даних. Іноді потрібно реалізувати складні алгоритмиперевірки даних, що гарантують їх достовірність та реальність. Крім того, часто необхідно відстежувати зміни значень таблиці, щоб належним чиномзмінити пов'язані дані. Тригери SQLможна розглядати як свого роду фільтри, що набувають чинності після виконання всіх операцій відповідно до правил, стандартними значеннямиі т.д.
Застосування SQL тригерів пов'язано з додатковими витратами ресурсів сервера на операції додавання ( trigger insert), оновлення ( trigger update) або видалення ( trigger delete) даних у таблиці.
У тому випадку, коли тих же результатів можна досягти за допомогою процедур або прикладних програм, що зберігаються, застосування тригерів недоцільно.
CREATE TRIGGER
Основний формат команди CREATE TRIGGER показаний нижче:CREATE TRIGGER trigger_name [ BEFORE | AFTER ] ON table_name begin
Момент запуску тригера визначається ключовими словами BEFORE(тригер запускається перед виконанням пов'язаної з ним подією; наприклад, до додавання запису) або AFTER(після події). Якщо тригер викликається до події, він може внести зміни до запису, що модифікується подією, якщо подія - не видалення запису. Деякі СУБД накладають обмеження на оператори, які можуть бути використані у тригері (наприклад, може бути заборонено вносити зміни до таблиці, з якою «пов'язаний» тригер тощо).
Тригери можуть бути підключені не до таблиці, а до подання VIEW. У цьому випадку з їх допомогою реалізується механізм «представлення, що оновлюється». При підключенні тригера до подання ключові слова BEFOREі AFTERвпливають лише послідовність виклику тригерів, оскільки власне подія (видалення, вставка чи оновлення) немає.
Тригер може бути викликаний для кожного рядка ( FOR EACH ROW), охопленої цією подією, або лише один раз для кожної події ( FOR EACH STATEMENT).
Позначення<список_псевдонимов>відноситься до таких компонентів, як старий або новий рядок (OLD / NEW) або стара або нова таблиця (OLD TABLE / NEW TABLE). Старі значення не застосовні до подій вставки, а нові – для подій видалення.
Основна перевага тригерів полягає в тому, що стандартні функціїзберігаються всередині бази даних та активізуються при кожному її оновленні. Це дозволяє суттєво спростити програми.
Некоректно написані тригери можуть призвести до серйозним проблемам, пов'язаним із появою блокувань. Тригери здатні довгий часблокувати ресурси, тому слід звернути особливу увагу до зведення до мінімуму конфліктів доступу.
MS SQL trigger
Синтаксис створення тригера в СУБД MS SQL має такий вигляд:
CREATE TRIGGER trigger_name ON (WITH ENCRYPTION) [ [,] [,] ] [ WITH APPEND ] [ NOT FOR REPLICATION ] AS ( sql_statement )
schema_name
Найменування схеми тригера DML. Дія тригерів DMLобмежується областю схеми таблиці чи уявлення, котрим вони створено. schema_name не може вказуватися для тригерів DDL або тригерів входу.
trigger_name
Найменування тригера. Аргумент trigger_name повинен відповідати правилам для ідентифікаторів – за винятком того, що trigger_name не може починатися з символів # або ##.
table_name | view_name
Таблиця або уявлення, до яких підключено тригер.
Приклад ms sql trigger
Для реалізації тригера буде створено дві таблиці: test_table, test_log. До таблиці test_table буде підключено тригер. При оновленні записів у таблиці test_table тригер реєструватиме в таблиці test_log результати змін. Тобто. тригер буде викликатися за подією update.
Тестова таблиця test_table:
Create table dbo.test_table (id int not null, field1 varchar(255) null, field2 varchar(255) null, constraint pkTestTableID primary key (id));
Таблиця журналування test_log:
Create table dbo.test_log (id bigint identity(1,1) not null, table_name varchar(50) not null, oper varchar(15) not null, record_old xml null, record_new xml null, data datetime null, constraint pkTestLog id));
Тригер оновлення даних:
Trigger update create trigger dbo.trg_test_table_update on dbo.test_table for UPDATE як безперервний set nocount on -- змінні для зберігання старих і нових даних declare @record_new xml; declare @record_old xml; -- у таблиці deleted зберігаються старі/видалені дані set @record_old = (SELECT * FROM deleted FOR XML RAW, TYPE); -- у таблиці inserted зберігаються змінені (щойно створені) дані set @record_new = (SELECT * FROM inserted FOR XML RAW, TYPE); if (@record_new is not null) and (@record_old is not null) begin insert into dbo.test_log (table_name, opera, record_old, record_new, data) значення ("test_table", "update", @record_old, @record_new, GETD ()) end; end;
Додамо кілька рядків до тестової таблиці, які будемо оновлювати для тестування тригера:
Insert into dbo.test_table (id, field1, field2) values (1, "Кава", "Nescafe"); insert into dbo.test_table (id, field1, field2) values (2, "Чай", "Greenfield");
Перевіряємо роботу тригера оновленням рядків:
Перевіряємо таблицю журналу test_log. Результат має виглядати так, як це представлено на скріншоті:
XML дані проглядаються і показують, що таблиця журналування включає як старі, і нові значення.
PostgreSQL trigger
Синтаксис створення тригера
CREATE TRIGGER trigger_name [ подія [ OR подія ]] ON table_name FOR EACH ( ROW | STATEMENT ) EXECUTE PROCEDURE function_name (аргументи)
У аргументі вказується найменування створюваного тригера. За потреби може бути вказано найменування схеми.
(BEFORE | AFTER)
Ключове слово BEFORE означає, що trigger beforeта функція має виконуватися перед виконанням відповідної події. Ключове слово AFTER означає, що trigger afterі функція викликається після завершення операції, що приводить у дію тригер.
( подія [ OR подія ... ] )
У PostgreSQL підтримуються такі події. При перерахуванні кількох подій як роздільник використовується ключове слово OR.
Найменування таблиці, модифікація якої призводить до спрацьовування тригера.
FOR EACH ( ROW | STATEMENT )
Ключове слово, що йде за конструкцією FOR EACHта визначальна кількість викликів функції при настанні вказаної події. Використання ROW означає, що функція викликається для кожного модифікованого запису. Якщо функція повинна викликатись лише один раз для всієї команди, використовується ключове слово STATEMENT.
EXECUTE PROCEDURE function_name
Найменування функції з аргументами. Насправді аргументи під час виклику тригерних функцій не використовуються.
Синтаксис визначення тригерної функції
CREATE FUNCTION function_name () RETURNS trigger AS DECLARE - оголошення змінних BEGIN - тіло тригерної функції END; LANGUAGE plpgsql;
У тригерних функціях використовуються спеціальні змінні, що містять інформацію про тригер, що спрацював. За допомогою цих змінних тригерна функціяпрацює з даними. Нижче наведено деякі змінні, доступні в тригерних функціях.
| Найменування | Тип | Опис |
|---|---|---|
| NEW | RECORD | Нові значення поля запису, створеної командою INSERT або оновленою командою UPDATE, при спрацюванні тригера рівня запису (ROW). Змінна використовується для зміни нових записів. Змінна NEW доступна лише за INSERT і UPDATE. Поля запису NEW можуть бути змінені тригером. |
| OLD | RECORD | Старі значення полів запису, що містилися в записі перед виконанням команди DELETE або UPDATE під час спрацьовування тригера рівня запису (ROW). Змінна OLD доступна лише за DELETE та UPDATE. Поля запису OLD можна використовувати тільки для читання, не можна змінювати. |
| TG_NAME | name | Ім'я тригера, що спрацював. |
| TG_WHEN | text | Оператори BEFORE чи AFTER залежно від моменту спрацьовування тригера, вказаного у визначенні. |
| TG_LEVEL | text | Рядок ROW або STATEMENT залежно від рівня тригера, вказаного у визначенні. |
| TG_OP | text | Рядок INSERT, UPDATE або DELETE в залежності від операції, що спричинила спрацювання тригера. |
| TG_RELID | oid | Ідентифікатор об'єкта таблиці, де спрацював тригер. |
| TG_RELNAME | name | Ім'я таблиці, де спрацював тригер. |
До окремих полів запису NEWі OLDу тригерних процедурах звертаються так: NEW.names, OLD.rg.
Приклад postgresql trigger
У прикладі реалізовано просту систему логування користувачів. Вона стежить за таблицею користувачів та всі зміни реєструє в таблиці журналування. Наприклад будемо створювати спрощені таблиці.
Таблиця користувачів:
CREATE TABLE "public".users (id int not null, name varchar (64), constraint pkUsersID primary key (id));
Таблиця протоколування
CREATE TABLE "public".logs (text varchar(256), data timestamp without time zone);
Тригерна функція
CREATE OR REPLACE FUNCTION "public".add_to_log() RETURNS TRIGGER AS $$ DECLARE v_action varchar(30); v_user varchar(64); v_retstr varchar(256); BEGIN IF TG_OP = "INSERT" THEN v_user = NEW.name; v_action:= "Add new user"; v_retstr: = v_action | v_user; INSERT INTO "public".logs(text, data) values (v_retstr, NOW()); RETURN NEW; ELSIF TG_OP = "UPDATE" THEN v_user = NEW.name; v_action:= "Update user"; v_retstr: = v_action | v_user; INSERT INTO "public".logs(text, data) values (v_retstr, NOW()); RETURN NEW; ELSIF TG_OP = "DELETE" THEN v_user = OLD.name; v_action:= "Remove user"; v_retstr: = v_action | v_user; INSERT INTO "public".logs(text, data) values (v_retstr, NOW()); RETURN OLD; END IF; END; $$ LANGUAGE plpgsql;
Тригерна функція без вхідних параметрів повертає спеціальний тип TRIGGER. У функції у розділі DECLAREвизначено 3-ї змінні. У тілі функції виконується перевірка значення змінної TG_OP(Внутрішня змінна тригера). Залежно від транзакції визначаємо перемінну v_user і формується рядок retstr, який записується в таблицю logs.
Змінні NEWі OLD- це власне рядки, які обробляє тригер. У разі INSERT змінна NEW міститиме новий рядок, а OLD буде порожнім. У випадку UPDATE обидві змінні будуть визначені (відповідними даними), а у випадку DELETE змінна NEW буде порожня, OLD містити рядок, що видаляється.
Сам тригер описується на PL/pgSQL як:
Trigger insert & trigger update & trigger delete CREATE TRIGGER trg_user AFTER INSERT OR UPDATE OR DELETE ON "public".users FOR EACH ROW EXECUTE PROCEDURE add_to_log();
Тригер trg_user буде виконуватись після виконання транзакцій INSERT, UPDATE, DELETE для кожного рядка та викликати функцію add_to_log(). Тепер будь-які дії з таблицею users протоколюватимуться.
Наступні скрипти дозволяють перевірити працездатність тригера:
Додавання запису до таблиці користувачів insert in users (id, name) values (1, " Киса Воробьянинов " ); -- Оновлення запису в таблиці користувачів update users set name = "Остап Бендер" where id = 1 -- Читання користувачів select * from users -- Читання журналу протоколювання. Має бути 2 записи select * from logs
Oracle triggers
Синтаксис CREATE TRIGGER в Oracle має такий вигляд:
Trigger before CREATE TRIGGER trigger_name BEFORE DELETE OR INSERT OR UPDATE ON table_name REFERENCING<список_псевдонимов>FOR EACH ROW WHEN (new.field_name > 0) DECLARE - змінні, константи, курсори і т.п. BEGIN - блок PL/SQL END;
У тексті створення тригера може бути включене необов'язкове обмеження тригера шляхом визначення булевського виразу SQL у фразі WHEN. Вираз у фразі WHEN перевіряється для кожного рядка, що зачіпає тригер. Якщо результат висловлювання ІСТИНА, то тіло тригера виконується. Якщо вираз брехня або NULL, то тіло тригера не виконується. Вираз у фразі WHEN має бути виразом SQL, але з виразом PL/SQL, і може включати підзапит.
REFERENCING
Опція REFERENCING може використовуватися в тілі тригера для того, щоб уникнути конфліктів між кореляційними іменами та іменами таблиць, якщо таблиця має ім'я "OLD" або "NEW". Така ситуація рідкісна, і ця опція майже ніколи не застосовується.
Як приклад, можна розглянути таблицю з ім'ям new. Наступне визначення CREATE TRIGGERпоказує тригер, асоційований з таблицею new, який використовує опцію REFERENCINGщоб уникнути конфліктів між кореляційними іменами та ім'ям таблиці:
Trigger before CREATE TRIGGER trg_dummy BEFORE UPDATE ON new REFERENCING new AS new FOR EACH ROW BEGIN:newest.field2:= TO_CHAR (:newest.field1); END;
Оператор newперейменований у newest за допомогою опції REFERENCING, а потім використаний у тілі тригера.
Умовні предикати
Якщо тригер може бути викликаний виконання більш ніж одним типом пропозиції DML (наприклад, "INSERT OR DELETE OR UPDATE"), то в тілі тригера можна використовувати оператори INSERTING, DELETINGі UPDATINGдля виконання різних ділянок коду в залежності від умови. У коді всередині тіла тригера ви можете використовувати такі умови:
IF INSERTING THEN. . . END IF; IF UPDATING THEN. . . END IF;
Перше умова буде виконуватися у випадках, коли тригер був стартований при вставці рядка в таблицю. Друга умова виконуватиметься при оновленні рядка таблиці.
В операторі UPDATINGможна додатково використовувати умову перевірки імені стовпця, що оновлюється. Як приклад можна розглянути наступний код, в якому тіло буде виконуватися, якщо пропозиція UPDATE, що порушила тригер, оновлює стовпець SAL:
IF UPDATING ("SAL") THEN . . . END IF;
Oracle triggers відключення, включення
У Oracle тригер можна тимчасово вимкнути, якщо має місце одна з наступних умов:
- об'єкт, якого звертається тригер, недоступний;
- необхідно здійснити масове завантаження даних без виклику тригерів;
- Необхідно завантажити дані в таблицю без виклику тригера.
За замовчуванням тригер включається в момент його створення. Щоб вимкнути тригер, необхідно використати команду ALTER TRIGGERз опцією DISABLE. Щоб увімкнути тригер, використовуйте команду ALTER TRIGGER з опцією ENABLE. Можна одночасно вимкнути всі тригери, асоційовані з таблицею, за допомогою команди ALTER TABLEз опцією DISABLE ALL TRIGGERS.
Вимкнення тригера ALTER TRIGGER TRG_Orders_INS DISABLE; -- підключення тригера ALTER TRIGGER TRG_Orders_INS ENABLE; - відключення всіх тригерів таблиці ALTER TABLE Orders DISABLE ALL TRIGGERS;
Для включення або відключення тригера за допомогою команди ALTER TABLE, необхідно бути власником таблиці, або мати відповідний привілей.
Приклад oracle trigger
Генератор послідовностей CREATE SEQUENCE seqID; -- таблиця користувачів CREATE TABLE users (id int PRIMARY KEY не null, name varchar(50), phone varchar(15), dt date); -- trigger insert визначає ідентифікатор запису CREATE OR REPLACE TRIGGER trgAutonumber BEFORE INSERT ON users -- trigger before FOR EACH ROW BEGIN select seqID.NEXTVAL into:new.id from dual; END; -- trigger insert визначає дату запису CREATE OR REPLACE TRIGGER trgDate BEFORE INSERT ON users trigger before FOR EACH ROW BEGIN if:old.dt is null then:new.dt:= current_date; end if; END trgDate;
У наступному прикладі тригер trgDepartmentst_del_cascade виконує каскадне видалення записів TRIGGER DELETE CASCADE. Тригер, підключений до таблиці departments, реалізує посилальну дію DELETE CASCADE за первинним ключем таблиці deptID:
Trigger after CREATE OR REPLACE TRIGGER trgDepartmentst_del_cascade AFTER DELETE ON departments FOR EACH ROW BEGIN /* Після видалення рядка з таблиці Departments видалити з таблиці Employees всі рядки, що мають таке ж значення deptID. */ DELETE FROM employees WHERE employees.deptID = :old.deptID; END;
Примітка: зазвичай код для DELETE CASCADEоб'єднують разом з кодом UPDATE SET NULL або UPDATE SET DEFAULT, щоб врахувати як оновлення, так і видалення в одному тригері.
У SQL Serverіснують два види тригерів:
Тригери, що виконуються після події, що сталася з таблицею ( Повний аналогпроцедур подій у Visual Basic);
Тригери, що виконуються замість події, що відбувається з таблицею. У цьому випадку подія (додавання, зміна або видалення записів) не виконується, а замість нього виконуються SQL командизадані всередині тригера.
Перший вид тригерів застосовується обробки подій таблиць, а другий - забезпечення цілісності даних, тобто видалення записівз вторинної таблиці при видаленні пов'язаного з ними запису з первинної таблиці.
Зауваження: Тригери створюються для конкретної таблиці та виконуються автоматично, якщо з таблицею, для якої вони були створені, відбувається подія (додавання, зміна або видалення записів).
Для створення тригера на вкладці нового запиту необхідно набрати команду CREATE TRIGGER, що має наступний синтаксис:
CREATE TRIGGER<Имя триггера>
ON<Имя таблицы>
FOR
AS<Команды SQL>
Ім'я тригера - це ім'я тригера.
Ім'я таблиці - ім'я таблиці, на яку створюється тригер.
Якщо використовується параметр AFTER, тригер виконується після події, а якщо параметр INSTEAD OF, то виконується замість події.
Параметри INSERT, UPDATE і DELETE визначають подію, коли (або замість якого) виконується тригер.
Параметр WITH ENCRYPTION – призначений для включення шифрування данихпри виконанні тригера.
Команди SQL - це SQL команди, які виконуються при активізації тригера.
Розглянемо приклади створення різних тригерів для таблиці "Студенти".
приклад: Створює тригер "Додавання", що виводить на екран повідомлення "Запис додано"при додаванні нового записудо таблиці "Студенти"
CREATE TRIGGER Додавання
ON Студенти
FOR AFTER INSERT
AS PRINT "Запис додано"
приклад: Створює тригер "Зміна" "Запис змінено"при зміні записув таблиці "Студенти"
CREATE TRIGGER Зміна
ON Студенти
FOR AFTER UPDATE
AS PRINT "Запис змінено"
приклад: Створює тригер "Видалення", що виводить на екран з повідомлення "Запис видалено"при видаленні запису з таблиці "Студенти"
CREATE TRIGGER Видалення
ON Студенти
FOR AFTER DELETE
AS PRINT "Запис видалено"
приклад: В даному прикладізамість видалення студента з таблиці "Студенти"виконується код між BEGIN та END. Він складається із двох команд DELETE. Перша командавидаляє всі записи з таблиці "Оцінки", які пов'язані із записами з таблиці "Студенти". Тобто у яких Оцінки. [Код студента]дорівнює коду студента, що видаляється. Потім із таблиці "Студенти"видаляється сам студент.
CREATE TRIGGER ВидаленняСтудента
ON Студенти
INSTEAD OF DELETE
DELETE Оцінки
WHERE deleted.[Код студента]=Оцінки.[Код студента]
DELETE Студенти
WHERE deleted.[Код студента]=Студенти.[Код студента]
Зауваження: Тут видалена записпозначається службовим словом deleted.
Зауваження: Для забезпечення цілісності данихтригери зазвичай використовують разом з діаграмами, але ми можемо застосовувати такі тригери і без діаграм, однак ми не можемо застосовувати діаграми без тригерів.
Створення генераторів
Генератор – це програма, що зберігається в БД, що видає при кожному зверненні до неї унікальне число.
Створення генератора:
CREATE GENERATOR<Имя генератора>
Початкове значення задається інструкцією:
SET GENERATOR<Имя генератора>TO<Начальное значение (целое число)>
CREATE GENERATOR GenStore
SET GENERATOR GenStore TO 1
Звернення до створеного генератора виконується за допомогою функції
GEN_ID (<Имя генератора>, <Шаг>)
Тригер – це процедура, що перебуває на сервері БД і викликається автоматично під час модифікації записів БД, тобто. при зміні стовпців або при їх видаленні та додаванні. На відміну від процедур, що зберігаються, тригери не можна викликати з програми клієнта, а також передавати їм параметри і отримувати від них результати.
Створення тригера:
CREATE TRIGGER<>FOR<>
(BEFORE | AFTER)
(UPDATE | INSERT | DELETE)
AS<Тело триггера>
Описувачі ACTIVE | INACTIVE визначають активність тригера одразу після його створення. За промовчанням діє ACTIVE.
Описувачі Before | AFTER задають момент початку виконання тригера до або після настання відповідної події, пов'язаної зі зміною записів.
Описувачі UPDATE | INSERT | DELETE визначають, при настанні якоїсь події викликається тригер – при редагуванні, додаванні або видаленні записів.
Для однієї події можна створити кілька тригерів, кожен із яких буде автоматично виконаний (якщо перебуває в активному стані). За наявності кількох тригерів порядок їхнього виклику (виконання) визначає число, вказане в операнді POSITION. Тригери виконуються у порядку зростання цих чисел.
Видалення тригера:
DROP TRIGGER<Имя триггера>
Зміна тригера:
Для доступу до значень стовпця використовуються інструкції формату:
OLD.<Имя столбца>- звертається до старого (до внесення змін) значення стовпця,
NEW.<Имя столбца>- Звертається до нового (після внесення змін) значення стовпця.
Створення тригера для занесення в ключовий стовпець унікальних значень
CREATE TABLE Store
(S_Code INTEGER NOT NULL ,
PRIMARY KEY (S_Code));
CREATE GENERATOR GenStore
SET GENERATOR GenStore TO 1
CREATE TRIGGER CodeStore FOR Store
NEW.S_Code = GEN_ID (GenStore, 1);
При додаванні до таблиці Store новоїзапису ключового стовпця S_Code цього запису автоматично надається унікальне значення. Це забезпечується зверненням GEN_ID до генератора GenStore.
Реалізація каскадного видалення записів за участю тригера
CREATE TABLE Store
(S_Code INTEGER NOT NULL ,
PRIMARY KEY (S_Code));
CREATE TABLE Cards
(C_Code INTEGER NOT NULL,
C_Code2 INTEGER NOT NULL,
PRIMARY KEY (C_Code));
CREATE TRIGGER DeleteStore FOR Store
DELETE FROM Cards WHERE Store.S_Code = Cards.C_Code2;
Після видалення запису в таблиці Store автоматично видаляються всі відповідні записи в таблиці Cards.
Зауваження: для таблиць не повинні діяти обмеження цілісності, задані на фізичному рівні.
Оновлення стовпців зв'язку ( ключових стовпців) пов'язаних таблиць, що полягає в тому, що при зміні значення шпальти зв'язку головної таблиці відповідно змінюються значення шпальти зв'язку всіх пов'язаних записів підпорядкованої таблиці.
CREATE TRIGGER ChangeStore FOR Store
IF (OLD.S_Code<>NEW.S_Code)
THEN UPDATE Cards
SET C_Code2 = NEW.S_Code
WHERE C_Code2 = OLD.S_Code;
При зміні стовпця S_Code, який використовується для зв'язку головної таблиці Store з підлеглою таблицею Cards, автоматично змінюються значення стовпця зв'язку C_Code2 відповідних записів підпорядкованої таблиці.
Щоб стовпець головної таблиці можна було редагувати, не має бути створено ключ.
Тригер називають процедуру, яка зберігається на сервері у відкомпілюваному вигляді і виконується автоматично при настанні події додавання, редагування або видалення запису незалежно від бажання клієнта. Примусово запустити тригер із програми клієнта не можна. Тригеру не можна передати вхідні параметрита отримати від нього вихідні параметри.
Тригер завжди працює з поточноюзаписом та реалізує конкретну дію.
Тригери можуть виконуватися до настання події (параметр BEFORE)або після настання події (параметр AFTER).
Тригери розрізняють у напрямку дії:
- INSERT -на додавання запису;
- UPDATE- на редагування запису;
- DELETE- видалення запису.
Тригер створюється для конкретної таблиці та належить їй. Якщо таблиця має кілька тригерів одного напряму дії, час їх спрацьовування визначається насамперед параметрами BEFOREі AFTER, а при однаковому значенніпараметра настання події - параметром POSITIONіз зазначенням номера (порядку) спрацьовування тригера.
Працюючи з тригерами слід пам'ятати, что:
- при відкаті транзакцій відкочуються зміни, зроблені тригерами;
- легко реалізуються каскадні зміни та каскадні видалення у дочірніх таблицях;
- зміни, внесені в тіло тригера, автоматично працюють для кожної програми клієнта, тобто немає необхідності зроблені зміни доводити до кожного користувача.
Створення тригера проводиться за правилами створення процедур, що зберігаються, хоча є деякі особливості.
Створення тригера
Для створення тригера використовується оператор CREATE TRIGGER.
Формат оператора
CREATE TRIGGER FOR
[ ACTIVE | INACTIVE ]
[ BEFORE|AFTER]
[INSERT | UPDATE DELETE ]
[POSITION ]
Призначення опцій:
ACTIVE- тригер активний, тобто при зверненні до зазначеної таблиці виконується процедура, записана в стелі тригерам
INACTIVE- тригер пасивний, тобто. тригер створений і зберігається на сервері, але при зверненні до зазначеної таблиці стело тригера> не виконується;
BEFORE - донастання події;
AFTER -визначає час спрацьовування післянастання події;
INSERT -визначає для тригера подію додавання запису таблицю;
UPDATE -визначає для тригера подію редагування запису таблиці;
DELETE -визначає для тригера подію видалення запису з таблиці;
POSITION -визначає номер (позицію) спрацьовування тригера всередині певної події спрацьовування (BEFOREабо AFTER).
Для опису процедури стело тригера> використовуються ті ж оператори і конструкції, які використовуються при створенні процедури, що зберігається. У заголовку тригера визначають його активність, подія спрацьовування, дію, яку він реагує, і, за необхідності, позицію спрацьовування тригера.
При написанні стела тригера> додатково можна використовувати ключові слова OLD(до події) та NEW(після події) з наступним зазначенням імені поля.
Так як у віддаленій базі даних усі зміни в таблицях (додавання, редагування та видалення запису) проводяться у вибірках (в оперативній пам'яті), то, наприклад, при зміні значення поля можна звернутися як до старого (до зміни) значення поля - ОЬО. поля>, так і до нового (після зміни) значення поля - NEW-сім'я полях Якщо у вказане поле зміна не вносилася, то ОЬО.сім'я поля> дорівнюватиме NEW.chmb полях
Приклад 6.10.Створення тригера.
CREATE TRIGGER T_COMPCODE FOR COMPOSERS
ACTIVE BEFORE INSERT POSITION 0
new.code_composer=gen_id(g_composers, 1); if(COMPOSERS.data born is not null) then begin
new.actuallyage = (CAST("NOW" AS DATE)-COMPOSERS.data_born)/365; if(COMPOSERS.data day is not null) then COM POSERS.age =(COMPOSERS.data_day-COMPOSERS. data born) /365; else new.age=null; end
Тригер спрацьовує до настання події додавання нового запису та обчислює вік композитора. Обчислений вік записує у полі «actually_age».
Зміна тригера
Для зміни тригера використовується команда ALTER TRIGGER, що має аналогічний формат та аналогічний принцип роботи, що і команда ALTER PROCEDUREдля зміни тіла процедури, що зберігається.
Формат команди
ALTER TRIGGER FOR [ ACTIVE I INACTIVE
BEFORE I AFTER
INSERT I UPDATE DELETE
[ POSITION 1
Після виконання оператора ALTER TRIGGERстаре визначення тригера замінюється новим визначенням. Старе визначення тригера відновити не можна.
Приклад 6.11.Редагування тригера.
ALTER TRIGGER T_COMPCODE FOR COMPOSERS ACTIVE BEFORE INSERT POSITION 5 AS
new.code_composer=gen_id(g_composers, 1); if(COMPOSERS.data_born is not null) then begin
new.actually_age = (CAST("NOW" AS DATE)-COMPOSERS.data_born)/365; if(COM POSERS.dataday is not null) then COM POSERS.age =
(COMPOSERS.data_day-COMPOSERS.data born) / 365; else new.age=null; end
else begin new.age=null; new.actually_age=null; end end
У тригер, створений у прикладі 6.10, внесено зміну: задана нова позиція ( POSITION) спрацьовування тригера – 5.
Видалення тригера
Для видалення тригера використовують команду
Приклад 6.12.Видалення тригера.
DROP TRIGGER T_COMPCODE
Відновити віддалений тригер не можна.
Використання тригера у каскадних впливах
Як зазначалося вище, передбачено два види каскадних впливів: каскадна зміна та каскадне видалення. Тому кожної батьківської таблиці треба передбачити два тригера: один - для каскадного зміни, другий - для каскадного видалення. При цьому умови цілісності повинні бути видалені.
Приклад 6.13.Каскадне видалення записів.
При видаленні прізвища з батьківської таблиці ГАМнеобхідно видалити відповідні прізвища (за ключами фамі-
лии) у всіх дочірніх таблицях (у прикладі таблиці AUTORі BOOK).
CREATE TRIGGER DEL FAM FOR FAM ACTIVE
DELETE FROM AUTHOR
WHERE FAM.KEYFAM = AUTHOR. KEYFAM; DELETE FROM BOOK
WHERE FAM.KEY FAM = BOOK.KEY FAM;
Приклад 6.14.Каскадне редагування записів.
При зміні значення ключового поля (KEY FAM)у батьківській таблиці FAMнеобхідно змінити відповідні значення зовнішніх ключіву всіх дочірніх таблицях (у прикладі таблиці AUTHOR та BOOK).
CREATE TRIGGER UPD FAM FOR FAM ACTIVE
BEFORE UPDATE AS
IF (OLD.KEYFAM NEW.KEY FAM) THEN BEGIN
UPDATE AUTHOR SET KEY FAM = NEW.KEY FAM
WHERE KEY FAM - OLD.KEY FAM; UPDATE BOOK SET KEYFAM – N EW. KEYFAM
WHERE KEY FAM = OLD.KEY FAM;
Особливості використання каскадних впливів:
- при відкаті транзакцій всі зміни, зроблені тригерами, повертаються до вихідним значенням;
- в одному тригері допускається звернення до кількох таблиць;
- тригер може звертатися тільки до поточноюзаписи таблиці, тобто заборонено перехід до інших записів таблиці.
Забезпечення достовірності даних за допомогою тригера
Так як одна таблиця може мати кілька тригерів на одну подію (додавання, редагування або видалення запису), то є можливість забезпечити достовірність даних, що вводяться в таблицю. Для створення тригера для забезпечення достовірності даних додатково використовують оператори та ключові слова.
а. Забезпечення унікальності значення поля
Як правило, для цього використовують генератор. Роботу з генераторами див. 6.3. Попередньо створюється генератор, потім ім'я генератора вказують у тілі тригера.
Приклад 6.15.Заповнення поля первинного ключа.
Написати тригер за додаванням унікального значення первинного ключа KEYFAM.Генератор GFAMвже створено.
CREATE TRIGGER ДО РАМ FOR FAM
N EW. KEYFAM = GEN_ID(G_FAM, 1);
Приклад 6.16.Заповнення інформаційного поля.
CREATE TRIGGER TCOMPDATE FOR COMPOSERS
ACTIVE BEFORE UPDATE POSITION 0
if(COMPOSERS.data born is not null) then begin
new.actually_age = (CAST("NOW" AS
DATE)-COMPOSERS.data_born)/365; if(COMPOSERS.data day is not null) then COM POSERS.age =
(COM POSERS.data_day-COMPOSERS.data_born)/365; else new.age=null; end
else begin new.age=null; new.actually_age=null; end
У цьому прикладі обчислюється вік людини та заповнюється поле age.
Ведення журналу аудиту за допомогою тригера
У віддалених базах даних особливий інтерес представляє ведення журналу змін таблиць бази даних із визначення джерела недостовірних даних. Під час ведення журналу змін у спеціальній таблиці фіксується:
- виконану дію над таблицею;
- нове значення поля;
- старе значення поля;
- дата внесення зміни;
- прізвище, ім'я та по батькові користувача (USER NAME);
- номер (ім'я) робочої станції.
Приклад 6.17.Автоматичне заповнення журналу аудиту.
CREATE TRIGGER AFTJNS_DOGS FOR DOGS ACTIVE AFTER INSERT POSITION 0 AS begin
insert в log (act, table_name,record_id) values("INSERT","DOGS",DOGS. ID); end
CREATE TRIGGER AFT_UPD_DOGS FOR DOGS ACTIVE AFTER UPDATE POSITION 0 AS begin
insert в log (act,table_name,record_id) values('UPDATE',"DOGS',DOGS.ID); end
CREATE TRIGGER AFT DEL DOGS FOR DOGS ACTIVE AFTER DELETE POSITION 0 AS
insert into log (act,table_name,record_id) values('DELETE","DOGS',DOGS.ID); end
У цьому прикладі для таблиці DOGSстворено три тригери (по одному на кожну подію INSERT, UPDATEі DELETE).Кожен із тригерів додає до таблиці аудиту. logодин рядок, який містить поля «виконана дія», «ім'я таблиці» та «номер запису». За бажання кількість полів у таблиці аудиту можна збільшити.
Тригер- це підпрограма, схожа на процедуру БД, що автоматично викликається СУБД при зміні, видаленні або додаванні запису в таблиці. До тригерів неможливо звернутись із програми, передати їм параметри або отримати від них результат. Найчастіше тригери застосовуються підтримки посилальної цілісності і каскадних операцій на БД. Посилальні специфікації, що визначають каскадні дії при видаленні та оновленні та створювані при оголошенні таблиць, також реалізуються через тригери, проте текст цих тригерів не редагується.
Призначення тригерів
Запобігання зміни (наприклад, запобігти рахункам від змін після того, як вони розіслані).
. Журналування зміни (наприклад, зберігати копії старих даних).
. Аудит змін (наприклад, вести лог користувачів та ролей, що беруть участь у змінах).
. Фіксація змін (наприклад, забезпечити, щоб усі зміни датувалися щогодини сервера, а не клієнта).
. Реалізація бізнес-правил.
. Реплікація даних (наприклад, зберігати записи всіх змін, які будуть надіслані в іншій базі даних пізнішої версії).
. Підвищення продуктивності (наприклад, оновлення сальдо після кожної деталі угоди для прискорення запитів).
Оголошення тригерів
CREATE TRIGGER {BEFORE | AFTER} {DELETE | INSERT | UPDATE [OF ]} ON REFERENCING {OLD {[ROW]|TABLE [AS] } NEW {ROW|TABLE} [AS] }] [FOR EACH {STATEMENT|ROW [WHEN ]}]
[BEGIN ATOMIC]
[END]
Ключові слова
. BEFORE | AFTER- Час запуску тригера - до | після операції поновлення.
. DELETE | INSERT | UPDATE= подія спрацьовування тригера.
. FOR EACH ROW– для кожного рядка (маленький тригер, тоді і WHEN).
. FOR EACH STATEMENT– для всієї команди (за замовчуванням).
. REFERENCING- дозволяє надавати до 4-х псевдонімів старим і | або новим рядкам та | або таблицям, яких можуть звертатися тригера.
Обмеження тригерів
Тіло тригера не може містити операторів:
. Визначення, видалення та зміни об'єктів БД (таблиць, доменів тощо)
. Обробка транзакцій (COMMIT, ROLLBACK)
. Підключення та відключення до БД (CONNECT, DISCONNECT)
Особливості застосування
. Тригер виконується після застосування всіх інших (декларативних) перевірок цілісності і доцільний тоді, коли критерій перевірки досить складний. Якщо декларативні перевірки відхиляють операцію поновлення, то до виконання тригерів справа не доходить. Тригер працює у контексті транзакції, а обмеження FK немає.
. Якщо тригер викликає додаткову модифікацію своєї базової таблиці, то найчастіше це призводить до його рекурсивного виконання, проте це слід уточнювати. У СУБД SQL Server 2005 передбачено можливість вказати рекурсії до 255 рівнів за допомогою ключового слова OPTION (MAXRECURSIV 3).
. Тригери зазвичай не виконуються під час обробки великих двійкових стовпців (BLOB).
. Слід пам'ятати, що при оновленні даних СУБД автоматично створює так звані тригерні віртуальні таблиці, які в різних СУБД носять різні назва. У InterBase та Oracle – це New і Old. У SQL Server – Inserted та Deleted. Причому за зміни даних створюються обидві. Ці таблиці мають таку кількість стовпців, з тими самими іменами і доменами, як і оновлювана таблиця. У СУБД SQL Server 2005 передбачена можливість вказівки таблиці, включаючи тимчасову, в яку слід вставити дані за допомогою ключового слова OUTPUT Inserted.ID, INTO @ .
. У ряді СУБД припустимо оголошувати тригери для кількох дій одночасно. Для реалізації різних реакцій на різні діїу Oracle передбачені предикати Deleting, Inserting, Updating, що повертають True для відповідного виду оновлення.
. У СУБД Oracleможна для тригерів Update вказати список стовпців (After Update Of), що забезпечить виклик тригера лише за зміни значень лише цих стовпців.
. Для кожного тригерного події може бути оголошено кілька тригерів (в Oracle 12 тригерів на таблицю) і зазвичай порядок їх запуску визначається порядком створення. У деяких СУБД, наприклад InterBase, порядок запуску вказується за допомогою додаткового ключового слова POSITION . У випадку вважається, що спочатку повинні виконуватися тригери кожної команди, та був – кожної рядки.
. Тригери можна вбудовувати один в одного. Так SQL Server допускає 32 рівня вкладення (за допомогою глобальної змінної @@NextLevel можна визначити рівень вкладення).
Недоліки тригерів
Складність. Розміщення деяких дій над даними в БД ускладнює її проектування, реалізацію та адміністрування.
. Прихованість функціональних можливостейвід користувача. Важко проводити модернізацію програми, коли приховані деякі можливості.
. Вплив на продуктивність. При невеликій кількостіТригери збільшують час обробки даних.
Зміна та видалення тригерів
Для видалення тригера використовується оператор DROP TRIGGER
. Для зміни тригера використовується оператор ALTER TRIGGER.
. Відключення тригерів
У ряді випадків, наприклад, при пакетному завантаженні, тригери потрібно відключати. У ряді СУБД передбачені відповідні повноваження. У Oracle та SQL Server ключовіслова DISABLE|ENABLE, в InterBase INACTIVE|ACTIVE оператора ALTER TRIGGER.
Особливості промислових серверів
1) InterBase/Firebird
CREATE TRIGGER FOR {ACTIVE|INACTIVE} {BEFORE | AFTER} {INSERT|DELETE|UPDATE} [POSITION ]
AS [DECLARE VARIABLE [()]]
BEGIN
END
Приклад:
CREATE TRIGGER BF_Del_Cust FOR Customer
ACTIVE BEFORE DELETE POSITION 1 AS
BEGIN
DELETE FROM Orders WHERE Orders.CNum=Customer.CNum;
END;
2) SQL Server
CREATE TRIGGER ON [WITH ENCRYPTION] {FOR|AFTER|INSTEAD OF} {INSERT|UPDATE|DELETE}
AS
USE B1;
GO
CREATE TRIGGER InUpCust1 ON Customer AFTER INSERT, UPDATE
AS RAISEERROR('Змінена таблиця Customer');
Додаткові види тригерів
У СУБД Oracle і SQL Server є можливість створення (заміщаючих) тригерів для представлень, що не оновлюються. Для цього передбачено ключові слова INSTEAD OF:
CREATE TRIGGER ON INSTEAD OF INSERT AS …
Можна відслідковувати спроби клієнта оновлювати дані за допомогою уявлень і виконувати будь-які дії, обробляти уявлення, що не оновлюються, і т.п.
. У СКБД SQL Server передбачено тригер відкату, який фактично припиняє всі дії з видачею повідомлення:
ROLLBACK TRIGGER
