Главная » Microsoft SQL Server, Базы данных » Работа с транзакциями

0

Запускать триггер могут инструкции 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 dbo.GenColUpdated (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 =dbo.GenColUpdated(@ColCounter,©ColTotal)

If (@Col_Updated & ©ColUpdatedTemp) = @ColUpdatedTemp

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

‘ com, применяет этот программный код к базе данных Northwind.

Логические таблицы Inserted И Deleted

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

Таблица Deleted содержит строки в состоянии до применения инструкции DML, а таблица Inserted — после применения (табл. 23.2).

Таблица 23.2. Таблицы 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 на ‘

+ Inserted.LastName 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. В табл. 23.3 перечислены возможные комбинации объединений, которые можно использовать в триггерах, работающих с множеством строк.

Таблица 23.3. Предложения from для многострочных операций

Инструкция DML

Предложение from

INSERT

UPDATE

INSERT, UPDATE DELETE

FROM Inserted FROM Inserted JOIN Deleted ON Inserted.PK=Deleted.PK FROM Inserted

LEFT OUTER JOIN Deleted ON Inserted.PK=Deleted.PK FROM Deleted

В следующем примере триггер просматривает таблицы Inserted и Deleted:

ALTER TRIGGER TriggerOne ON Person AFTER Insert, Update AS

SELECT D.LastName + 1 изменен на 1 + I.LastName FROM Inserted I JOIN Deleted D

ON I.PersonID = D.PersonID;

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 Inserted.FatherID = Person.PersonID WHERE Person.Gender = 1F1 )

BEGIN

ROLLBACK

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

RETURN

END

END

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии