ENABLE TRIGGER (Transact-SQL)
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
schema_name
Имя схемы, к которой принадлежит триггер. Аргумент schema_name не может указываться для триггеров DDL или триггеров входа.
trigger_name
Имя триггера.
ALL
Указывает, что включаются все триггеры, определенные в области предложения ON.
object_name
Имя таблицы или представления, для выполнения которых создан триггер DML с именем trigger_name.
DATABASE
Показывает, что триггер DDL trigger_name был создан или изменен для выполнения в области базы данных.
ALL SERVER
Область применения: SQL Server 2008 (10.0.x) и более поздних версий.
Показывает, что триггер DDL trigger_name был создан или изменен для выполнения в области сервера. Параметр ALL SERVER также применяется к триггерам входа.
Этот параметр недоступен в автономной базе данных.
Remarks
При включении триггера он не создается повторно. Отключенный триггер не может быть запущен, но продолжает существование в виде объекта в текущей базе данных. Включение триггера приводит к его запуску при выполнении любых инструкций Transact-SQL, на которые он изначально был запрограммирован. Триггеры отключаются с помощью инструкции DISABLE TRIGGER. Триггеры DML, определенные в таблицах, также можно отключать или включать с помощью ALTER TABLE.
Разрешения
Чтобы включить триггер DML, пользователю необходимо, как минимум, обладать разрешением ALTER для таблицы или представления, на которые был создан триггер.
Для включения триггера DDL в области сервера (ON ALL SERVER) или триггера входа пользователю необходимо обладать разрешением CONTROL SERVER на этот сервер. Чтобы включить триггер DDL в области базы данных (ON DATABASE), пользователю необходимо, как минимум, иметь разрешение ALTER ANY DATABASE DDL TRIGGER для текущей базы данных.
Примеры
A. Включение триггера DML для таблицы
В данном примере отключается триггер uAddress , созданный для таблицы Address в базе данных AdventureWorks, а затем он включается.
DISABLE TRIGGER Person.uAddress ON Person.Address; GO ENABLE Trigger Person.uAddress ON Person.Address; GO Б. Включение триггера DDL
В следующем примере создается триггер DDL safety в области базы данных, а затем он отключается и включается.
CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger "safety" to drop or alter tables!' ROLLBACK; GO DISABLE TRIGGER safety ON DATABASE; GO ENABLE TRIGGER safety ON DATABASE; GO В. Включение всех триггеров, определенных в одной области
В следующем примере включаются все триггеры DDL, созданные в области сервера.
Область применения: SQL Server 2008 (10.0.x) и более поздних версий.
ENABLE Trigger ALL ON ALL SERVER; GO Триггеры
Триггеры представляют специальный тип хранимой процедуры, которая вызывается автоматически при выполнении определенного действия над таблицей или представлением, в частности, при добавлении, изменении или удалении данных, то есть при выполнении команд INSERT, UPDATE, DELETE.
Формальное определение триггера:
CREATE TRIGGER имя_триггера ON [INSERT | UPDATE | DELETE] AS выражения_sql
Для создания триггера применяется выражение CREATE TRIGGER , после которого идет имя триггера. Как правило, имя триггера отражает тип операций и имя таблицы, над которой производится операция.
Каждый триггер ассоциируется с определенной таблицей или представлением, имя которых указывается после слова ON .
Затем устанавливается тип триггера. Мы можем использовать один из двух типов:
- AFTER : выполняется после выполнения действия. Определяется только для таблиц.
- INSTEAD OF : выполняется вместо действия (то есть по сути действие — добавление, изменение или удаление — вообще не выполняется). Определяется для таблиц и представлений
После типа триггера идет указание операции, для которой определяется триггер: INSERT , UPDATE или DELETE .
Для триггера AFTER можно применять сразу для нескольких действий, например, UPDATE и INSERT. В этом случае операции указываются через запятую. Для триггера INSTEAD OF можно определить только одно действие.
И затем после слова AS идет набор выражений SQL, которые собственно и составляют тело триггера.
Создадим триггер. Допустим, у нас есть база данных productsdb со следующим определением:
CREATE DATABASE productdb; GO USE productdb; CREATE TABLE Products ( Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL );
Определим триггер, который будет срабатывать при добавлении и обновлении данных:
USE productdb; GO CREATE TRIGGER Products_INSERT_UPDATE ON Products AFTER INSERT, UPDATE AS UPDATE Products SET Price = Price + Price * 0.38 WHERE Id FROM inserted)
Допустим, в таблице Products хранятся данные о товарах. Но цена товара нередко содержит различные надбавки типа налога на добавленную стоимость, налога на добавленную коррупцию и так далее. Человек, добавляющий данные, может не знать все эти тонкости с налоговой базой, и он определяет чистую цену. С помощью триггера мы можем поправить цену товара на некоторую величину.
Таким образом, триггер будет срабатывать при любой операции INSERT или UPDATE над таблицей Products. Сам триггер будет изменять цену товара, а для получения того товара, который был добавлен или изменен, находим этот товар по Id. Но какое значение должен иметь Id такой товар? Дело в том, что при добавлении или изменении данные сохраняются в промежуточную таблицу inserted. Она создается автоматически. И из нее мы можем получить данные о добавленных/измененных товарах.
И после добавления товара в таблицу Products в реальности товар будет иметь несколько большую цену, чем та, которая была определена при добавлении:
Удаление триггера
Для удаления триггера необходимо применить команду DROP TRIGGER :
DROP TRIGGER Products_INSERT_UPDATE
Отключение триггера
Бывает, что мы хотим приостановить действие триггера, но удалять его полностью не хотим. В этом случае его можно временно отключить с помощью команды DISABLE TRIGGER :
DISABLE TRIGGER Products_INSERT_UPDATE ON Products
А когда триггер понадобится, его можно включить с помощью команды ENABLE TRIGGER :
ENABLE TRIGGER Products_INSERT_UPDATE ON Products
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.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
ВКЛЮЧЕНИЕ ТРИГГЕРА
При наличии отключенного триггера вам может понадобиться его включить. Вы можете сделать это с помощью команды ALTER TRIGGER.
Синтаксис
ALTER TRIGGER имя_триггера ENABLE;
Параметры и аргументы
имя_триггера наименование триггера, который вы хотите включить.
Примечание
- Смотрите также, как включить все триггеры таблицы.
- Смотрите также, как отключить триггер таблицы или отключить все триггеры таблицы.
Пример
Рассмотрим пример, который показывает, как включить триггер в Oracle.
ALTER TRIGGER orders_before_insert ENABLE;
Этот пример использует команду ALTER TRIGGER, для включения триггера orders_before_insert.