Триггеры
— это механизм, который вызывается, когда в указанной таблице происходит определенное действие. Каждый триггер имеет следующие основные составляющие: имя, действие и исполнение. Имя триггера может содержать максимум 128 символов. Действием триггера может быть или инструкция DML (INSERT, UPDATE или DELETE), или инструкция DDL. Таким образом, существует два типа триггеров: триггеры DML и триггеры DDL. Исполнительная составляющая триггера обычно состоит из хранимой процедуры или пакета.
Компонент Database Engine позволяет создавать триггеры, используя или язык Transact-SQL, или один из языков среды CLR, такой как C# или Visual Basic.
Создание триггера DML
Триггер создается с помощью инструкции CREATE TRIGGER, которая имеет следующий синтаксис:
Предшествующий синтаксис относится только к триггерам DML. Триггеры DDL имеют несколько иную форму синтаксиса, которая будет показана позже.
Параметры INSERT, UPDATE и DELETE задают действие триггера. Под действием триггера имеется в виду инструкция Transact-SQL, которая запускает триггер. Допускается любая комбинация этих трех инструкций. Инструкция DELETE не разрешается, если используется параметр IF UPDATE.
Как можно видеть в синтаксисе инструкции CREATE TRIGGER, действие (или действия) триггера указывается в спецификации AS sql_statement.
Компонент Database Engine позволяет создавать несколько триггеров для каждой таблицы и для каждого действия (INSERT, UPDATE и DELETE). По умолчанию определенного порядка исполнения нескольких триггеров для данного модифицирующего действия не имеется.
Только владелец базы данных, администраторы DDL и владелец таблицы, для которой определяется триггер, имеют право создавать триггеры для текущей базы данных. (В отличие от разрешений для других типов инструкции CREATE это разрешение не может передаваться.)
Изменение структуры триггера
Язык Transact-SQL также поддерживает инструкцию ALTER TRIGGER, которая модифицирует структуру триггера. Эта инструкция обычно применяется для изменения тела триггера. Все предложения и параметры инструкции ALTER TRIGGER имеют такое же значение, как и одноименные предложения и параметры инструкции CREATE TRIGGER.
Для удаления триггеров в текущей базе данных применяется инструкция DROP TRIGGER.
Использование виртуальных таблиц deleted и inserted
При создании действия триггера обычно требуется указать, ссылается ли он на значение столбца до или после его изменения действием, запускающим триггер. По этой причине, для тестирования следствия инструкции, запускающей триггер, используются две специально именованные виртуальные таблицы:
Структура этих таблиц эквивалентна структуре таблицы, для которой определен триггер.
Таблица deleted используется в том случае, если в инструкции CREATE TRIGGER указывается предложение DELETE или UPDATE, а если в этой инструкции указывается предложение INSERT или UPDATE, то используется таблица inserted. Это означает, что для каждой инструкции DELETE, выполненной в действии триггера, создается таблица deleted. Подобным образом для каждой инструкции INSERT, выполненной в действии триггера, создается таблица inserted.
Инструкция UPDATE рассматривается, как инструкция DELETE, за которой следует инструкция INSERT. Поэтому для каждой инструкции UPDATE, выполненной в действии триггера, создается как таблица deleted, так и таблица inserted (в указанной последовательности).
Таблицы inserted и deleted реализуются, используя управление версиями строк, которое рассматривалось в предыдущей статье. Когда для таблицы с соответствующими триггерами выполняется инструкция DML (INSERT, UPDATE или DELETE), для всех изменений в этой таблице всегда создаются версии строк. Когда триггеру требуется информация из таблицы deleted, он обращается к данным в хранилище версий строк. В случае таблицы inserted, триггер обращается к самым последним версиям строк.
В качестве хранилища версий строк механизм управления версиями строк использует системную базу данных tempdb. По этой причине, если база данных содержит большое число часто используемых триггеров, следует ожидать значительного увеличения объема этой системной базы данных.
Области применения DML-триггеров
Такие триггеры применяются для решения разнообразных задач. В этом разделе мы рассмотрим несколько областей применения триггеров DML, в частности триггеров AFTER и INSTEAD OF.
Триггеры AFTER
Как вы уже знаете, триггеры AFTER вызываются после того, как выполняется действие, запускающее триггер. Триггер AFTER задается с помощью ключевого слова AFTER или FOR. Триггеры AFTER можно создавать только для базовых таблиц. Триггеры этого типа можно использовать для выполнения, среди прочих, следующих операций:
создания журнала логов действий в таблицах базы данных;
принудительного обеспечения ссылочной целостности.
Создание журнала логов
В SQL Server можно выполнять отслеживание изменения данных, используя систему перехвата изменения данных CDC (change data capture). Эту задачу можно также решить с помощью триггеров DML. В примере ниже показывается, как с помощью триггеров можно создать журнал логов действий в таблицах базы данных:
В этом примере создается таблица AuditBudget, в которой сохраняются все изменения столбца Budget таблицы Project. Изменения этого столбца будут записываться в эту таблицу посредством триггера trigger_ModifyBudget.
Этот триггер активируется для каждого изменения столбца Budget с помощью инструкции UPDATE. При выполнении этого триггера значения строк таблиц deleted и inserted присваиваются соответствующим переменным @budgetOld, @budgetNew и @projectNumber. Эти присвоенные значения, совместно с именем пользователя и текущей датой, будут затем вставлены в таблицу AuditBudget.
В этом примере предполагается, что за один раз будет обновление только одной строки. Поэтому этот пример является упрощением общего случая, когда триггер обрабатывает многострочные обновления. Если выполнить следующие инструкции Transact-SQL:
то содержимое таблицы AuditBudget будет таким:
Реализация бизнес-правил
С помощью триггеров можно создавать бизнес-правила для приложений. Создание такого триггера показано в примере ниже:
Здесь создается правило для управления модификацией бюджетов проектов. Триггер trigger_TotalBudget проверяет каждое изменение бюджетов и выполняет только такие инструкции UPDATE, которые увеличивают сумму всех бюджетов не более чем на 50%. В противном случае для инструкции UPDATE выполняется откат посредством инструкции ROLLBACK TRANSACTION.
Принудительное обеспечение ограничений целостности
В системах управления базами данных применяются два типа ограничений для обеспечения целостности данных: декларативные ограничения, которые определяются с помощью инструкций языка CREATE TABLE и ALTER TABLE; процедурные ограничения целостности, которые реализуются посредством триггеров.
В обычных ситуациях следует использовать декларативные ограничения для обеспечения целостности, поскольку они поддерживаются системой и не требуют реализации пользователем. Применение триггеров рекомендуется только в тех случаях, для которых декларативные ограничения для обеспечения целостности отсутствуют.
В примере ниже показано принудительное обеспечение ссылочной целостности посредством триггеров для таблиц Employee и Works_on:
Триггер trigger_WorksonIntegrity в этом примере проверяет ссылочную целостность для таблиц Employee и Works_on. Это означает, что проверяется каждое изменение столбца Id в ссылочной таблице Works_on, и при любом нарушении этого ограничения выполнение этой операции не допускается. (То же самое относится и к вставке в столбец Id новых значений.) Инструкция ROLLBACK TRANSACTION во втором блоке BEGIN выполняет откат инструкции INSERT или UPDATE в случае нарушения ограничения для обеспечения ссылочной целостности.
Триггеры INSTEAD OF
Триггер с предложением INSTEAD OF заменяет соответствующее действие, которое запустило его. Этот триггер выполняется после создания соответствующих таблиц inserted и deleted, но перед выполнением проверки ограничений целостности или каких-либо других действий.
Триггеры INSTEAD OF можно создавать как для таблиц, так и для представлений. Когда инструкция Transact-SQL ссылается на представление, для которого определен триггер INSTEAD OF, система баз данных выполняет этот триггер вместо выполнения любых действий с любой таблицей. Данный тип триггера всегда использует информацию в таблицах inserted и deleted, созданных для представления, чтобы создать любые инструкции, требуемые для создания запрошенного события.
Значения столбцов, предоставляемые триггером INSTEAD OF, должны удовлетворять определенным требованиям:
значения не могут задаваться для вычисляемых столбцов;
значения не могут задаваться для столбцов с типом данных timestamp;
значения не могут задаваться для столбцов со свойством IDENTITY, если только параметру IDENTITY_INSERT не присвоено значение ON.
Эти требования действительны только для инструкций INSERT и UPDATE, которые ссылаются на базовые таблицы. Инструкция INSERT, которая ссылается на представления с триггером INSTEAD OF, должна предоставлять значения для всех столбцов этого представления, не допускающих пустые значения NULL. (То же самое относится и к инструкции UPDATE. Инструкция UPDATE, ссылающаяся на представление с триггером INSTEAD OF, должна предоставить значения для всех столбцов представления, которое не допускает пустых значений и на которое осуществляется ссылка в предложении SET.)
В примере ниже показана разница в поведении при вставке значений в вычисляемые столбцы, используя таблицу и ее соответствующее представление:
В этом примере используется таблица Orders, содержащая два вычисляемых столбца. Представление view_AllOrders содержит все строки этой таблицы. Это представление используется для задания значения в его столбце, которое соотносится с вычисляемым столбцом в базовой таблице, на которой создано представление. Это позволяет использовать триггер INSTEAD OF, который в случае инструкции INSERT заменяется пакетом, который вставляет значения в базовую таблицу посредством представления view_AllOrders. (Инструкция INSERT, обращающаяся непосредственно к базовой таблице, не может задавать значение вычисляемому столбцу.)
Триггеры first и last
Компонент Database Engine позволяет создавать несколько триггеров для каждой таблицы или представления и для каждой операции (INSERT, UPDATE и DELETE) с ними. Кроме этого, можно указать порядок выполнения для нескольких триггеров, определенных для конкретной операции. С помощью системной процедуры sp_settriggerorder можно указать, что один из определенных для таблицы триггеров AFTER будет выполняться первым или последним для каждого обрабатываемого действия. Эта системная процедура имеет параметр @order, которому можно присвоить одно из трех значений:
Изменение структуры триггера посредством инструкции ALTER TRIGGER отменяет порядок выполнения триггера (первый или последний). Применение системной процедуры sp_settriggerorder показано в примере ниже:
Для таблицы разрешается определить только один первый и только один последний триггер AFTER. Остальные триггеры AFTER выполняются в неопределенном порядке. Узнать порядок выполнения триггера можно с помощью системной процедуры sp_helptrigger или функции OBJECTPROPERTY.
Возвращаемый системной процедурой sp_helptrigger результирующий набор содержит столбец order, в котором указывается порядок выполнения указанного триггера. При вызове функции objectproperty в ее втором параметре указывается значение ExeclsFirstTrigger или ExeclsLastTrigger, а в первом параметре всегда указывается идентификационный номер объекта базы данных. Если указанное во втором параметре свойство имеет значение true, функция возвращает значение 1.
Поскольку триггер INSTEAD OF исполняется перед тем, как выполняются изменения в его таблице, для триггеров этого типа нельзя указать порядок выполнения «первым» или «последним».
Триггеры DDL и области их применения
Ранее мы рассмотрели триггеры DML, которые задают действие, предпринимаемое сервером при изменении таблицы инструкциями INSERT, UPDATE или DELETE. Компонент Database Engine также позволяет определять триггеры для инструкций DDL, таких как CREATE DATABASE, DROP TABLE и ALTER TABLE. Триггеры для инструкций DDL имеют следующий синтаксис:
Как можно видеть по их синтаксису, триггеры DDL создаются таким же способом, как и триггеры DML. А для изменения и удаления этих триггеров используются те же инструкции ALTER TRIGGER и DROP TRIGGER, что и для триггеров DML. Поэтому в этом разделе рассматриваются только те параметры инструкции CREATE TRIGGER, которые новые для синтаксиса триггеров DDL.
После указания области действия триггера DDL нужно в ответ на выполнение одной или нескольких инструкций DDL указать способ запуска триггера. В параметре event_type указывается инструкция DDL, выполнение которой запускает триггер, а в альтернативном параметре event_group указывается группа событий языка Transact-SQL. Триггер DDL запускается после выполнения любого события языка Transact-SQL, указанного в параметре event_group. Ключевое слово LOGON указывает триггер входа.
Кроме сходства триггеров DML и DDL, между ними также есть несколько различий. Основным различием между этими двумя видами триггеров является то, что для триггера DDL можно задать в качестве его области действия всю базу данных или даже весь сервер, а не всего лишь отдельный объект. Кроме этого, триггеры DDL не поддерживают триггеров INSTEAD OF. Как вы, возможно, уже догадались, для триггеров DDL не требуются таблицы inserted и deleted, поскольку эти триггеры не изменяют содержимого таблиц.
В следующих подразделах подробно рассматриваются две формы триггеров DDL: триггеры уровня базы данных и триггеры уровня сервера.
Триггеры DDL уровня базы данных
В примере ниже показано, как можно реализовать триггер DDL, чья область действия распространяется на текущую базу данных:
Триггер в этом примере предотвращает удаление любого триггера для базы данных SampleDb любым пользователем. Предложение DATABASE указывает, что триггер trigger_PreventDrop является триггером уровня базы данных. Ключевое слово DROP_TRIGGER указывает предопределенный тип события, запрещающий удаление любого триггера.
Триггеры DDL уровня сервера
В примере ниже демонстрируется создание триггера уровня сервера, который является триггером входа:
Здесь сначала создается имя входа SQL Server loginTest, которое потом используется в триггере уровня сервера. По этой причине, для этого имени входа требуется разрешение VIEW SERVER STATE, которое и предоставляется ему посредством инструкции GRANT. После этого создается триггер trigger_ConnectionLimit. Этот триггер является триггером входа, что указывается ключевым словом LOGON.
С помощью представления sys.dm_exec_sessions выполняется проверка, был ли уже установлен сеанс с использованием имени входа loginTest. Если сеанс уже был установлен, выполняется инструкция ROLLBACK. Таким образом имя входа loginTest может одновременно установить только один сеанс.
Триггеры и среда CLR
Создается исходный код триггера на языке C# или Visual Basic, который затем компилируется, используя соответствующий компилятор в объектный код.
Объектный код обрабатывается инструкцией CREATE ASSEMBLY, создавая соответствующий выполняемый файл.
Посредством инструкции CREATE TRIGGER создается триггер.
Выполнение всех этих трех этапов создания триггера CLR демонстрируется в последующих примерах. Ниже приводится пример исходного кода программы на языке C# для триггера из первого примера в статье. Прежде чем создавать триггер CLR в последующих примерах, сначала нужно удалить триггер trigger_PreventDrop, а затем удалить триггер trigger_ModifyBudget, используя в обоих случаях инструкцию DROP TRIGGER.
Пространство имен Microsoft.SQLServer.Server содержит все классы клиентов, которые могут потребоваться программе C#. Классы SqlTriggerContext и SqlFunction являются членами этого пространства имен. Кроме этого, пространство имен System.Data.SqlClient содержит классы SqlConnection и SqlCommand, которые используются для установления соединения и взаимодействия между клиентом и сервером базы данных. Соединение устанавливается, используя строку соединения «context connection = true».
Затем определяется класс Triggers, который применяется для реализации триггеров. Метод ModifyBudget() реализует одноименный триггер. Экземпляр context класса SqlTriggerContext позволяет программе получить доступ к виртуальной таблице, создаваемой при выполнении триггера. В этой таблице сохраняются данные, вызвавшие срабатывание триггера. Метод IsUpdatedColumn() класса SqlTriggerContext позволяет узнать, был ли модифицирован указанный столбец таблицы.
Данная программа содержит два других важных класса: SqlConnection и SqlCommand. Экземпляр класса SqlConnection обычно применяется для установления соединения с базой данных, а экземпляр класса SqlCommand позволяет исполнять SQL-инструкции.
Программу из этого примера можно скомпилировать с помощью компилятора csc, который встроен в Visual Studio. Следующий шаг состоит в добавлении ссылки на скомпилированную сборку в базе данных:
Инструкция CREATE ASSEMBLY принимает в качестве ввода управляемый код и создает соответствующий объект, на основе которого создается триггер CLR. Предложение WITH PERMISSION_SET в примере указывает, что разрешениям доступа присвоено значение SAFE.
Наконец, в примере ниже посредством инструкции CREATE TRIGGER создается триггер trigger_modify_budget:
SPBDEV Blog
Вступление
Триггеры могут быть определены как объекты базы данных, которые выполняют некоторые действия для автоматического выполнения всякий раз, когда пользователи пытаются выполнить команды изменения данных (INSERT, DELETE и UPDATE) для указанных таблиц. Триггеры привязаны к конкретным таблицам. Согласно MSDN, триггеры могут быть определены как особый вид хранимых процедур. Эта статья даст вам подробные знания о триггерах SQL, которые могут быть очень полезны в вашей работе. Прежде чем описывать типы триггеров, мы должны сначала понять магические таблицы, на которые ссылаются триггеры и которые используются для повторного использования.
Волшебные таблицы
В SQL Server вставлены и удалены две таблицы, которые в народе называются Magic tables. Это не физические таблицы, а внутренние таблицы SQL Server, обычно используемые с триггерами для извлечения вставленных, удаленных или обновленных строк. Эти таблицы содержат информацию о вставленных строках, удаленных строках и обновленных строках. Эта информация может быть обобщена следующим образом:
Таблица содержит все вставленные строки
Таблица не содержит строк
Таблица не содержит строк
Таблица содержит все удаленные строки
Таблица содержит строки после обновления
Таблица содержит все строки до обновления
Разница между хранимой процедурой и триггером
Триггеры DML
Типы триггера
В SQL Server есть два типа триггеров, которые приведены ниже:
В этой статье мы будем использовать три таблицы с именами 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.
Пример триггера AFTER для обновления
Предположим, у нас также есть требование, чтобы всякий раз, когда клиент зачислял средства на свою учетную запись или обновлял свое имя (имя и фамилию), клиенту должно быть отправлено письмо, содержащее эту информацию. В этом случае мы можем использовать триггер After для обновления. В этом примере мы будем использовать Магическую таблицу Inserted.
В приведенном выше примере мы использовали функцию Update для количества столбцов, custfname и custEname, которая запускает триггер обновления при изменении этих столбцов.
Триггеры INSTEAD OF
Триггер INSTEAD OF используется, когда мы хотим выполнить другое действие вместо действия, которое вызывает срабатывание триггера. Триггеры INSTEAD OF
могут быть определены в случае вставки, удаления и обновления. Например, предположим, что у нас есть условие, что в одной транзакции пользователь не сможет дебетовать более 15000 долларов. Мы можем использовать триггер вместо, чтобы реализовать это ограничение. Если пользователь пытается снять со своего счета более 15000 долларов за один раз, появляется сообщение об ошибке « Cannot Withdraw more than 15000 at a time ». В этом примере мы используем волшебную таблицу Inserted.
Триггеры DDL
У триггеров DDL такое же поведение, как и у триггеров DML, за исключением того, что они запускаются в ответ на событие типа DDL, такое как команда Alter, команда Drop и команды Create. Другими словами, он будет срабатывать в ответ на события, которые пытаются изменить схему базы данных. Поэтому эти триггеры не создаются для конкретной таблицы, но они применимы ко всем таблицам в базе данных. Также триггеры DDL могут быть запущены только после выполнения команд, которые их запускают. Они могут быть использованы для следующих целей:
1) Чтобы предотвратить любые изменения в схеме базы данных
2) Если мы хотим хранить записи всех событий, которые меняют схему базы данных.
Например, предположим, что мы хотим создать таблицу command_log, в которой будут храниться все пользовательские команды для создания таблиц (Create table) и команды, которые изменяют таблицы. Также мы не хотим, чтобы какая-либо таблица был удалена. Поэтому, если какая-либо команда удаления таблицы запущена, триггер DDL откатит команду с сообщением «Вы не можете удалить таблицу».
Скрипт для таблицы command_log будет приведен ниже:
DDL Trigger для создания таблицы
Для сохранения команды create table в таблице command_log нам сначала нужно создать триггер, который будет запущен в ответ на выполнение команды Create table.
Этот триггер срабатывает всякий раз, когда запускается любая команда для создания таблицы, и вставляет команду в таблицу command_log, а также выводит сообщение «Таблица была успешно создана».
DDL Trigger для изменения таблицы
Предположим, что если мы хотим сохранить команды alter table также в таблице command_log, нам нужно создать триггер для команды Alter_table.
Этот триггер срабатывает всякий раз, когда в базе данных запускается любая команда alter table, и выводит сообщение «Таблица успешно изменена».
DDL Trigger для удаления таблицы
Этот триггер не позволит удалить любую таблицу, а также выведет сообщение «Таблица не может быть удалена».
Вложенные триггеры
Например, предположим, что существует триггер t1, определенный в таблице tbl1, и есть другой триггер t2, определенный в таблице tbl2, если действие триггера t1 инициирует триггер t2, то оба триггера называются вложенными. В SQL Server триггеры могут быть вложены до 32 уровней. Если действие вложенных триггеров приводит к бесконечному циклу, то после 32 уровня триггер завершается.
Поскольку триггеры выполняются внутри транзакции, то сбой на любом уровне внутри вложенных триггеров может отменить всю транзакцию и привести к общему откату.
Мы также можем остановить выполнение вложенных триггеров с помощью следующей команды SQL:
Рекурсивные триггеры
В SQL Server у нас могут быть рекурсивные триггеры, где действие триггера может инициироваться снова. В SQL Server у нас есть два типа рекурсии:
В прямой рекурсии действие триггера снова инициирует сам триггер, что приводит к рекурсивному вызову триггера.
В косвенной рекурсии действие над триггером инициирует другой триггер, и выполнение этого триггера снова вызывает исходный триггер, и это происходит рекурсивно. Оба триггера могут быть в одной и той же таблице или созданы в разных таблицах.
Обратите внимание: рекурсивный триггер возможен только при установленной опции рекурсивного триггера.
Опцию рекурсивного запуска можно установить с помощью следующей команды 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’
Результат:
Как отключить триггер
Отключение триггера 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
Пример из реальной жизни
Несколько недель назад один разработчик получил задание, которое нужно выполнить на очень старом написанном коде. Задача включает в себя условие, что письмо должно быть отправлено пользователю в следующих случаях:
Проблемы в этой задаче включают в себя:
Возможные решения:
Два подхода для вставки строк:
Мы использовали второй подход по следующим 4 причинам:
2) Существует риск того, что новый разработчик может не знать об этом требовании отправки почты и забыть добавить код для вставки значений в таблицу tblmail.
3) Если нам нужно что-то изменить в требовании, оно должно быть изменено во всех этих файлах и хранимых процедурах.
4) При втором подходе нам нужно только создать триггеры для таблицы и разработчика, и это также сведет к минимуму упоминание риска в трех упомянутых выше трех пунктах.
Преимущества триггеров SQL
1) Они помогают поддерживать ограничения целостности в таблицах базы данных, особенно когда не определены ограничения первичного ключа и внешнего ключа.
2) Иногда они помогают сохранить короткие и простые коды SQL, как показано на примере из реальной жизни.
3) Помогают поддерживать отслеживание всех изменений (обновление, удаление и вставка), происходящих в таблицах, путем вставки значений изменений в таблицы аудита.
4) Иногда, если код плохо управляется, он может помочь в поддержании ограничений базы данных, определенных для таблиц, для которых определен триггер. Например, предположим, что есть ситуация, когда существует онлайновая система обучения, в которой пользователь может зарегистрироваться в нескольких курсах.
Предположим, что организация хочет определить ограничение: определено, что пользователь не может быть удален до тех пор, пока он или она не пройдет весь курс, в котором он зарегистрирован, или пользователь не должен сначала самостоятельно пройти из всех незавершенных или неудачных курсов.
Недостатки триггеров
1) Их трудно поддерживать, поскольку есть вероятность того, что новый разработчик не сможет узнать о триггере, определенном в базе данных, и задаться вопросом, как данные вставляются, удаляются или обновляются автоматически.
2) Их трудно отлаживать, так как их трудно просматривать по сравнению с хранимыми процедурами, представлениями, функциями и т. д.
3) Чрезмерное использование триггеров может замедлить производительность приложения, поскольку, если мы определили триггеры во многих таблицах, они будут автоматически выполняться каждый раз, когда данные вставляются, удаляются или обновляются в таблицах (на основе определения триггера), и это делает обработку очень медленной.
4) Если в триггерах написан сложный код, это замедлит работу приложений.
5) Стоимость создания триггеров может быть больше для таблиц, в которых высока частота операций DML (вставка, удаление и обновление), таких как массовая вставка.