І як зазвичай почнемо з невеликою теорії, дізнаємося, що таке тригер і взагалі, навіщо він потрібен. Тобто дамо визначення триггеру і розглянемо його призначення в базі даних.
Визначення та призначення тригера
Тригер - в базі даних це звичайна процедура, що зберігається, але викликається вона не користувачем, а подією, яка може статися в базі даних. Події можуть бути наступні, і відносяться вони як до таблиць, так і до уявленням VIEW:
- INSERT - додавання записів в таблицю;
- DELETE - видалення записів з таблиці;
- UPDATE - оновлення записів таблиці.
З визначенням розібралися, тепер давайте розберемося, для чого потрібні ці тригери. А потрібні вони всього лише для однієї мети, виконання додаткових дій до або після самої sql інструкції в якій присутні вищеперелічені події. Мається на увазі, Ви самі можете вказати, коли виконувати і на яку подію реагувати. Ви можете написати тригер відразу на всі події (insert, delete, update) а можете тільки на одне або на два.
Які додаткові дії тут маються на увазі, в принципі будь-які, наприклад, на подія insert можна написати тригер і повісити на таблицю, і при додаванні записів, наприклад, відправляти повідомлення на пошту, або ще приклад, який ми розберемо трохи нижче, суть якого полягає в збереженні старої і нової записи при оновленні (update) тобто свого роду вести аудит всіх змін в тій чи іншій таблиці.
Як я вже сказав, наш приклад буде полягати в наступному, ми будемо вести аудит всіх змін в певній таблиці, тобто тригер буде викликатися за подією update.
Ми з Вами вже реалізовували подібну задачу, яку розглядали в матеріалі Журналювання змін даних в таблиці на Transact-SQL. Але там ми не використали тригери, і використовували свого роду імпровізований метод зберігання всіх змін, але сьогодні як в матеріалі Transact-sql - робота з xml ми будемо використовувати XML дані саме в тригері.
Примітка! Всі дії ми будемо виконувати в Management Studio SQL сервера.
І для початку, давайте створимо таблицю, на яку будемо вішати тригер:
Код створення таблиці:
І додамо пару рядків, які в подальшому ми будемо оновлювати, я додав наступні дані, і перевірив їх наявність за допомогою простого запиту select
Тепер давайте створимо таблицю, в яку ми будемо записувати всі зміни, я назвав її audit.
Код створення таблиці audit:
- id - це ідентифікатор з Автоінкрементний;
- table_name - назва таблиці, в якій відбулася зміна;
- oper - операція, в даному випадку у нас туди буде записувати update;
- record_old - xml дані старої рядки;
- record_new - xml дані нового рядка;
- username - хто змінив;
- date_ch - коли змінили.
Тепер давайте приступимо безпосередньо до написання самого тригера, і для початку, я приведу приклад синтаксису, трохи звичайно спрощеного, але так навіть краще для початківців, так як так більш зрозуміло і цього достатньо, щоб написати свій перший тригер. А весь синтаксис Ви можете подивитися в Інтернеті, наприклад в офіційній довідці Transact-SQL. Просто я вважаю, що для початківців він буде трохи складний і незрозумілий, тому ось простий синтаксис, якщо можна так сказати основа цього синтаксису:
CREATE TRIGGERназваніе тріггераONтабліца на яку вішати
FORна які операції (update, insert, delete)
Сюди пишемо самі sql інструкції які необхідно виконувати при спрацьовуванні тригера
Тепер залишилося перевірити роботу даного тригера, для початку запустимо update одного рядка.
І подивимося, що у нас з'явилося в таблиці audit.
Як бачите, XML дані легко проглядаються, і ми бачимо, що в старому записі були одні значення, а в новій вже відповідно нові.
До речі якщо натиснути на ці xml дані можна потрапити в окреме вікно, де ще наочніше буде видно, що за дані ми оновили, але для початку давайте спробуємо, оновити відразу кілька рядків, і подивимося, як поведе себе наш тригер, запустимо простий запит на оновлення :
І перевіряємо audit
Як бачите у нас додалася всього одне рядок, але ми ж оновили два рядки, все дуже просто всі наші дані записалися в xml документ, і відповідно в record_old зберігаються старі два рядки, а в record_new змінені два рядки, тобто скільки б ми не оновили рядків вони будуть відображені в xml документі. І тепер давайте все-таки натиснемо на xml дані і подивимося що буде.
А тут відображені всі рядки, які ми торкнулися.