Робота з транзакціями

Запускати тригер можуть інструкції DML INSERT, UPDATE і DELETE Дуже важливо, щоб тригер мав доступ до змін, виконаним інструкцією DML, щоб перевірити отримані значення або обробити результати транзакції SQL Server пропонує чотири способи перевірки в тілі тригера ефекту, виробленого інструкцією DML Образи Inserted і Deleted містять набори даних до і після виконання інструкції, а функції updated () і columns_updated () можна використовувати для визначення того, на які стовпці справила вплив інструкція DML

Визначення складу оновлених стовпців

SQL Server пропонує два методи визначення складу оновлених стовпців Функція update () повертає значення true для одного стовпця, якщо на нього вплинула транзакція DML:

IF UPDATE (Col umnName)

Наступна інструкція INSERT зробить вплив на всі стовпці, після чого функція update () вкаже на наявність оновлень в конкретному стовпці:

ALTER TRIGGER TriggerOne ON Person AFTER Insert, Update AS

IF Update(LastName)

PRINT Змінено стовпець LastName1 ELSE

PRINT Стовпець LastName залишився недоторканим

Протестуємо даний тригер, який перевіряє наявність змін в стовпці Last Name: UPDATE Person SET LastName = Johnson

WHERE PersonID =25

Буде отримано наступний результат:

Змінено стовпець LastName

Звичайно функція update () використовується для виконання перевірки даних у міру необхідності Немає сенсу заново перевіряти дані в стовпці, якщо вони не змінювалися інструкцією DML У той же час ця функція помічає саме наявність операції зміни стовпця в інструкції DML, а не зміна самих даних Наприклад, якщо інструкція змінить значення abc 1 на 1 abc 1 (Тобто фактично не змінить його), функція update () все одно відзвітує про наявність змін

Функція columns_updated () повертає бітову карту varbinary наявності змін в стовпцях таблиці Якщо конкретний біт має значення true, це означає, що стовпець був змінений Результат функції columns_updated () Можна порівняти з цілочисельними або двійковими даними за допомогою будь бітової операції, щоб визначити, чи був змінений конкретний стовпець

У документації йдеться, що стовпці представлені бітами в напрямку зліва направо, проте це не зовсім так Насправді зліва направо слідують байти, а в кожному байті біти проходять справа наліво Додатково ускладнює ситуацію те, що дані типу varbinary, повертаються функцією columns_updated (), залежать від кількості стовпців в таблиці

Наступна функція імітує реальний режим роботи функції columns_updated () Функції передається номер тестованого шпальти і загальна кількість стовпців у таблиці У результаті повертається бітова маска для цього шпальти:

CREATE FUNCTION dboGenColUpdated (OCol INT, @ColTotal INT)

RETURNS INT AS

BEGIN

– Copyright 2 0 01 Стать Нільсен

– Ця функція імітує режим роботи функції Columns_Updated ()

DECLARE

@ColByte INT,

@ColTotalByte INT,

@ ColBit INT – Обчислення позиції байта SET @ ColTotalByte = 1 + ((@ ColTotal-l) / 8)

SET @ColByte = 1 + ((@Col-l)/8)

SET @ColBit = @col – ((@colByte-l) * 8)

RETURN Power(2, @colbit + ((@ColTotalByte-@ColByte) * 8)-1)

END

Ця функція використовується в динамічному тригері (або збереженої процедурою) ведення журналу аудиту при цьому виконується операція побітового and (&) між результатами функцій columns_updated () і GenColUpdated () Якщо результат операції дорівнює результату функції GenColUpdated (), значить, розглянутий стовпець насправді був оновлений:

Set @Col_Updated = Columns_Updated()

Set @ColUpdatedTemp =dboGenColUpdated(@ColCounter,©ColTotal)

If (@Col_Updated &amp ©ColUpdatedTemp) = @ColUpdatedTemp

4опоянітел £ ная Повний програмний код динамічного аудиту наведено в розділі 24 Сценарій ^ інформація \ DynamicAudit sql, який можна знайти на сайті книги www SQLServerBible

‘Com, застосовує цей програмний код до бази даних Northwind

Логічні таблиці Inserted І Deleted

SQL Server дозволяє програмному коду в тілі тригера отримати доступ до ефекту від транзакції, яка викликала тригер Логічні таблиці Inserted і Deleted являють собою образи даних, доступні тільки для читання Ці таблиці можна розглядати як представлення журналу транзакцій

Таблиця Deleted містить рядки в стані до застосування інструкції DML, а таблиця Inserted – після застосування (табл 232)

Таблиця 232 Таблиці inserted і Deleted

Інструкція DML

Таблиця Inserted

Таблиця Deleted

INSERT

Вставлені рядки

Порожня

UPDATE

Рядки бази даних після оновлення

Рядки бази даних до поновлення

DELETE

Порожня

Рядки, що підлягають видаленню

Таблиці Inserted і Deleted мають дуже обмежену область визначення Їх можна побачити тільки всередині тригера збережені процедури, що викликаються тригером, не мають доступу до цих таблиць

У наступному прикладі таблиця Inserted використовується для створення звіту про нові значеннях в стовпці LastName:

ALTER TRIGGER TriggerOne ON Person AFTER Insert, Update AS

SET NoCount ON IF Update(LastName)

SELECT Ви змінили значення стовпця LastName на

+ InsertedLastName FROM Inserted

Якщо цей тригер прикріпити до таблиці Person і оновити значення стовпця LastName наступним чином:

UPDATE Person

SET LastName = ’Johnson

WHERE PersonID =32

то отримаємо наступний результат:

Ви змінили значення стовпця LastName на Johnson (1 row (s) affected)

Створення тригерів, що працюють

з безліччю рядків

Багато тригери, які мені зустрічалися на практиці, не передбачали можливість роботи з багаторядковими інструкціями INSERT, UPDATE і DELETE Вони зчитували значення з таблиці Inserted або Deleted і зберігали його в локальної змінної для обробки або перевірки Така методика дозволяє обробляти тільки останній рядок, оброблену інструкцією DML, що створює велику пролом в системі підтримки цілісності даних Мені також зустрічалися бази даних, які для проходу за зміненими рядках використовували курсор Саме таке неналежне програмування і завоювало для тригерів погану репутацію

Так як SQL є середовищем, орієнтованої на роботу з множинами, будь тригер повинен враховувати можливість зміни інструкцією DML безлічі рядків У такій ситуації краще всього працювати з таблицями inserted і Deleted за допомогою операцій над множинами

Обєднання між таблицями Inserted і Deleted або між Inserted і вихідною таблицею поверне повне безліч рядків, на які справила вплив інструкція DML У табл 233 перераховані можливі комбінації обєднань, які можна використовувати в тригерах, що працюють з безліччю рядків

Таблиця 233 Пропозиції from для багаторядкових операцій

Інструкція DML

Пропозиція from

INSERT

UPDATE

INSERT, UPDATE DELETE

FROM Inserted FROM Inserted JOIN Deleted ON InsertedPK=DeletedPK FROM Inserted

LEFT OUTER JOIN Deleted ON InsertedPK=DeletedPK FROM Deleted

У наступному прикладі тригер переглядає таблиці Inserted і Deleted:

ALTER TRIGGER TriggerOne ON Person AFTER Insert, Update AS

SELECT DLastName + 1 змінений на 1 + ILastName FROM Inserted I JOIN Deleted D

ON IPersonID = DPersonID

UPDATE Person

SET LastName = Carter

WHERE LastName = Johnson 1

Буде отримано наступний результат:

Johnson змінений на Carter Johnson змінений на Carter (2 row (s) affected)

Наступний тригер, AFTER, витягнутий з навчальної бази даних Family, підтримує наступне правило: ідентифікатор батька Fat her ID не тільки повинен вказувати на існуюче обличчя, але до того ж дана людина повинен бути чоловіком:

CREATE TRIGGER Person_Parents ON Person

AFTER INSERT, UPDATE AS

IF UPDATE(FatherID)

BEGIN

– Невірний підлогу батька IF EXISTS (

SELECT *

FROM Person JOIN Inserted

ON InsertedFatherID = PersonPersonID WHERE PersonGender = 1F1 )

BEGIN

ROLLBACK

RAISERROR(Incorrect Gender for Father 1,14,1)

RETURN

END

END

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*