Кафедра комп’ютерних технологiй
Індивiдуальне завдання
з дисциплiни: " Структурована мова запитiв SQL"
Тема: Вiдстежування змiн за допомогою стовпцiв i таблиць аудиту
Коломия 2009
План
Налаштування стовпцiв аудиту
Використання тригера UPDATE для заповнення таблицi аудиту
Використання OUTPUT для заповнення таблицi аудиту
Вiдновлення даних за допомогою таблиць аудиту
Аудит за допомогою стовпцiв
Перевага аудиту за допомогою стовпцiв полягає в тому, що контрольна iнформацiя розмiщується в тiй же таблицi, що i данi. У табл. 1 перерахованi деякi стовпцi аудиту, якi зазвичай додаються в таблицi.
|
Типи даних |
Коментарi |
INSERT, UPDATE або DELETE
INSERT, UPDATE або DELETE
DELETE
|
DATETIME
VARCHAR
BІТ/ТІN YINT
|
Використовується для вiдстежування дати i часу виконання вiдстежуваної дiї
Використовується для того, щоб помiтити данi як що видаляються. Це може з великою ефективнiстю застосовуватися в iндексуваннi i фiльтрацiї
|
По цiй таблицi можна зробити вивiд, що змiни даних насправдi не протоколюються. Найбiльш ефективний спосiб використання стовпцiв аудиту - це вiдстежування факту внесення змiни, часу змiни i особи або додатку, що виконав цю змiну. Можна використовувати цi стовпцi в будь-якiй комбiнацiї, щоб вiдстежувати змiни в записах в реальнiй таблицi.
Налаштування стовпцiв аудиту
Спочатку потрiбно визначити подiї, якi потрiбно вiдстежувати. В даному прикладi ви показано як додавати стовпцi аудиту для вiдстежування iнiцiатора змiн, дати i часу створення запису, дати i часу останнього оновлення запису i того, чи був видалений запис з таблицi Person. Address бази даних Adventure Works.
Вибравши таблицю (Person. Address) i визначивши подiї, якi вiдстежуватимуться, потрiбно вирiшити, якi стовпцi додати в таблицю.
Стовпець CreatedDate вiдстежуватиме, коли був створений запис. Тип даних цього стовпця DATETIME, з використанням функцiї GETDATE () для надання поточної дати як значення за замовчуванням.
Стовпець IsDeleted - стовпець з типом даних BIT, який використовуватиметься для запису про видалення рядка. Дата i користувач вiдстежуватимуться через стовпцi ModifiedDate i ModifiedBy. Якщо запис був видалений, цей стовпець буде помiчений, а в змiненому стовпцi будуть вiдомостi про того, хто i коли видалив запис.
Тепер можна виконати представлений нижче сценарiй, щоб змiнити таблицю Person. Address.
USE Adventure Works
GO
ALTER TABLE Person. Address
,ModifiedBy VARCHAR (50) NULL
Далi, якщо змiнювати таблицю з вже наявними даними, слiд задати в стовпцi CreatedDate значення, що показує, що стовпець був створений до того, як був початий аудит. Щоб задати значення CreatedDate, потрiбно виконати наступний код:
UPDATE Person. Address SET Createddate = '1/1/1980';
Тепер потрiбно змiнити процедури, що зберiгаються, i код додатку для заповнення цих стовпцiв потрiбними результатами. Для оновлення стовпцiв можна використовувати тригери, але зазвичай краще контролювати змiну даних i використовувати для оновлення стовпцiв аудиту код додатку.
Остання дiя в цьому процесi - це додавання фiльтру до всiх процедур i програм, що посилаються на дану таблицю, щоб запобiгти поверненню видалених записiв. Ось фiльтр, який потрiбно використовувати:
WHERE IsDeleted = 0
Аудит за допомогою таблиць
Тепер ми знаємо, як використовувати аудит для повiдомлення про зробленi змiни. Проте єдина змiна, яка може бути легко вiдмiнена - це подiя DELETE. Досить просто скинути прапор IsDeleted, i данi будуть знову доступнi. Існує також можливiсть вiдмiнити подiю CREATE, якщо про цю дiю є достатня iнформацiя. Проте якщо потрiбно мати можливiсть повнiстю вiдстежувати стан даних перед змiною, можливо, кращим варiантом виявиться використання таблиць аудиту. Цю можливiсть слiд використовувати з обережнiстю, тому що вона може викликати багато проблем з обслуговуванням i продуктивнiстю. Такi проблеми виникають тому, що доводиться копiювати данi в таблицю аудиту i змiнювати їх в початковiй таблицi. Для цього прикладу ми задамо аудит на базi таблицi в таблицi Sales. Special Offer. Мета - вiдстежування будь-яких змiн в цiй таблицi i забезпечення можливостi вiдмiнити змiни пiсля того, як вони були зафiксованi.
Налаштування таблицi аудиту
Запускаємо SQL Server Management Studio i знаходимо в Object Explorer (Оглядач об'єктiв) в базi даних Adventure Works таблицю Sales. SpecialOffer.
Генеруємо базовий сценарiй аудиту, клацнувши правою КНОПКОЮ мишi на таблицю Sales. SpecialOffer i вибравши з контекстного меню команди Script Table As, Create To, New Query Editor Window (Створити сценарiй для таблицi, Використовуючи CREATE, В новому вiкнi редактора запитiв). Пiсля цього вiдкриється нове вiкно запиту з готовим для редагування сценарiєм CREATE TABLE.
Вiдредагуємо сценарiй, виконавши перерахованi нижче дiї. Для цього прикладу остаточна редакцiя сценарiю показана у дiї 4. Спочатку видаляємо всi додатковi сценарiї. Потрiбно видалити всi рядки кодiв, якi не входять в iнструкцiю CREATE. Потiм змiнюємо iм'я таблицi з Sales. SpecialOffer на Sales. SpecialOfferAudit.
звичайним операцiям в таблицi iз самого початку. Це також повинно спростити управлiння таблицею. Додаємо всi додатковi стовпцi, якi допомагатимуть у визначеннi типу змiн, дати змiн i iнших елементiв аудиту, якi потрiбно вiдстежувати. У даному прикладi потрiбно додати стовпцi, перерахованi в табл. 2.
Табл. 2. Стовпцi, якi потрiбно додати в таблицю аудиту
| Ім’я стовпця |
Тип даних |
| AuditModif iedDate |
DATETIME |
| AuditType |
NVARCHAR (20) |
4. Виконуємо остаточний сценарiй, представлений нижче, в базi даних Adventure Works. (Цей код можна знайти у файлах прикладiв пiд iм'ям CreateАuditTable. sql)
USE AdventureWorks;
GO
CREATE TABLE Sales. SpecialOfferAudit (
Description NVARCHAR (255) NULL,
[Type] NVARCHAR (50) NULL,
Category NVARCHAR (50) NULL,
StartDate DATETIME NULL,
EndDate DATETIME NULL,
MinQty INT NULL,
MaxQty INT NULL,
rowguid UNIQUEIDENTIFIER NULL,
ModifiedDate DATETIME NULL,
AuditType NVARCHAR (20) null
);
GO
Основнi способи перемiщення даних в таблицi аудиту в SQL Server 2005 - це тригери бази даних i нова пропозицiя T-SQL OUTPUT. Проте OUTPUT додає деякi цiкавi можливостi. Тепер ми на прикладi вивчимо кожен з цих двох варiантiв.
Використання тригера UPDATE для заповнення таблицi аудиту
Код, приведений нижче, - це приклад синтаксичної конструкцiї, яку можна використовувати.
USE AdventureWorks
GO
CREATE TRIGGER SpecialOfferUpdateAudit ON Sales. SpecialOffer
FOR UPDATE
AS
INSERT INTO Sales. SpecialOfferAudit
(SpecialOfferID
,DiscountPct
,Category
,StartDate
,EndDate
,MinQty
,MaxQty
,rowguid
,ModifiedDate
SELECT TOP 1 d. SpecialOfferlD
,d. Description
,d. DiscountPct
,d. [Type]
. d. StartDate
,d. EndDate
. d. MinQty
,d. rowguid
,GETDATE ()
,’UPDATE’
FROM deleted d;
GO
Перевага використання тригера полягає в тому, що вiн захоплюватиме будь-якi оновлення, якi вiдбудуться в таблицi, незалежно вiд їх джерела. Це варiант аудиту з повним обхватом. Якщо мова йде про данi, якi змiнюються без контролю з вашого боку, то це чудовий варiант. Але якщо ми ретельно контролюємо данi, якi заносяться в таблицю, особливо якщо це виконується за допомогою процедур, що зберiгаються, то в SQL Server 2005 є нова можливiсть аудиту змiн - пропозицiя OUTPUT.
Використання OUTPUT для заповнення таблицi аудиту
Щоб ефективно використовувати OUTPUT, кожну подiю, яку потрiбно вiдстежувати, зажадає розробки процедур, що зберiгаються, i iнструкцiй SQL, якi використовуватимуться для оновлення (UPDATE), вставки (INSERT) або видалення (DELETE) даних у вiдстежуваних таблицях. OUTPUT надає доступ до таблиць, що вставляються i видаляються, в цих процедурах i iнструкцiях SQL. Тепер не обов'язково використовувати тригери для доступу до даних. Представлений нижче код показує приклад використання OUTPUT для аудиту оновлення в таблицi SpecialOffer в таблицi SpecialOfferAudit.
GO
SET description = 'Big Mountain Tire Sale'
OUTPUT deleted. SpecialOfferID
,deleted. DiscountPct
,deleted. [Type]
,deleted. StartDate
,deleted. EndDate
,deleted. MinQty
,deleted. MaxQty
,deleted. ModifiedDate
,GETDATE ()
’UPDATE’ INTO Sales. SpecialOfferAudit
WHERE SpecialOfferID = 10
Пропозицiя OUTPUT помiщає змiненi данi в рамках простого доступу в процесi змiни даних. В процесi операцiй UPDATE i DELETE доступний префiкс DELETED. У процесi операцiй UPDATE i INSERT доступний префiкс INSERTED. Потрiбно звернути увагу на те, що обидва префiкси не можуть бути доступними одночасно, на вiдмiну вiд таблиць deleted i inserted, якi використовуються в тригерах. Ця взаємодоступнiсть вимагає, щоб рiзнi операцiї оброблялися по-рiзному для збору потрiбних даних i перемiщення їх в таблицi аудиту.
Вiдновлення даних за допомогою таблиць аудиту
Тепер, коли у нас є два варiанти завантаження даних в таблицю аудиту, можна подумати, для чого використовувати цi данi. Оскiльки всi змiни в таблицi зберiгаються в таблицi аудиту, можна вiдновити будь-якi змiни даних, перезаписавши поточнi данi змiною, яку потрiбно зберегти. Таблиця аудиту може зберiгати декiлька версiй даних, тому найчастiше це доведеться робити уручну. Проте можна також створити обслуговуючу процедуру, що зберiгається, для вiдмiни найостаннiшої змiни.
Використання таблиць аудиту для вiдновлення змiнених даних
1. Визначаємо, який запис слiд вiдновити. Для цього потрiбно iдентифiкувати змiнний запис i данi, якi його замiнять.
2. Користуємось додатком UPDATE для перезапису поточних даних змiною, яку слiд вiдновити в цiй таблицi. У даному прикладi доведеться використовувати або властивiсть rowguid, або стовпець SpecialOf f erID у поєднаннi з AuditModif iedDate як критерiєм для iнструкцiї UPDATE, як показано нижче.
GO
UPDATE Sales. SpecialOffer
SET Description = а. Description
,DiscountPct = а. DiscountPct
,Type = а. Type
,Category = а. Category
,StartDate = а. StartDate
,EndDate = а. EndDate
,MinQty = а. MinQty
,MaxQty = а. MaxQty
,rowguid = а. rowguid
,ModifiedDate = а. ModifiedDate
FROM Sales. SpecialOfferAudit а
WHERE Specialoffer. SpecialOfferlD = 10
AND а. SpecialOfferlD = 10
AND а. AuditModifiedDate = '2006-04-02 22: 40: 27. 513'
Якщо у нас є данi, якi потребують регулярного вiдновлення, можна iнкапсулювати приведений вище код в процедуру обслуговування, що зберiгається. Проте якщо ми вирiшимо реалiзувати її, у нас можуть виникнути проблеми з введенням вiдновлених даних. Описанi варiанти добре пiдходять для обмеженої кiлькостi рядкiв в однiй таблицi. Якщо ми працюємо з масовим високопродуктивним завантаженням в декiлькох таблицях, слiд використовувати моментальнi знiмки, про якi йшла мова в першому роздiлi.
Висновок
архiвнi данi для пiдведення пiдсумкiв i аналiзу даних на певних вiдрiзках часу. Ми також навчилися виконувати аудит змiн даних i вiдновлювати окремi записи даних аудиту. Зберiгаючи архiвнi данi i вiдстежуючи змiни в базi даних, можна забезпечити цiлiснiсть даних, використовуючи можливiсть вiдмiни окремих змiн, не вiдновлюючи всю базу даних.
| 
