Пример триггера в SQL Server
Пересказ статьи Daniel Farina. SQL Server Trigger Example
Триггеры являются одними из наименее понимаемыми вещами для новичков в SQL Server. Возможно, это объясняется тем фактом, что они допускают почти всю функциональность, которой обладают хранимые процедуры, что вызывает затруднения неопытных разработчиков при выборе между хранимыми процедурами и триггерами.
Что такое триггер SQL Server?
Триггер SQL Server - это часть процедурного кода, как и хранимая процедура, но который выполняется только при наступлении определенного события. Имеются различные типы событий, которые могут вызвать срабатывание триггера. Например, вставка строки в таблицу, изменение структуры таблицы или авторизация пользователя в экземпляре SQL Server.
Три главных характеристики, которые отличают триггеры от хранимых процедур:
- Пользователь не может выполнить триггеры вручную.
- Триггеры не принимают параметры.
- Вы не можете зафиксировать или откатить транзакцию внутри триггера.
Тот факт, что невозможно использовать параметры с триггерами, не является ограничением для получения информации от события, которое вызывает срабатывание. Как вы увидите ниже, имеются альтернативы для получения информации о событии, вызвавшем срабатывание триггера.
Классы триггеров SQL Server
Имеется два класса триггеров в SQL Server:
- Триггеры DDL (язык определения данных). Этот класс триггеров срабатывает на событиях, которые изменяют структуру (типа создания, модификации или удаления таблицы), или на определенных событиях, относящихся к серверу, например, событиях изменения безопасности или обновления статистики.
- Триггеры DML (язык модификации данных). Это наиболее часто используемый класс событий. Это события, которые вызывают срабатывание триггера при модификации данных; это может быть оператор insert, update или delete, применяемый к таблице или представлению.
Кроме того, триггеры DML имеют различные типы:
- FOR или AFTER [INSERT, UPDATE, DELETE]: Эти типы триггеров выполняются после завершения выполнения оператора, вызвавшего срабатывание триггера (insert, update или delete).
- INSTEAD OF [INSERT, UPDATE, DELETE]: в отличие от типа FOR (AFTER) триггеры INSTEAD OF выполняются вместо оператора, вызвавшего срабатывание триггера. Другими словами, этот тип триггера заменяет вызвавший его оператор. Это очень полезно в случаях, когда вам нужно обеспечить ссылочную целостность между базами данных.
Почему триггеры важны?
Одной из фундаментальных характеристик реляционных баз данных является согласованность данных. Это означает, что информация, хранимая в базе данных должна быть согласована все время для каждой сессии и каждой транзакции. Способ, которым ядро реляционной системы баз данных типа SQL Server обеспечивает согласованность, заключается во введении ограничений, как-то: первичные и внешние ключи. Но иногда этого оказывается недостаточно.
В SQL Server нет возможности обеспечить ссылочную целостность между двумя таблицами посредством внешних ключей, если эти таблицы находятся в разных базах данных или на разных серверах. В таких случаях можно применить только один способ - использование триггеров.
Как узнать, какие строки были обновлены, вставлены или удалены, используя триггер DML в SQL Server?
В случае триггеров DML при их выполнении создаются две виртуальных таблицы, которые содержат данные, на которые влияет выполнение триггера. Эти таблицы называются inserted и deleted, и они имеют ту же структуру, что и структура базовой таблицы.
Следует иметь в виду, что таблицы inserted и deleted не всегда доступны вместе (т.е. вы можете иметь таблицу inserted, но не иметь таблицы deleted, и наоборот). Вы можете найти больше информации об этих таблицах в следующей статье.
Синтаксис триггера DML в SQL Server
Вот базовый синтаксис команды создания триггера CREATE TRIGGER.
CREATE TRIGGER trigger_name
ON { Table name or view name }
[ WITH ]
{ FOR | AFTER | INSTEAD OF }
{ [INSERT], [UPDATE] , [DELETE] } В следующей таблице описывается каждый из аргументов синтаксиса CREATE TRIGGER.
Сценарии использования триггеров в SQL Server
Имеется два понятных сценария, когда триггеры являются лучшим выбором: аудит и соблюдение бизнес-правил. Используя триггер, вы можете отслеживать изменения на заданной таблице путем записи в журнал информации о том, кто выполнил изменение, и какие изменения были сделаны.
Вы можете подумать, что могли бы сделать то же самое в приложении с помощью хранимой процедуры, которая обрабатывает модификацию данных типа вставок и обновлений. Вы можете использовать хранимую процедуру, но в таком случае вы не сможете журнализировать изменения, которые были сделаны непосредственно в базе данных вне приложения.
То же самое имеет место, когда вы хотите обеспечить соблюдение бизнес-правил с помощью хранимой процедуры. Если кто-то модифицирует данные в базовой таблице извне приложения, у вас может возникнуть проблема, поскольку согласованность данных в этом случае не будет гарантирована. Чтобы избежать подобных проблем, вы могли бы обеспечить доступ к данным в таблице только посредством хранимой процедуры.
Простой триггер SQL Server DML
Предположим, что у нас есть база данных для департамента людских ресурсов. База данных содержит таблицу Employees для хранения персональной информации и зарплаты. С помощью триггера мы можем сохранить запись аудита в отдельной таблице, которая содержит каждое изменение записи, а также пользователя, который выполнил изменение, и время изменения.
Сначала мы должны создать таблицу Employees.
CREATE TABLE Employees
(
EmployeeID integer NOT NULL IDENTITY(1, 1) ,
EmployeeName VARCHAR(50) ,
EmployeeAddress VARCHAR(50) ,
MonthSalary NUMERIC(10, 2)
PRIMARY KEY CLUSTERED (EmployeeID)
)
GOЗатем мы должны создать таблицу EmployeesAudit для хранения записей аудита. Эта таблица имеет ту же структуру, что и таблица Employees, плюс включает столбец AuditId в качестве первичного ключа, ModifiedDate для хранения даты модификации, ModifiedBy для того, чтобы мы могли узнать, кто модифицировал таблицу Employees, и, наконец, Operation, где будет указываться операция DML, которая сгенерировала запись аудита, одной из трех букв ( I для вставки, U для обновления и D для удаления).
CREATE TABLE EmployeesAudit
(
AuditID INTEGER NOT NULL IDENTITY(1, 1) ,
EmployeeID INTEGER ,
EmployeeName VARCHAR(50) ,
EmployeeAddress VARCHAR(50) ,
MonthSalary NUMERIC(10, 2) ,
ModifiedBy VARCHAR(128) ,
ModifiedDate DATETIME ,
Operation CHAR(1)
PRIMARY KEY CLUSTERED ( AuditID )
)
GOЧтобы протестировать триггер, нам потребуется добавить некоторые данные в таблицу Employees.
INSERT INTO dbo.Employees
( EmployeeName ,
EmployeeAddress ,
MonthSalary
)
SELECT 'Mark Smith', 'Ocean Dr 1234', 10000
UNION ALL
SELECT 'Joe Wright', 'Evergreen 1234', 10000
UNION ALL
SELECT 'John Doe', 'International Dr 1234', 10000
UNION ALL
SELECT 'Peter Rodriguez', '74 Street 1234', 10000
GOТеперь, когда мы имеем все для тестирования, пора создать наш триггер Посмотрите код ниже.
CREATE TRIGGER TR_Audit_Employees ON dbo.Employees
FOR INSERT, UPDATE, DELETE
AS
DECLARE @login_name VARCHAR(128)
SELECT @login_name = login_name
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
IF EXISTS ( SELECT 0 FROM Deleted )
BEGIN
IF EXISTS ( SELECT 0 FROM Inserted )
BEGIN
INSERT INTO dbo.EmployeesAudit
( EmployeeID ,
EmployeeName ,
EmployeeAddress ,
MonthSalary ,
ModifiedBy ,
ModifiedDate ,
Operation
)
SELECT D.EmployeeID ,
D.EmployeeName ,
D.EmployeeAddress ,
D.MonthSalary ,
@login_name ,
GETDATE() ,
'U'
FROM Deleted D
END
ELSE
BEGIN
INSERT INTO dbo.EmployeesAudit
( EmployeeID ,
EmployeeName ,
EmployeeAddress ,
MonthSalary ,
ModifiedBy ,
ModifiedDate ,
Operation
)
SELECT D.EmployeeID ,
D.EmployeeName ,
D.EmployeeAddress ,
D.MonthSalary ,
@login_name ,
GETDATE() ,
'D'
FROM Deleted D
END
END
ELSE
BEGIN
INSERT INTO dbo.EmployeesAudit
( EmployeeID ,
EmployeeName ,
EmployeeAddress ,
MonthSalary ,
ModifiedBy ,
ModifiedDate ,
Operation
)
SELECT I.EmployeeID ,
I.EmployeeName ,
I.EmployeeAddress ,
I.MonthSalary ,
@login_name ,
GETDATE() ,
'I'
FROM Inserted I
END
GOВ начале код содержит получение пользователя, который модифицирует таблицу Employees, обращаясь к динамическому административному представлению sys.dm_exec_sessions для получения сессии по текущему
ИД сессии (SPID). Потом триггер вставляет одну запись в таблицу EmployeesAudit для каждой вставленной, обновленной или удаленной записи в таблице Employees, а так же текущее время и операцию DML, которая вызвала срабатывание триггера.
Для тестирования триггера я написал три запроса. Я разместил код внутри транзакции, просто чтобы сохранить порядок в моей тестовой среде, который вы можете опустить.
Первый из этих запросов выполняет обновление.
BEGIN TRANSACTION
SELECT *
FROM dbo.Employees
WHERE EmployeeID = 1
UPDATE Employees
SET EmployeeName = 'zzz'
WHERE EmployeeID = 1
SELECT *
FROM dbo.Employees
WHERE EmployeeID = 1
SELECT *
FROM dbo.EmployeesAudit
ROLLBACK TRANSACTION На следующем скриншоте вы увидите обновленную запись в таблице Employees и новую запись таблице EmployeesAudit, которая отслеживает операцию DML в таблице Employees.
Второй запрос вставляет две строки в таблицу Employees.
BEGIN TRANSACTION
INSERT INTO dbo.Employees
( EmployeeName ,
EmployeeAddress ,
MonthSalary
)
SELECT 'zz' ,
'dsda' ,
10000
UNION ALL
SELECT 'Markus Rubius' ,
'dsda' ,
6000
SELECT *
FROM dbo.Employees
SELECT *
FROM dbo.EmployeesAudit
ROLLBACK TRANSACTIONНа скриншоте ниже вы можете увдеть две вставленные записи в таблице Employees и соответствующие записи аудита в таблице EmployeesAudit.
Наконец, третий запрос - оператор удаления из таблицы Employees.
BEGIN TRANSACTION
SELECT *
FROM dbo.Employees
WHERE EmployeeID = 1
DELETE FROM dbo.Employees
WHERE EmployeeID = 1
SELECT *
FROM dbo.EmployeesAudit
SELECT *
FROM dbo.Employees
WHERE EmployeeID = 1
ROLLBACK TRANSACTION На скриншоте видно удаление строки из таблицы Employees и соответствующую запись аудита в таблице EmployeesAudit.
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой