Вступление

Триггеры могут быть определены как объекты базы данных, которые выполняют некоторые действия для автоматического выполнения всякий раз, когда пользователи пытаются выполнить команды изменения данных (INSERT, DELETE и UPDATE) для указанных таблиц. Триггеры привязаны к конкретным таблицам. Согласно MSDN, триггеры могут быть определены как особый вид хранимых процедур. Эта статьядаст вам подробные знания о триггерах SQL, которые могут быть очень полезны в вашей работе. Прежде чем описывать типы триггеров, мы должны сначала понять магические таблицы, на которые ссылаются триггеры и которые используются для повторного использования.

Волшебные таблицы

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

Разница между хранимой процедурой и триггером

1. Мы можем выполнить хранимую процедуру всякий раз, когда захотим, с помощью команды exec, но триггер можно выполнить только всякий раз, когда событие (вставка, удаление и обновление) запускается в таблице, для которой определен триггер.
2. Мы можем вызвать хранимую процедуру из другой хранимой процедуры, но мы не можем напрямую вызвать другой триггер внутри триггера. Мы можем достичь только вложенности триггеров, при которой действие (вставка, удаление и обновление), определенное внутри триггера, может инициировать выполнение другого триггера, определенного в той же или другой таблице.
3. Хранимые процедуры могут быть запланированы через задание для выполнения в заранее определенное время, но мы не можем запланировать триггер.
4. Хранимая процедура может принимать входные параметры, но мы не можем передать параметры в качестве входных данных для триггера.
5. Хранимые процедуры могут возвращать значения, но триггер не может возвращать значение.
6. Мы можем использовать команды Print внутри хранимой процедуры для отладки, но мы не можем использовать команду print внутри триггера.
7. Мы можем использовать операторы транзакции, такие как начало транзакции, фиксация транзакции и откат внутри хранимой процедуры, но мы не можем использовать операторы транзакции внутри триггера.
8. Мы можем вызвать хранимую процедуру из внешнего интерфейса (.asp-файлы, .aspx-файлы, .ascx-файлы и т. Д.), Но мы не можем вызвать триггер из этих файлов.

Триггеры DML

Типы триггера

В SQL Server есть два типа триггеров, которые приведены ниже:

1. Триггеры AFTER
2. Триггеры INSTEAD OF

В этой статье мы будем использовать три таблицы с именами customer, customerTransaction и Custmail, структура которых приведена ниже:

Create table customer (customerid int identity (1, 1) primary key,Custnumber nvarchar(100), custFname nvarchar(100), CustEnamn nvarchar(100), email nvarchar(100), Amount int, regdate datetime)
Create table customerTransaction(Transactionid int identity(1,1)primary key,custid int,Transactionamt int, mode nvarchar, trandate datetime)
Create table Custmail (Custmailid int identity (1, 1) primary key, custid int, Amt int, Mailreason nvarchar(1000))

Триггеры AFTER

Триггеры AFTER  выполняются после выполнения действия модификации данных (INSERT, UPDATE или DELETE) для соответствующих таблиц. Таблица может иметь несколько триггеров, определенных на ней.

Синтаксис триггера  AFTER  

Create Trigger trigger_name
On Table name
For Insert/Delete/update
As
Begin
//SQL Statements
End

Пример триггера AFTER   для вставки

Предположим, у нас есть требование, что всякий раз, когда добавляется новый клиент, автоматически его соответствующее значение должно быть вставлено в таблицу Custmail, чтобы можно было отправить электронное письмо клиенту и уполномоченному лицу в Банке. Чтобы решить эту проблему, мы можем создать триггер After Insert для таблицы customer, синтаксис которой приведен ниже:

Create Trigger trig_custadd on Customer
For Insert
As
Begin
Declare @Custnumber as nvarchar(100)
Declare @amount as int
Declare @custid as int
Select @Custnumber=Custnumber, @amount=Amount From inserted
Select @custid=customerid From customer Where Custnumber =@Custnumber
Insert Into Custmail (custid,Amt,Mailreason)
Values (@custid,@amount,'New Customer')
End

Этот триггер сработает всякий раз, когда новый клиент добавляется в банк и соответствующая запись вставляется в таблицу Custmail. Функциональность почты будет использовать записи из таблицы custmail для отправки почты клиенту.

Пример триггера AFTER  для удаления

Предположим, есть еще одно требование, что всякий раз, когда клиент удаляется из системы, ему отправляется почта, содержащая уведомление об удалении. Для отправки почты нам необходимо вставить запись клиента в таблицу custmail всякий раз, когда клиент удаляется из главной таблицы клиентов. Для этого мы будем использовать триггер AFTER для удаления. В приведенном ниже примере мы будем использовать волшебную таблицу Deleted.

Create trigger trig_custdelete on customer
For Delete
As begin
Declare @Custnumber as nvarchar(100)
Declare @custid as int
Select @Custnumber=Custnumber from deleted
Select @custid=customerid from customer where Custnumber =@Custnumber
Delete from customerTransaction where custid=@custid
Insert into Custmail
Values(@custid,0,'Customer delete')
end

Пример триггера AFTER  для обновления

Предположим, у нас также есть требование, чтобы всякий раз, когда клиент зачислял средства на свою учетную запись или обновлял свое имя (имя и фамилию), клиенту должно быть отправлено письмо, содержащее эту информацию. В этом случае мы можем использовать триггер After для обновления. В этом примере мы будем использовать Магическую таблицу Inserted.

create trigger trig_Custupdate
on customer
for update
as
begin
 declare @Custnumber as nvarchar(100)
 declare @amount as int
 Declare @custid as int
 if update(amount)
   begin
       select @Custnumber=Custnumber, @amount=Amount from inserted
       select @custid=customerid from customer where Custnumber =@Custnumber
       insert into Custmail
       values(@custid,@amount,'Customer Amount Update')
   end
 if update(custFname)or update(CustEnamn)
   begin
  insert into Custmail
  values(@custid,0,'Customer Name Update')
   end
end

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

Триггеры  INSTEAD OF

Триггер  INSTEAD OF используется, когда мы хотим выполнить другое действие вместо действия, которое вызывает срабатывание триггера. Триггеры  INSTEAD OF

могут быть определены в случае вставки, удаления и обновления. Например, предположим, что у нас есть условие, что в одной транзакции пользователь не сможет дебетовать более 15000 долларов. Мы можем использовать триггер вместо, чтобы реализовать это ограничение. Если пользователь пытается снять со своего счета более 15000 долларов за один раз, появляется сообщение об ошибке «Cannot Withdraw more than 15000 at a time». В этом примере мы используем волшебную таблицу Inserted.

Create trigger trigg_insteadofdelete
on customerTransaction
instead of insert
as
begin
declare @Custnumber as nvarchar(100)
declare @amount as int
Declare @custid as int
Declare @mode as nvarchar(10)
select @custid=custid , @amount=Transactionamt,@mode=mode from
inserted
if @mode='c'
begin
update customer set amount=amount+@amount where
customerid=@custid
insert into Custmail
values(@custid,@amount,'Customer Amount Update')
end
if @mode='d'
begin
if @amount<=15000
begin
update customer set amount=amount-@amount where
customerid=@custid
insert into Custmail
values(@custid,@amount,'Customer Amount Update')
end
else
begin
Raiserror ('Cannot Withdraw more than 15000 at a time',16,1)
rollback;
end
end
end

Триггеры DDL

У триггеров DDL такое же поведение, как и у триггеров DML, за исключением того, что они запускаются в ответ на событие типа DDL, такое как команда Alter, команда Drop и команды Create. Другими словами, он будет срабатывать в ответ на события, которые пытаются изменить схему базы данных. Поэтому эти триггеры не создаются для конкретной таблицы, но они применимы ко всем таблицам в базе данных. Также триггеры DDL могут быть запущены только после выполнения команд, которые их запускают. Они могут быть использованы для следующих целей:

1) Чтобы предотвратить любые изменения в схеме базы данных

2) Если мы хотим хранить записи всех событий, которые меняют схему базы данных.

Например, предположим, что мы хотим создать таблицу command_log, в которой будут храниться все пользовательские команды для создания таблиц (Create table) и команды, которые изменяют таблицы. Также мы не хотим, чтобы какая-либо таблица был удалена. Поэтому, если какая-либо команда удаления таблицы запущена, триггер DDL откатит команду с сообщением «Вы не можете удалить таблицу».

Скрипт для таблицы command_log будет приведен ниже:

CREATE TABLE Command_log(id INT identity(1,1), Commandtext NVARCHAR(1000), Commandpurpose nvarchar(50))

DDL Trigger для создания таблицы

Для сохранения команды create table в таблице command_log нам сначала нужно создать триггер, который будет запущен в ответ на выполнение команды Create table.

CREATE TRIGGER DDL_Createtable
ON database
FOR CREATE_Table
AS
Begin
PRINT 'Table has been successfully created.'
insert into command_log ()
Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText ) [1] ','nvarchar(1000)')

End

Этот триггер срабатывает всякий раз, когда запускается любая команда для создания таблицы, и вставляет команду в таблицу command_log, а также выводит сообщение «Таблица была успешно создана».

Примечание. Eventdata () - это функция, которая возвращает информацию о событиях сервера или базы данных. Возвращает значение типа XML.

DDL Trigger для изменения таблицы

Предположим, что если мы хотим сохранить команды alter table также в таблице command_log, нам нужно создать триггер для команды Alter_table.

Create Trigger DDL_Altertable
On Database
for Alter_table
as
begin
declare @coomand as nvarchar(max)
print 'Table has been altered successfully'
insert into command_log(commandtext)
Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]','nvarchar(1000)')

end

Этот триггер срабатывает всякий раз, когда в базе данных запускается любая команда alter table, и выводит сообщение «Таблица успешно изменена».

DDL Trigger для удаления таблицы

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

Create TRIGGER DDL_DropTable
ON database
FOR Drop_table
AS
Begin
PRINT 'Table cannot be dropped.'
INSERT into command_log(commandtext)
Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]','nvarchar(1000)')
Rollback;
end

Этот триггер не позволит удалить любую таблицу, а также выведет сообщение «Таблица не может быть удалена».

Вложенные триггеры

Вложенный триггер: - В Sql Server триггеры называются вложенными, когда действие одного триггера инициирует другой триггер, который может находиться в той же или другой таблице.

Например, предположим, что существует триггер t1, определенный в таблице tbl1, и есть другой триггер t2, определенный в таблице tbl2, если действие триггера t1 инициирует триггер t2, то оба триггера называются вложенными. В SQL Server триггеры могут быть вложены до 32 уровней. Если действие вложенных триггеров приводит к бесконечному циклу, то после 32 уровня триггер завершается.

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

Мы также можем остановить выполнение вложенных триггеров с помощью следующей команды SQL:

sp_CONFIGURE 'nested_triggers',0<br style="padding: 0px; margin: 0px;" />GO
RECONFIGURE
GO

Рекурсивные триггеры

В SQL Server у нас могут быть рекурсивные триггеры, где действие триггера может инициироваться снова. В SQL Server у нас есть два типа рекурсии:

1.  Прямая рекурсия
2.  Непрямая рекурсия

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

В косвенной рекурсии действие над триггером инициирует другой триггер, и выполнение этого триггера снова вызывает исходный триггер, и это происходит рекурсивно. Оба триггера могут быть в одной и той же таблице или созданы в разных таблицах.

Обратите внимание: рекурсивный триггер возможен только при установленной опции рекурсивного триггера.

Опцию рекурсивного запуска можно установить с помощью следующей команды SQL:

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF

Как найти триггеры в базе данных

1. Нахождение всех триггеров, определенных для всей базы данных

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

select o1.name, o2.name from sys.objects o1 inner join sys.objects o2 on  o1.parent_object_id=o2.object_id and o1.type_desc='sql_trigger'

2. Нахождение всех триггеров, определенных в конкретной таблице

Например, если мы хотим выяснить все триггеры, созданные в таблице Customer, мы можем использовать следующую инструкцию SQL:

sp_helptrigger Tablename
example:-
sp_helptrigger 'Customer'

3. Нахождение определения триггера

Предположим, что если мы хотим узнать определение триггера, мы можем использовать следующую инструкцию SQL:

sp_helptext triggername
For example:-
sp_helptext 'trig_custadd'

Результат:

Как отключить триггер

DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]

Отключение триггера DML для таблицы

DISABLE TRIGGER 'trig_custadd' ON Customer;

Отключение триггера DDL

DISABLE TRIGGER 'DDL_Createtable' ON DATABASE;

Отключение всех триггеров, которые были определены с одинаковой областью действия

DISABLE Trigger ALL ON ALL SERVER;

Как включить триггер

Включение триггера DML для таблицы

ENABLE Trigger 'trig_custadd'  ON Customer;

Включение триггера DDL

ENABLE TRIGGER 'DDL_Createtable' ON DATABASE;

Включение всех триггеров, которые были определены с одинаковой областью действия

ENABLE Trigger ALL ON ALL SERVER;

Как сбросить триггер

Сбрасывание триггера DML:

DROP TRIGGER trig_custadd ;

Сбрасывание триггера DDL:

DROP TRIGGER DDL_Createtable ON DATABASE

Пример из реальной жизни

Несколько недель назад один разработчик получил задание, которое нужно выполнить на очень старом написанном коде. Задача включает в себя условие, что письмо должно быть отправлено пользователю в следующих случаях:

1. Пользователь добавлен в систему.
2. Всякая информация, касающаяся пользователя, обновляется, удаляется или добавляется.
3. Пользователь удален.

Проблемы в этой задаче включают в себя:

1. Код очень старый и неструктурированный. Поэтому он имеет много встроенных запросов, написанных на различных страницах .aspx.
2. Запросы на вставку, удаление и обновление также записаны во многих хранимых процедурах.

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

Возможные решения:

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

Два подхода для вставки строк:

1. Найдите все места в файлах .aspx и хранимых процедурах, где есть запросы на вставку, удаление и обновление, и после этих запросов добавляют запрос на вставку для таблицы tblmail.
2. Вместо того, чтобы искать эти запросы во всех файлах и хранимых процедурах .axps, создайте триггер after (вставка, обновление и удаление) в основной таблице пользователя, который вставит дату в таблицу tblmail после выполнения оператора вставки, обновления и удаления.

Мы использовали второй подход по следующим 4 причинам:

1) Очень сложно найти столько файлов .aspx и хранимых процедур, чтобы найти требуемые запросы.

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

3) Если нам нужно что-то изменить в требовании, оно должно быть изменено во всех этих файлах и хранимых процедурах.

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

Преимущества триггеров SQL

1) Они помогают поддерживать ограничения целостности в таблицах базы данных, особенно когда не определены ограничения первичного ключа и внешнего ключа.

2) Иногда они помогают сохранить короткие и простые коды SQL, как показано на примере из реальной жизни.

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

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

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

Поскольку код плохо управляется, а код для удаления пользователя определяется как встроенный запрос на многих страницах .net и в нескольких хранимых процедурах (это не очень хорошая вещь, но бывает), нужно написать код для применение этого ограничения ко всем этим .net-файлам и хранимым процедурам, которые занимают так много времени, и если новый разработчик не выполняет это ограничение, он забывает включить код принудительного применения, который повреждает базу данных. В этом случае мы можем определить триггер вместо таблицы, который проверяет каждый раз, когда пользователь удаляется, и, если условие вышеуказанного ограничения не выполняется, вместо удаления пользователя отображается сообщение об ошибке.

Недостатки триггеров

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

2) Их трудно отлаживать, так как их трудно просматривать по сравнению с хранимыми процедурами, представлениями, функциями и т. д.

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

4) Если в триггерах написан сложный код, это замедлит работу приложений.

5) Стоимость создания триггеров может быть больше для таблиц, в которых высока частота операций DML (вставка, удаление и обновление), таких как массовая вставка.

Резюме

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

No Comments