Главная » Microsoft SQL Server, Базы данных » Аудит изменений данных

0

Введение аудита повышает уровень целостности данных в базе. Полноценный журнал аудита может содержать массу информации, в том числе приведенную ниже.

?               Все изменения данных в строках с момента их изначальной вставки.

?               Все изменения данных, внесенные конкретным пользователем за последнюю неделю.

?               Все изменения данных, внесенные на конкретной рабочей станции во время обеденного перерыва.

?               Все изменения данных, внесенные клиентскими приложениями, отличающимися от стандартного.

Ведение журнала аудита данных решает множество существенных проблем как для администраторов баз данных, так и для конечных пользователей. Однажды моя консультационная компания разработала систему управления юридическими документами для компании из списка Fortune 100. Эту базу данных постоянно должна была пополнять регуляторными законами юридическая фирма. Однажды эта фирма выбилась из графика и объяснила это тем, что не могла в течение двух недель вводить данные из-за проблем с программным обеспечением. Когда мы вывели список из более 70000 изменений данных, зафиксированных за эти две недели в журнале аудита данных, вопрос поиска виновного разрешился сам собой.

Мне приходилось встречать в публикациях методы аудита данных, добавляющие в таблицу несколько столбцов или дублирующие таблицы для сохранения последних изменений. Ни один из этих методов нельзя назвать приемлемым. Частичный аудит и аудит последних изменений не имеют практически никакого смысла. Журнал аудита должен постоянно хранить все изменения данных, в противном случае любой, кто поймет его систему действия, сможет внести еще одно изменение и тем самым навсегда стереть исходное значение.

Журнал аудита

Задачей таблицы (или журнала) аудита (Audit) является организация единого места хранения всех изменений в базе данных. Предлагаемая ниже таблица журнала аудита способна хранить все некрупные изменения (не BLOB), вносимые во все таблицы. Столбец Operation хранит значения, определяющие характер инструкции DML, ответственной за изменения (I, и или D):

CREATE TABLE dbo.Audit (

AuditID UNIQUEIDENTIFIER RowGUIDCol NOT NULL CONSTRAINT DF_Audit_Audi11D DEFAULT (NEWID() )

CONSTRAINT PK_Audit PRIMARY KEY NONCLUSTERED (AuditID),

AuditDate DATETIME NOT NULL,

SysUser VARCHAR(50) NOT NULL,

Application VARCHAR(50) NOT NULL,

TableName VARCHAR(50)NOT NULL,

Operation CHAR(l) NOT NULL,

PrimaryKey VARCHAR (50) NOT NULL,

RowDescription VARCHAR(50) NULL,

SecondaryRow VARCHAR (50) NULL,

[Column] VARCHAR(50) NOT NULL,

OldValue VARCHAR(50) NULL,

NewValue VARCHAR(50) NULL )

В столбце PrimaryKey хранится указатель на измененную строку, а в столбце RowDescription— осмысленное описание этой строки. Эти два столбца позволяют создать объединение с исходной таблицей и получить непосредственное описание операции. Значение столбца PrimaryKey имеет первостепенную важность, так как позволяет быстро отыскать все изменения в некоторой строке, независимо от того, как описание изменится со временем.

Фиксированный триггер журнала аудита

Наиболее простой метод аудита данных использует триггеры для всех таблиц, проверяющие все столбцы с помощью функции updated () и записывающие найденные изменения в таблицу аудита.

Инструкция INSERT объединяет таблицы Inserted и Deleted, чтобы корректно обработать все многострочные вставки и обновления. Это объединение является левым внешним, так что операции вставки, содержащие строки только в таблице Inserted, будут все равно записаны в журнал аудита. Данное объединение также ограничено условием, гарантирующим, что если многострочное обновление затрагивает столбцы только нескольких строк из этого множества, то только эти изменения будут записаны в таблицу аудита.

В приведенном ниже примере триггера журнала аудита отслеживаются изменения в таблице Product базы данных OBXKites.

CREATE TRIGGER Product_Audit ON dbo.Product AFTER Insert, Update NOT FOR REPLICATION AS

DECLARE (©Operation CHAR(l)

IF EXISTS(SELECT * FROM Deleted)

SET (©Operation = ‘U’

ELSE

SET (©Operation = 11′

IF UPDATE(ProductCategorylD)

INSERT dbo.Audit

(AuditDate, SysUser, Application, TableName, Operation,

PrimaryKey, RowDescription, SecondaryRow, [Column],

OldValue, NewValue)

SELECT GetDate(), suser_sname(), APP_NAME(), ‘Product’, (©Operation, Inserted.ProductID, Inserted.Code,

NULL, 1ProductCategorylD1,

ОРС.ProductcategoryName, NPC.ProductcategoryName FROM Inserted

LEFT OUTER JOIN Deleted

ON Inserted.ProductID = Deleted.ProductID AND Inserted.ProductCategorylD

<> Deleted.ProductCategorylD LEFT OUTER JOIN dbo.ProductCategory OPC ON Deleted.ProductCategorylD = OPC.ProductCategorylD JOIN dbo.ProductCategory NPC ON Inserted.ProductCategorylD = NPC.ProductCategorylD

IF UPDATE(Code)

INSERT dbo.Audit

(AuditDate, SysUser, Application, TableName, Operation, PrimaryKey, RowDescription, SecondaryRow, [Column], OldValue, NewValue)

SELECT GetDate(), suser_sname(), APP_NAME(),

‘Product’, ©Operation, Inserted.ProductID,

Inserted.Code, NULL, ‘Code’,

Deleted.Code, Inserted.Code FROM Inserted

LEFT OUTER JOIN Deleted

ON Inserted.ProductID = Deleted.ProductID AND Inserted.Code <> Deleted.Code

IF UPDATE(ProductName)

INSERT dbo.Audit

(AuditDate, SysUser, Application, TableName, Operation, PrimaryKey, RowDescription, SecondaryRow, [Column], OldValue, NewValue)

SELECT GetDate(), suser_sname(), APP_NAME(),

‘Product’, ©Operation,

Inserted.ProductID, Inserted.Code, NULL, ‘Name’, Deleted.ProductName, Inserted.ProductName FROM Inserted

LEFT OUTER JOIN Deleted

ON Inserted.ProductID = Deleted.ProductID

AND Inserted.ProductName <> Deleted.ProductName

IF UPDATE(ProductDescription)

INSERT dbo.Audit

(AuditDate, SysUser, Application, TableName, Operation, PrimaryKey, RowDescription, SecondaryRow, [Column], OldValue, NewValue)

SELECT GetDateO , suser_sname () , APP_NAME“<) / ‘Product’ , ©Operation, Inserted.ProductID, Inserted.Code,

NULL, ‘ProductDescription’,

Deleted.ProductDescription,

Inserted.ProductDescription FROM Inserted

LEFT OUTER JOIN Deleted

ON Inserted.ProductID = Deleted.ProductID AND Inserted.ProductDescription <> Deleted.ProductDescription

IF UPDATE(ActiveDate)

INSERT dbo.Audit

(AuditDate, SysUser, Application, TableName, Operation,

PrimaryKey, RowDescription, SecondaryRow, [Column],

OldValue, NewValue)

SELECT GetDate() , suser_sname() , APP_NAME() , 1 Product1, @Operation, Inserted.ProductID, Inserted.Code,

NULL, ‘ActiveDate’,

Deleted.ActiveDate, Inserted.ActiveDate FROM Inserted

LEFT OUTER JOIN Deleted

ON Inserted.ProductID = Deleted.ProductID

AND Inserted.ActiveDate != Deleted.ActiveDate

IF UPDATE(DiscontinueDate)

INSERT dbo.Audit

(AuditDate, SysUser, Application, TableName, Operation,

PrimaryKey, RowDescription, SecondaryRow, [Column],

OldValue, NewValue)

SELECT GetDate(), suser_sname(), APP_NAME() , ‘Product1, ©Operation, Inserted.ProductID, Inserted.Code,

NULL, ‘DiscontinueDate1,

Deleted.DiscontinueDate, Inserted.DiscontinueDate FROM Inserted

LEFT OUTER JOIN Deleted

ON Inserted.ProductID = Deleted.ProductID AND Inserted.DiscontinueDate

!= Deleted.DiscontinueDate

Если установить этот триггер аудита, то следующий пакет вставки и обновления данных о товарах, использующий две инструкции DML, а также созданные ранее хранимые процедуры, позволит его проверить. Первый тест использует хранимую процедуру pProduct_AddNew:

ЕХЕС pProduct_AddNew ‘Kite’, 200, ‘The MonstaKite’,

‘Man what a big Kite!’

SELECT TableName, RowDescription, [Column], NewValue FROM dbo.Audit

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

TableName RowDescription

Column                                            NewValue

Product    2 00                                    ProductCategorylD        Kite

Product    200                                     Code    200

Product    2 00                                    Name   The MonstaKite

Product    200                                     ProductDescription       Man what a big Kite!

Product    200                                     ActiveDate       Mar 1 2 0 02 1:35PM

Product    2 00                                    DiscontinueDate           NULL

Триггеры являются отличным средством ведения журнала аудита, так как регистрируют все изменения, даже выполняемые прямым вводом инструкций DML. В следующем примере мы не будем использовать хранимые процедуры и выполним обычную инструкцию UPDATE. После этого в журнале аудита мы увидим как старое, так и новое значение:

UPDATE dbo.Product

SET ProductDescription = ‘Biggie Sized’

WHERE Code = 2 00

Следующий запрос извлечет для нас историю столбца ProductDescription для товара с кодом 200:

SELECT AuditDate, OldValue, NewValue FROM dbo.Audit WHERE TableName = 1 Product1 AND RowDescription = ‘200’

AND [Column] = ‘ProductDescription’

Получим следующий результат:

AuditDate                                OldValue                          NewValue

2002-03-01 13:35:17.093 NULL                                     Man what a

big Kite!

2002-03-01 15:10:49.257 Man what a                             Biggie Sized

big Kite!

Выполнение отката операций с помощью журнала аудита

В полноценной системе аудита пользователь может увидеть все изменения конкретной строки, внесенные с момента ее создания. В этом списке пользователь может выбрать любую операцию и откатить ее назад. Как только выбрана некоторая строка журнала аудита, откат операции сводится к обычному выполнению инструкции UPDATE, использующей данные журнала аудита.

В следующем примере продемонстрирован откат изменения, зарегистрированного в журнале аудита. Хранимая процедура pAudit_RollBack принимает в качестве параметра первичный ключ таблицы Audit, после чего создает динамическую инструкцию UPDATE, содержащую корректные таблицу, строку, столбец и восстанавливаемое значение:

CREATE PROCEDURE pAudit_RollBack (

@AuditID UNIQUEIDENTIFIER)

AS

SET NoCount ON

DECLARE

@SQLString NVARCHAR(4000),

@TableName NVARCHAR(50),

@PrimaryKey NVARCHAR(50),

@Column NVARCHAR(50),

@NewValue NVARCHAR(50)

SELECT

@TableName = TableName,

@PrimaryKey = PrimaryKey,

@Column = [Column],

@NewValue = OldValue

FROM dbo.Audit

WHERE AuditID = @AuditID

SET @SQLString =

‘UPDATE ‘ + @TableName

+ ‘ SET ‘ + @Column +’=”’+ @NewValue +””

+ ‘ WHERE ‘ + ©TableName + ‘ID = ”’ + ©PrimaryKey + 11,1 EXEC sp_executeSQL @SQLString Return

Имея в своем распоряжении данную хранимую процедуру, следующий сценарий имитирует логику, необходимую для отката операции обновления. Как вы помните, исходным описанием

товара с кодом 200 было “Man what a big Kite”; во время тестирования триггера журнала аудита оно было изменено на “Biggie Sized”. Следующий сценарий находит строку, соответствующую этому изменению, и передает идентификатор GUTO процедуре pAudit_RollBack, которая, в свою очередь, откатывает данное обновление:

DECLARE @AuditRollBack UNIQUEIDENTIFIER

SELECT @AuditRollBack = AuditID FROM dbo.Audit WHERE TableName = ‘Productr AND RowDescription = ‘200’

AND OldValue = ’Man what a big Kite I’

EXEC pAudit_RollBack @AuditRollBack

SELECT ProductDescription FROM dbo.Product WHERE Code = 2 00

Результат выполнения пакета:

ProductDescription

Man what a big Kite!

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

Сложности аудита

Кроме дополнительного времени, необходимого на программирование, ведение аудита влечет за собой и другие сложности.

Спроектируйте всю базу данных и проверьте корректность ее схемы еще до реализации системы аудита. Внести изменения в схему будет намного сложнее, когда триггеры журнала аудита уже будут в наличии.

Аудит связанных данных

Наибольшую сложность представляет аудит связанных данных, таких как вторичные строки. Например, внесение изменений в строку заказа фактически изменяет весь заказ. Когда пользователь захочет просмотреть всю историю заказа, он должен увидеть все изменения, связанные с ним. Таким образом, внесение изменения в таблицу строк заказа должно быть записано и как изменение таблицы заказов, при этом номер измененной строки заказа должен быть отображен в столбце Secondary Row.

Запись изменений, вносимых во внешние ключи, представляет собой еще один сложный аспект полноценного аудита. Пользователю вряд ли захочется видеть новый идентификатор GUID или значение идентичности. Если, к примеру, способ доставки товара изменился со “Slow Boat” на “Speedy Express”, то триггер журнала аудита должен будет перейти по внешнему ключу и записать в журнал понятное пользователю значение. В приведенном ранее примере фиксированного триггера Product_Audit изменения, вносимые в столбец идентификатора категории товара, приводят к записи в журнал аудита названия этой категории.

Создаваемые и изменяемые даты

При использовании полноценной системы аудита из журнала можно извлечь даты создания и последнего изменения любой строки. На практике же в многопользовательской среде с большим потоком данных я настоятельно рекомендую выполнить денормализацию и вынести эти даты непосредственно в таблицу аудита.

Дополнительная В главе 23 был описан триггер, обновляющий созданные и обновленные столб- чфорМсщиг, цы и одновременно предотвращающий проблемы, связанные с рекурсией.

Аудит инструкций SELECT

Триггеры аудита данных ограничены инструкциями INSERT, UPDATE и DELETE. Для реализации аудита чтения данных используйте хранимую процедуру fetch. Используйте средства защиты информации SQL Server для ограничения доступа к таблицам; при этом создавайте систему таким образом, чтобы все операции чтения выполнялись с помощью хранимой процедуры или функции.

Аудит данных и вопросы безопасности

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

Аудит данных и производительность

Полноценный журнал аудита данных добавляет на систему определенную нагрузку. Любая вставка одной строки в таблицу с двадцатью столбцами повлечет за собой выполнение двадцати инструкций вставки в таблицу аудита. Чтобы несколько снизить дополнительную нагрузку, примите следующие меры.

И Ограничьте число индексов в таблице аудита.

И Размещайте таблицу аудита в отдельной файловой группе в отдельной дисковой подсистеме. Наличие отдельной файловой группы также упростит и выполнение резервного копирования.

?               Используя фиксированные триггеры аудита, ограничьтесь только теми столбцами, которые требуют высокого уровня целостности данных.

Динамические триггеры и процедуры журнала аудита

Проведя несколько месяцев за написанием фиксированных триггеров аудита для проекта с сотнями таблиц, я однозначно пришел к решению использовать динамическую систему аудита.

Динамический журнал аудита требует небольших триггеров в каждой из таблиц. Эти триггеры всего лишь копируют таблицы Inserted и Deleted во временные, после чего передают несколько переменных хранимой процедуре, на которую возложена основная работа. Та проверяет двоичное значение Columns_Updated, после чего определяет правильное значение, используемое вместо пустого, а затем генерирует динамическую инструкцию SQL, выполняющую запись в журнал аудита.

Вот в чем состоит ключевой момент. Для выполнения динамической инструкции SQL требуется вызов системной хранимой процедуры sp_execSQL, которая функционирует во вложенном пакете Т-SQL. Область определения таблиц Inserted и Deleted ограничена триггером — эти таблицы недоступны вызывающей процедуре или динамической инструкции SQL. Именно поэтому и создаются временные таблицы, которые уже и передают изменения в хранимую процедуру и впоследствии в динамический запрос SQL. Область определения временной таблицы включает в себя вызывающую хранимую процедуру и исполняемые модули, что и делает возможным создание динамического триггера аудита.

Естественно, любой метод имеет свои достоинства и недостатки. С положительной стороны метод “триггер/хранимая процедура/динамический SQL” чрезвычайно легко реализовать. Он способен работать практически с любыми таблицами. В то же время существуют и некоторые ограничения этой версии динамического триггера аудита.

?               Для передачи значений таблиц Inserted и Deleted в хранимую процедуру он использует временные таблицы, поэтому его трудно назвать самым быстрым методом. К тому же временные таблицы создаются с помощью синтаксиса SELECT. . . INTO, что связано с дополнительными вопросами производительности и блокировки.

?               Текущий динамический журнал аудита не отслеживает автоматически связанные данные и вторичные таблицы. Он также не способен вести аудит таблиц с составными первичными ключами.

?               Он не ведет аудит таблиц со столбцами BLOB (типов image, t axt и ntext), поскольку их нельзя извлечь из таблиц Inserted и Deleted.

Исходя из этих ограничений, рекомендуется использовать метод динамического аудита только для таблиц, которые не обновляются часто, а также для баз данных, которые при использовании этого метода имеют нормальные показатели производительности. Для обеспечения высокой производительности лучше использовать метод фиксированных триггеров аудита, в которых “прошит” состав столбцов; в то же время этот метод предполагает намного больше программирования и сложности в сопровождении.

Принимая во внимание вышесказанное, приведем пример программного кода ведения динамического журнала аудита:

/*

Создание динамических журнала и триггера аудита.

Пол Нильсен

В этом примере к таблицам Customers и Products базы данных Northwind добавляется динамический триггер аудита.

Версия 1.1-6 августа 2001 */

USE Northwind

–Создание таблицы для хранения журнала аудита IF Exists (SELECT * FROM sysobjects WHERE NAME = ‘Audit’) DROP TABLE Audit

Go

CREATE TABLE dbo.Audit (

AuditID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL CONSTRAINT DF_Audit_AuditID DEFAULT (NEWIDO)

CONSTRAINT PK_Audit PRIMARY KEY NONCLUSTERED (AuditID), AuditDate DATETIME NOT NULL,

SysUser VARCHAR(50) NOT NULL,

Application VARCHAR(50) NOT NULL,

TableName VARCHAR(50)NOT NULL,

Operation CHAR(l) NOT NULL,

PrimaryKey VARCHAR(50) NOT NULL,

– RowDescription VARCHAR(50) NULL,

SecondaryRow VARCHAR(50) NULL,

[Column] VARCHAR(50) NOT NULL,

OldValue VARCHAR(50) NULL,

NewValue VARCHAR(50) NULL )

GO

– Создание функции для имитации значения Columns_Updated() IF EXISTS (SELECT *

FROM sysobjects

WHERE NAME = ‘GenColUpdated’)

DROP FUNCTION GenColUpdated

Go

CREATE FUNCTION dbo.GenColUpdated (OCol INT, @ColTotal INT)

RETURNS INT AS

BEGIN

– Эта функция имитирует Columns_Updated()

DECLARE

@ColByte INT,

@ColTotalByte INT,

@ColBit INT — Вычисление позиций байтов

SET @ColTotalByte = 1 + ((@ColTotal-l) /8)

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

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

– генерация значения Columns_Updated() для данного столбца RETURN

POWER(2, @colbit + ((@ColTotalByte-@ColByte) * 8)-l)

END

go

– Создание хранимой процедуры динамического аудита IF EXISTS (SELECT * FROM SysObjects WHERE NAME = ‘pAudit’) DROP PROC pAudit

Go

CREATE PROCEDURE pAudit (

@Col_Updated VARBINARY(1028),

@TableName VARCHAR(100),

@PrimaryKey SYSNAME)

AS

SET NoCount ON DECLARE

@ColTotal INT,

@ColCounter INT,

@ColUpdatedTemp INT,

@ColName SYSNAME,

@BlankString CHAR(l),

@SQLStr NVARCHAR(1000),

@ColNull NVARCHAR(50),

@SysUser NVARCHAR(100),

@ColumnDataType INT,

@IsUpdate BIT,

@tempError INT SET @SysUser = suser_sname()

SET @BlankString = ‘1

– Инициализация переменных столбцов SELECT @ColCounter = 0 SELECT @ColTotal = Count(*)

FROM SysColumns JOIN SysObjects

ON SysColumns.id = SysObjects.id WHERE SysObjects.name = @TableName — Установка флага IsUpdated IF EXISTS(SELECT * FROM #tempDel)

SELECT @IsUpdate = 1 ELSE

SELECT @IsUpdate = 0 — Обновления столбцов

WHILE ((SELECT @ColCounter) 1= @ColTotal)

BEGIN

SELECT @ColCounter = @ColCounter + 1 SET @ColUpdatedTemp

= dbo.GenColUpdated(@ColCounter,@ColTotal)

– битовый AND между обновляемыми битами — и битом выбранного столбца

IF (@Col_Updated & @ColUpdatedTemp) = @ColUpdatedTemp BEGIN

SET @ColNull = null SELECT

@ColName = SysColumns.[name],

– получение имени столбца и типа данных

@ColumnDataType = SysColumns.xtype FROM SysColumns JOIN SysObjects

ON SysColumns.id = SysObjects.id WHERE SysObjects.[NAME] = @TableName

and SysColumns.ColID = @ColCounter IF @ColName NOT IN (‘Created1, ‘Modified’)

BEGIN — текстовые столбцы

IF @ColumnDataType IN

( 175, 239, 99, 231, 35, 231, 98, 167 )

SET @ColNull =

– числовые и битовые столбцы

ELSE IF @ColumnDataType IN

( 106, 62, 56, 60, 108, 59, 52, 122, 104 SET @ColNull = ‘O’

– столбцы дат

ELSE IF @ColumnDataType IN ( 61, 58 )

SET @ColNull = ‘ ‘ ‘1/1/1980′ ‘ ‘

– столбцы уникальных идентификаторов

ELSE IF @ColumnDataType IN ( 36 ) SET @ColNull = ?’????

IF @ColNull IS NOT NULL BEGIN

IF @IsUpdate = 1 — нужно скорректировать отступы SET @SQLStr =

‘ Insert Audit(TableName, PrimaryKey, SysUser, [Column],’ +’ AuditDate, Application, OldValue, NewValue,Operation)’ + ‘ Select ‘ ‘ ‘+ ©TableName + ‘ 1 1 ,

#templn.[‘+ ©PrimaryKey + ‘],

111 + ©SysUser + 11, ‘ +

”’1 + ©ColName + 1”, GetDate(), App_Name(),1 +

‘ IsNul1(convert(nvarchar(100) ,

#tempDel.[1 + ©ColName + ‘]),”<null>11), ‘ +

1 IsNull(convert(nvarchar(100) ,

#templn.[‘ + @ColName + ‘]),”<null>”),”U”’ +

1 From #templn’ +

1 Join #tempDel1 +

‘ On #templn.[’ + @PrimaryKey + ‘]

= #tempDel. [1 + ©PrimaryKey + ‘]1 +

‘ AND isnull(#templn.1 + @ColName + ‘,’ + @ColNull + ‘)

!= isnull(#tempDel.1 + ©ColName + ‘,’ + @ColNull + ‘)’

+ ’ Where Not (#templn.[‘ + @ColName + ‘] Is Null and #tempDel.[‘ + @ColName + ‘] Is Null)1 ELSE — Вставка SET @SQLStr =

‘ Insert Audit(TableName, PrimaryKey, SysUser, [Column],’ +’ AuditDate, Application, OldValue, NewValue,Operation)’ +’ Select ”’+ @TableName + 111,#templn.[‘+ @PrimaryKey + ‘ ] , ‘ ‘ ‘ + @SysUser + ‘ ‘ ‘, ‘ +

‘ ‘ ‘ ‘ 4- ©ColName + ‘ ‘ ‘ , GetDateO , App_Name() , 1 +

1    Null, ‘ +

‘ IsNull(convert(nvarchar(100),

#templn.[‘ + ©ColName +’]),”<null>”),”I”’ +

‘ From #templn’ +

‘ Where Not (#templn.[‘ + @ColName + ‘] Is Null)’

EXEC sp_execuuesql ©SQLStr SET ©TempError = ©@Error IF ©TempError <> 0 BEGIN

– включайте откат, только если хотите,

– чтобы ошибка при записи в журнал аудита — отменяла операцию модификации данных — Откат

RAISERROR (‘Ошибка записи в журнал аудита’, 15, 1)

END

END END END END RETURN Go

– Примеры табличных триггеров

– Они должны быть добавлены ко всем таблицам — с настройками имени таблицы и первичного ключа — Триггер для таблицы Products IF EXISTS (SELECT *

FROM sysobjects

WHERE NAME = ‘Products_Audit’)

DROP TRIGGER Products_Audit

Go

CREATE TRIGGER Products_Audit ON dbo.Products AFTER Insert, Update NOT FOR REPLICATION

AS

DECLARE

@Col_Updated VARBINARY(1028),

@TableName VARCHAR (10 0) ,

@PrimaryKey SYSNAME SET NoCount ON

– Настройка данных для аудита — Установка имени таблицы SET @TableName = ‘Products’

– Настройка столбца, идентифицирующего строку SET @PrimaryKey = ’ProductID’

SET @Col_Updated = Columns_Updated()

SELECT * INTO #TempIn FROM Inserted

SELECT * INTO #TempDel FROM Deleted

– Вызов хранимой процедуры аудита

ЕХЕС pAudit @Col_Updated, @TableName, @PrimaryKey

Go

– Триггер для таблицы Customers IF EXISTS (SELECT *

FROM SysObjects

WHERE [NAME] = ’Customers_Audit’)

DROP TRIGGER Customers_Audit

Go

CREATE TRIGGER Customers_Audit ON dbo.Customers AFTER Insert, Update NOT FOR REPLICATION

AS

DECLARE

@Col_Updated VARBINARY(1028),

@Tab1eName VARCHAR(10 0),

@PrimaryKey SYSNAME SET NoCount ON SET @TableName = ’Customers’

SET @PrimaryKey = ‘CustomerlD’

SET @Col_Updated = Columns_Updated()

SELECT * INTO #TempIn FROM Inserted SELECT * INTO #TempDel FROM Deleted EXEC pAudit @Col_Updated, @TableName, @PrimaryKey

В сценарии DynamicAudit. sql, который можно загрузить с сайта книги (www. SQLServerBible.com), содержится несколько примеров вставки и удаления, а также запросов извлечения данных из таблицы аудита, при объединении ее с таблицей товаров.

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

По теме:

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