Где посмотреть триггеры в sql server

Использование Transact-SQL

Просмотр определения триггера DML

1. Соединитесь с ядром СУБД .
2. На панели «Стандартная» нажмите Создать запрос.
3. Скопируйте и вставьте один из следующих примеров в окно запроса и нажмите кнопку Выполнить. В каждом примере показано, как можно просмотреть определение триггера iuPerson .

USE AdventureWorks2022; GO SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID(N'Person.iuPerson'); GO 

USE AdventureWorks2022; GO SELECT OBJECT_DEFINITION (OBJECT_ID(N'Person.iuPerson')) AS ObjectDefinition; GO 

USE AdventureWorks2022; GO EXEC sp_helptext 'Person.iuPerson' GO 

Просмотр зависимостей триггера DML

1. Соединитесь с ядром СУБД .
2. На панели «Стандартная» нажмите Создать запрос.
3. Скопируйте и вставьте один из следующих примеров в окно запроса и нажмите кнопку Выполнить. В каждом примере показано, как можно просмотреть зависимости триггера iuPerson .

USE AdventureWorks2022; GO SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name, o.type_desc AS referencing_desciption, COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, referencing_class_desc, referenced_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name, is_caller_dependent, is_ambiguous FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id WHERE referencing_id = OBJECT_ID(N'Person.iuPerson'); GO 

Просмотр сведений о триггерах DML в базе данных

1. Соединитесь с ядром СУБД .
2. На панели «Стандартная» нажмите Создать запрос.
3. Скопируйте и вставьте один из следующих примеров в окно запроса и нажмите кнопку Выполнить. В каждом примере показано, как можно просмотреть сведения о триггерах DML ( TR ) в базе данных.

USE AdventureWorks2022; GO SELECT name, parent_id, create_date, modify_date, is_instead_of_trigger FROM sys.triggers WHERE type = 'TR'; GO 

USE AdventureWorks2022; GO SELECT name, object_id, schema_id, parent_object_id, type_desc, create_date, modify_date, is_published FROM sys.objects WHERE type = 'TR'; GO 

USE AdventureWorks2022; GO SELECT OBJECTPROPERTY(OBJECT_ID(N'Person.iuPerson'), 'ExecIsInsteadOfTrigger'); GO 

Просмотр сведений о событиях, которые вызывают срабатывание триггера DML

1. Соединитесь с ядром СУБД .
2. На панели «Стандартная» нажмите Создать запрос.
3. Скопируйте и вставьте один из следующих примеров в окно запроса и нажмите кнопку Выполнить. В каждом примере показано, как можно просмотреть события, которые вызывают срабатывание триггера iuPerson .

USE AdventureWorks2022; GO SELECT object_id, type, type_desc, is_trigger_event, event_group_type, event_group_type_desc FROM sys.events WHERE object_id = OBJECT_ID('Person.iuPerson'); GO 

USE AdventureWorks2022; GO SELECT object_id, type,is_first, is_last FROM sys.trigger_events WHERE object_id = OBJECT_ID('Person.iuPerson'); GO 

SQL-Ex blog

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

Что такое триггер SQL Server?

Триггер SQL Server — это часть процедурного кода, как и хранимая процедура, но который выполняется только при наступлении определенного события. Имеются различные типы событий, которые могут вызвать срабатывание триггера. Например, вставка строки в таблицу, изменение структуры таблицы или авторизация пользователя в экземпляре SQL Server.

• Пользователь не может выполнить триггеры вручную.
• Триггеры не принимают параметры.
• Вы не можете зафиксировать или откатить транзакцию внутри триггера.

Классы триггеров SQL Server

• Триггеры DDL (язык определения данных). Этот класс триггеров срабатывает на событиях, которые изменяют структуру (типа создания, модификации или удаления таблицы), или на определенных событиях, относящихся к серверу, например, событиях изменения безопасности или обновления статистики.
• Триггеры DML (язык модификации данных). Это наиболее часто используемый класс событий. Это события, которые вызывают срабатывание триггера при модификации данных; это может быть оператор insert, update или delete, применяемый к таблице или представлению.

• 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 >

В следующей таблице описывается каждый из аргументов синтаксиса 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.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись