Что такое триггеры в sql

Триггеры

— это механизм, который вызывается, когда в указанной таблице происходит определенное действие. Каждый триггер имеет следующие основные составляющие: имя, действие и исполнение. Имя триггера может содержать максимум 128 символов. Действием триггера может быть или инструкция DML (INSERT, UPDATE или DELETE), или инструкция DDL. Таким образом, существует два типа триггеров: триггеры DML и триггеры DDL. Исполнительная составляющая триггера обычно состоит из хранимой процедуры или пакета.

Компонент Database Engine позволяет создавать триггеры, используя или язык Transact-SQL, или один из языков среды CLR, такой как C# или Visual Basic.

Создание триггера DML

Триггер создается с помощью инструкции CREATE TRIGGER, которая имеет следующий синтаксис:

Предшествующий синтаксис относится только к триггерам DML. Триггеры DDL имеют несколько иную форму синтаксиса, которая будет показана позже.

Здесь в параметре schema_name указывается имя схемы, к которой принадлежит триггер, а в параметре trigger_name — имя триггера. В параметре table_name задается имя таблицы, для которой создается триггер. (Также поддерживаются триггеры для представлений, на что указывает наличие параметра view_name.)

Также можно задать тип триггера с помощью двух дополнительных параметров: AFTER и INSTEAD OF. (Параметр FOR является синонимом параметра AFTER.) Триггеры типа AFTER вызываются после выполнения действия, запускающего триггер, а триггеры типа INSTEAD OF выполняются вместо действия, запускающего триггер. Триггеры AFTER можно создавать только для таблиц, а триггеры INSTEAD OF — как для таблиц, так и для представлений.

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

USE SampleDb; /* Таблица AuditBudget используется в качестве журнала логов действий в таблице Project */ GO CREATE TABLE AuditBudget ( ProjectNumber CHAR(4) NULL, UserName CHAR(16) NULL, Date DATETIME NULL, BudgetOld FLOAT NULL, BudgetNew FLOAT NULL ); GO CREATE TRIGGER trigger_ModifyBudget ON Project AFTER UPDATE AS IF UPDATE(budget) BEGIN DECLARE @budgetOld FLOAT DECLARE @budgetNew FLOAT DECLARE @projectNumber CHAR(4) SELECT @budgetOld = (SELECT Budget FROM deleted) SELECT @budgetNew = (SELECT Budget FROM inserted) SELECT @projectNumber = (SELECT Number FROM deleted) INSERT INTO AuditBudget VALUES (@projectNumber, USER_NAME(), GETDATE(), @budgetOld, @budgetNew) END

В этом примере создается таблица AuditBudget, в которой сохраняются все изменения столбца Budget таблицы Project. Изменения этого столбца будут записываться в эту таблицу посредством триггера trigger_ModifyBudget.

Этот триггер активируется для каждого изменения столбца Budget с помощью инструкции UPDATE. При выполнении этого триггера значения строк таблиц deleted и inserted присваиваются соответствующим переменным @budgetOld, @budgetNew и @projectNumber. Эти присвоенные значения, совместно с именем пользователя и текущей датой, будут затем вставлены в таблицу AuditBudget.

В этом примере предполагается, что за один раз будет обновление только одной строки. Поэтому этот пример является упрощением общего случая, когда триггер обрабатывает многострочные обновления. Если выполнить следующие инструкции Transact-SQL:

USE SampleDb; UPDATE Project SET Budget = 200000 WHERE Number = 'p2';

то содержимое таблицы AuditBudget будет таким:

Реализация бизнес-правил

С помощью триггеров можно создавать бизнес-правила для приложений. Создание такого триггера показано в примере ниже:

USE SampleDb; -- Триггер trigger_TotalBudget является примером использования -- триггера для реализации бизнес-правила GO CREATE TRIGGER trigger_TotalBudget ON Project AFTER UPDATE AS IF UPDATE (Budget) BEGIN DECLARE @sum_old1 FLOAT DECLARE @sum_old2 FLOAT DECLARE @sum_new FLOAT SELECT @sum_new = (SELECT SUM(Budget) FROM inserted) SELECT @sum_old1 = (SELECT SUM(p.Budget) FROM project p WHERE p.Number NOT IN (SELECT d.Number FROM deleted d)) SELECT @sum_old2 = (SELECT SUM(Budget) FROM deleted) IF @sum_new > (@sum_old1 + @sum_old2) * 1.5 BEGIN PRINT 'Бюджет не изменился' ROLLBACK TRANSACTION END ELSE PRINT 'Изменение бюджета выполнено' END

Здесь создается правило для управления модификацией бюджетов проектов. Триггер trigger_TotalBudget проверяет каждое изменение бюджетов и выполняет только такие инструкции UPDATE, которые увеличивают сумму всех бюджетов не более чем на 50%. В противном случае для инструкции UPDATE выполняется откат посредством инструкции ROLLBACK TRANSACTION.

Принудительное обеспечение ограничений целостности

В системах управления базами данных применяются два типа ограничений для обеспечения целостности данных: декларативные ограничения, которые определяются с помощью инструкций языка CREATE TABLE и ALTER TABLE; процедурные ограничения целостности, которые реализуются посредством триггеров.

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

В примере ниже показано принудительное обеспечение ссылочной целостности посредством триггеров для таблиц Employee и Works_on:

USE SampleDb; GO CREATE TRIGGER trigger_WorksonIntegrity ON Works_on AFTER INSERT, UPDATE AS IF UPDATE(EmpId) BEGIN IF (SELECT Employee.Id FROM Employee, inserted WHERE Employee.Id = inserted.EmpId) IS NULL BEGIN ROLLBACK TRANSACTION PRINT 'Строка не была вставлена/модифицирована' END ELSE PRINT 'Строка была вставлена/модифицирована' END

Триггер trigger_WorksonIntegrity в этом примере проверяет ссылочную целостность для таблиц Employee и Works_on. Это означает, что проверяется каждое изменение столбца Id в ссылочной таблице Works_on, и при любом нарушении этого ограничения выполнение этой операции не допускается. (То же самое относится и к вставке в столбец Id новых значений.) Инструкция ROLLBACK TRANSACTION во втором блоке BEGIN выполняет откат инструкции INSERT или UPDATE в случае нарушения ограничения для обеспечения ссылочной целостности.

В этом примере триггер выполняет проверку на проблемы ссылочной целостности первого и второго случая между таблицами Employee и Works_on. А в примере ниже показан триггер, который выполняет проверку на проблемы ссылочной целостности третьего и четвертого случая между этими же таблицами (эти случаи обсуждались в статье «Transact-SQL — создание таблиц»):

USE SampleDb; GO CREATE TRIGGER trigger_RefintWorkson2 ON Employee AFTER DELETE, UPDATE AS IF UPDATE (Id) BEGIN IF (SELECT COUNT(*) FROM Works_on, deleted WHERE Works_on.EmpId = deleted.Id) > 0 BEGIN ROLLBACK TRANSACTION PRINT 'Строка не была вставлена/модифицирована' END ELSE PRINT 'Строка была вставлена/модифицирована' END

Триггеры 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.)

В примере ниже показана разница в поведении при вставке значений в вычисляемые столбцы, используя таблицу и ее соответствующее представление:

USE SampleDb; CREATE TABLE Orders ( OrderId INT NOT NULL, Price MONEY NOT NULL, Quantity INT NOT NULL, OrderDate DATETIME NOT NULL, Total AS Price * Quantity, ShippedDate AS DATEADD (DAY, 7, orderdate) ); GO CREATE VIEW view_AllOrders AS SELECT * FROM Orders; GO CREATE TRIGGER trigger_orders ON view_AllOrders INSTEAD OF INSERT AS BEGIN INSERT INTO Orders SELECT OrderId, Price, Quantity, OrderDate FROM inserted END

В этом примере используется таблица Orders, содержащая два вычисляемых столбца. Представление view_AllOrders содержит все строки этой таблицы. Это представление используется для задания значения в его столбце, которое соотносится с вычисляемым столбцом в базовой таблице, на которой создано представление. Это позволяет использовать триггер INSTEAD OF, который в случае инструкции INSERT заменяется пакетом, который вставляет значения в базовую таблицу посредством представления view_AllOrders. (Инструкция INSERT, обращающаяся непосредственно к базовой таблице, не может задавать значение вычисляемому столбцу.)

Триггеры first и last

Компонент Database Engine позволяет создавать несколько триггеров для каждой таблицы или представления и для каждой операции (INSERT, UPDATE и DELETE) с ними. Кроме этого, можно указать порядок выполнения для нескольких триггеров, определенных для конкретной операции. С помощью системной процедуры sp_settriggerorder можно указать, что один из определенных для таблицы триггеров AFTER будет выполняться первым или последним для каждого обрабатываемого действия. Эта системная процедура имеет параметр @order, которому можно присвоить одно из трех значений:

• first — указывает, что триггер является первым триггером AFTER, выполняющимся для модифицирования действия;
• last — указывает, что данный триггер является последним триггером AFTER, выполняющимся для инициирования действия;
• none — указывает, что для триггера отсутствует какой-либо определенный порядок выполнения. (Это значение обычно используется для того, чтобы выполнить сброс ранее установленного порядка выполнения триггера как первого или последнего.)

Изменение структуры триггера посредством инструкции ALTER TRIGGER отменяет порядок выполнения триггера (первый или последний). Применение системной процедуры sp_settriggerorder показано в примере ниже:

USE SampleDb; EXEC sp_settriggerorder @triggername = 'trigger_ModifyBudget', @order = 'first', @stmttype='update'

Для таблицы разрешается определить только один первый и только один последний триггер 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 нужно указать его область действия. Предложение DATABASE указывает в качестве области действия триггера DDL текущую базу данных, а предложение ALL SERVER — текущий сервер.

После указания области действия триггера 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, чья область действия распространяется на текущую базу данных:

USE SampleDb; GO CREATE TRIGGER trigger_PreventDrop ON DATABASE FOR DROP_TRIGGER AS PRINT 'Перед тем, как удалить триггер, вы должны отключить "trigger_PreventDrop"' ROLLBACK

Триггер в этом примере предотвращает удаление любого триггера для базы данных SampleDb любым пользователем. Предложение DATABASE указывает, что триггер trigger_PreventDrop является триггером уровня базы данных. Ключевое слово DROP_TRIGGER указывает предопределенный тип события, запрещающий удаление любого триггера.

Триггеры DDL уровня сервера

Триггеры уровня сервера реагируют на серверные события. Триггер уровня сервера создается посредством использования предложения ALL SERVER в инструкции CREATE TRIGGER. В зависимости от выполняемого триггером действия, существует два разных типа триггеров уровня сервера: обычные триггеры DDL и триггеры входа. Запуск обычных триггеров DDL основан на событиях инструкций DDL, а запуск триггеров входа — на событиях входа.

В примере ниже демонстрируется создание триггера уровня сервера, который является триггером входа:

USE master; GO CREATE LOGIN loginTest WITH PASSWORD = '12345!', CHECK_EXPIRATION = ON; GO GRANT VIEW SERVER STATE TO loginTest; GO CREATE TRIGGER trigger_ConnectionLimit ON ALL SERVER WITH EXECUTE AS 'loginTest' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'loginTest' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'loginTest') > 1 ROLLBACK; END;

Здесь сначала создается имя входа SQL Server loginTest, которое потом используется в триггере уровня сервера. По этой причине, для этого имени входа требуется разрешение VIEW SERVER STATE, которое и предоставляется ему посредством инструкции GRANT. После этого создается триггер trigger_ConnectionLimit. Этот триггер является триггером входа, что указывается ключевым словом LOGON.

С помощью представления sys.dm_exec_sessions выполняется проверка, был ли уже установлен сеанс с использованием имени входа loginTest. Если сеанс уже был установлен, выполняется инструкция ROLLBACK. Таким образом имя входа loginTest может одновременно установить только один сеанс.

Триггеры и среда CLR

Подобно хранимым процедурам и определяемым пользователем функциям, триггеры можно реализовать, используя общеязыковую среду выполнения (CLR — Common Language Runtime). Триггеры в среде CLR создаются в три этапа:

1. Создается исходный код триггера на языке C# или Visual Basic, который затем компилируется, используя соответствующий компилятор в объектный код.
2. Объектный код обрабатывается инструкцией CREATE ASSEMBLY, создавая соответствующий выполняемый файл.
3. Посредством инструкции CREATE TRIGGER создается триггер.

Выполнение всех этих трех этапов создания триггера CLR демонстрируется в последующих примерах. Ниже приводится пример исходного кода программы на языке C# для триггера из первого примера в статье. Прежде чем создавать триггер CLR в последующих примерах, сначала нужно удалить триггер trigger_PreventDrop, а затем удалить триггер trigger_ModifyBudget, используя в обоих случаях инструкцию DROP TRIGGER.

using System; using System.Data.SqlClient; using Microsoft.SqlServer.Server; public class Triggers < public static void ModifyBudget() < SqlTriggerContext context = SqlContext.TriggerContext; if (context.IsUpdatedColumn(2)) // Столбец Budget < float budget_old; float budget_new; string project_number; SqlConnection conn = new SqlConnection("context connection=true"); conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT Budget FROM DELETED"; budget_old = (float)Convert.ToDouble(cmd.ExecuteScalar()); cmd.CommandText = "SELECT Budget FROM INSERTED"; budget_new = (float)Convert.ToDouble(cmd.ExecuteScalar()); cmd.CommandText = "SELECT Number FROM DELETED"; project_number = Convert.ToString(cmd.ExecuteScalar()); cmd.CommandText = @"INSERT INTO AuditBudget (@projectNumber, USER_NAME(), GETDATE(), @budgetOld, @budgetNew)"; cmd.Parameters.AddWithValue("@projectNumber", project_number); cmd.Parameters.AddWithValue("@budgetOld", budget_old); cmd.Parameters.AddWithValue("@budgetNew", budget_new); cmd.ExecuteNonQuery(); >> > 

Пространство имен 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. Следующий шаг состоит в добавлении ссылки на скомпилированную сборку в базе данных:

USE SampleDb; GO CREATE ASSEMBLY CLRStoredProcedures FROM 'D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll' WITH PERMISSION_SET = SAFE

Инструкция CREATE ASSEMBLY принимает в качестве ввода управляемый код и создает соответствующий объект, на основе которого создается триггер CLR. Предложение WITH PERMISSION_SET в примере указывает, что разрешениям доступа присвоено значение SAFE.

Наконец, в примере ниже посредством инструкции CREATE TRIGGER создается триггер trigger_modify_budget:

USE SampleDb; GO CREATE TRIGGER trigger_modify_budget ON Project AFTER UPDATE AS EXTERNAL NAME CLRStoredProcedures.Triggers.ModifyBudget

Инструкция CREATE TRIGGER в примере отличается от такой же инструкции в примерах ранее тем, что она содержит параметр EXTERNAL NAME. Этот параметр указывает, что код создается средой CLR. Имя в этом параметре состоит из трех частей. В первой части указывается имя соответствующей сборки (CLRStoredProcedures), во второй — имя открытого класса, определенного в примере выше (Triggers), а в третьей указывается имя метода, определенного в этом классе (ModifyBudget).

CREATE TRIGGER (Transact-SQL)

Создает триггер языка обработки данных, DDL или входа. Триггер — это особая разновидность хранимой процедуры, которая автоматически выполняется при возникновении события на сервере базы данных. Триггеры DML выполняются, когда пользователь пытается изменить данные с помощью событий языка обработки данных (DML). Событиями DML являются процедуры INSERT, UPDATE или DELETE, применяемые к таблице или представлению. Эти триггеры срабатывают при запуске любого допустимого события независимо от наличия и числа затронутых строк таблицы. Дополнительные сведения см. в разделе DML Triggers.

Триггеры DDL активируются в ответ на разные события языка описания данных (DDL). Эти события прежде всего соответствуют инструкциям Transact-SQL CREATE, ALTER, DROP и некоторым системным хранимым процедурам, которые выполняют схожие с DDL операции.

Триггеры входа могут срабатывать в ответ на событие LOGON, которое возникает при создании пользовательского сеанса. Вы можете создавать триггеры непосредственно из инструкций Transact-SQL или методов сборок, созданных в среде CLR платформы Microsoft .NET Framework и переданных в экземпляр SQL Server. SQL Server позволяет создавать несколько триггеров для любой конкретной инструкции.

Вредоносный программный код внутри триггеров может быть запущен с расширенными правами доступа. Дополнительные сведения о том, как уменьшить эту угрозу, см. в статье Управление безопасностью триггеров.

В этой статье рассматривается интеграция среды CLR .NET Framework с SQL Server. Интеграция со средой CLR не применяется к базе данных SQL Azure.

Синтаксис SQL Server

-- SQL Server Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON < table | view >[ WITH [ . n ] ] < FOR | AFTER | INSTEAD OF > < [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] >[ WITH APPEND ] [ NOT FOR REPLICATION ] AS < sql_statement [ ; ] [ . n ] | EXTERNAL NAME > ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] ::= assembly_name.class_name.method_name 

-- SQL Server Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a -- table (DML Trigger on memory-optimized tables) CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON < table >[ WITH [ . n ] ] < FOR | AFTER > < [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] >AS < sql_statement [ ; ] [ . n ] > ::= [ NATIVE_COMPILATION ] [ SCHEMABINDING ] [ EXECUTE AS Clause ] 

-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, -- REVOKE or UPDATE statement (DDL Trigger) CREATE [ OR ALTER ] TRIGGER trigger_name ON < ALL SERVER | DATABASE >[ WITH [ . n ] ] < FOR | AFTER > < event_type | event_group >[ . n ] AS < sql_statement [ ; ] [ . n ] | EXTERNAL NAME < method specifier >[ ; ] > ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] 

-- Trigger on a LOGON event (Logon Trigger) CREATE [ OR ALTER ] TRIGGER trigger_name ON ALL SERVER [ WITH [ . n ] ] < FOR| AFTER >LOGON AS < sql_statement [ ; ] [ . n ] | EXTERNAL NAME < method specifier >[ ; ] > ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] 

Синтаксис базы данных SQL Azure

-- Azure SQL Database Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON < table | view >[ WITH [ . n ] ] < FOR | AFTER | INSTEAD OF > < [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] >AS < sql_statement [ ; ] [ . n ] [ ; ] >> ::= [ EXECUTE AS Clause ] 

-- Azure SQL Database Syntax -- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, -- REVOKE, or UPDATE STATISTICS statement (DDL Trigger) CREATE [ OR ALTER ] TRIGGER trigger_name ON < DATABASE >[ WITH [ . n ] ] < FOR | AFTER > < event_type | event_group >[ . n ] AS < sql_statement [ ; ] [ . n ] [ ; ] > ::= [ EXECUTE AS Clause ] 

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

OR ALTER
Применимо к: База данных SQL Azure, SQL Server (начиная с SQL Server 2016 (13.x) с пакетом обновления 1 (SP1).

Условно изменяет триггер только в том случае, если он уже существует.

schema_name
Имя схемы, которой принадлежит триггер DML. Действие триггеров DML ограничивается схемой той таблицы или того представления, для которых они созданы. Аргумент schema_name не может указываться для триггеров DDL или триггеров входа.

trigger_name
Имя триггера. Аргумент trigger_name должен соответствовать правилам для идентификаторов с одним дополнительным ограничением: trigger_name не может начинаться с символов # или ##.

table | view
Таблица или представление, в котором выполняется триггер DML. Эту таблицу или представление иногда называют таблицей триггера или представлением триггера соответственно. Указание уточненного имени таблицы или представления не является обязательным. Ссылку на представление можно использовать только в триггере INSTEAD OF. Нельзя определить триггеры DML для локальной или глобальной временных таблиц.

DATABASE
Применяет область действия триггера DDL к текущей базе данных. Если этот аргумент определен, триггер срабатывает всякий раз при возникновении в базе данных события типа event_type или event_group.

ALL SERVER
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Применяет область действия триггера DDL или триггера входа к текущему серверу. Если этот аргумент определен, триггер срабатывает всякий раз при возникновении на текущем сервере события типа event_type или event_group.

WITH ENCRYPTION
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Маскирует текст инструкции CREATE TRIGGER. Использование WITH ENCRYPTION предотвращает публикацию триггера в рамках реплика sql Server. Параметр WITH ENCRYPTION нельзя указать для триггеров CLR.

EXECUTE AS
Указывает контекст безопасности, в котором выполняется триггер. Позволяет контролировать, какую учетную запись пользователя использует экземпляр SQL Server для проверки разрешений на любые объекты базы данных, на которые ссылается триггер.

Этот параметр является обязательным для триггеров в таблицах, оптимизированных для памяти.

Дополнительные сведения см. в разделе EXECUTE AS (Transact-SQL).

NATIVE_COMPILATION
Указывает, что триггер компилируется в собственном коде.

Этот параметр является обязательным для триггеров в таблицах, оптимизированных для памяти.

SCHEMABINDING
Гарантирует, что используемые триггером таблицы ну будут удалены или изменены.

Этот параметр является обязательным для триггеров в таблицах, оптимизированных для памяти, и не поддерживается для триггеров в обычных таблицах.

FOR | AFTER
Значение FOR или AFTER указывает, что триггер DML срабатывает только после успешного запуска всех операций в инструкции SQL, по которой срабатывает триггер. Кроме того, до запуска триггера должны успешно завершиться все каскадные действия и проверки ограничений, на которые есть ссылки.

Нельзя определить триггеры AFTER для представлений.

INSTEAD OF
Указывает, что триггер DML выполняется вместо инструкции SQL, по которой он срабатывает, то есть переопределяет действия запускающих инструкций. Аргумент INSTEAD OF нельзя использовать для триггеров DDL или триггеров входа.

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

Триггеры INSTEAD OF нельзя определять для обновляемых представлений, которые используют параметр WITH CHECK OPTION. Такое действие вызовет ошибку, если триггер INSTEAD OF добавляется к обновляемому представлению с параметром WITH CHECK OPTION. Чтобы удалить этот параметр, выполните инструкцию ALTER VIEW перед определением триггера INSTEAD OF.

< [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] >
Определяет инструкции изменения данных, при применении которых к таблице или представлению срабатывает триггер DML. Укажите хотя бы один вариант. В определении триггера разрешены любые сочетания вариантов в любом порядке.

Для триггеров INSTEAD OF нельзя использовать параметр DELETE в таблицах со ссылочной связью, которая определяет каскадное действие ON DELETE. Аналогично параметр UPDATE недопустим в таблицах, у которых есть ссылочная связь с каскадным действием ON UPDATE.

WITH APPEND
Применимо: SQL Server 2008 (10.0.x) до SQL Server 2008 R2 (10.50.x).

Указывает, что требуется добавить триггер существующего типа. Аргумент WITH APPEND нельзя использовать для триггеров INSTEAD OF и в тех случаях, когда явно указан триггер AFTER. Для сохранения обратной совместимости аргумент WITH APPEND следует использовать только при указании параметра FOR без INSTEAD OF или AFTER. Нельзя указать WITH APPEND, если используется EXTERNAL NAME (то есть триггер является триггером CLR).

event_type
Имя языкового события Transact-SQL, запуск которого вызывает срабатывание триггера DDL. Список событий, которые могут быть использованы в триггерах DDL, приведен в разделе DDL-события.

event_group
Имя предварительно определенной группы относящихся к языку событий Transact-SQL. Триггер DDL срабатывает после запуска любого языкового события Transact-SQL, которое относится к группе event_group. Список групп событий, которые могут быть использованы в триггерах DDL, приведен в разделе Группы DDL-событий.

После завершения инструкции CREATE TRIGGER параметр event_group работает в режиме макроса, добавляя охватываемые им типы события в представление каталога sys.trigger_events.

NOT FOR REPLICATION
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

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

sql_statement
Условия и действия триггера. Условия триггера указывают дополнительные критерии, определяющие, какие события — DML, DDL или событие входа — вызывают выполнение триггера.

Действия триггера, указанные в инструкциях языка Transact-SQL, вступают в силу после попытки использования операции.

Триггеры могут содержать любое количество инструкций языка Transact-SQL любого типа, за некоторыми исключениями. Дополнительные сведения см. в подразделе «Примечания». Триггеры предназначены для проверки или изменения данных при выполнении инструкций модификации или определения данных. Не следует возвращать из них данные пользователю. Инструкции языка Transact-SQL в составе триггера часто содержат выражения языка управления потоком.

Триггеры DML используют логические (концептуальные) таблицы deleted и inserted. По своей структуре они подобны таблице, для которой определен триггер, то есть таблице, к которой применяется действие пользователя. В таблицах deleted и inserted содержатся старые или новые значения строк, которые могут быть изменены действиями пользователя. Например, для запроса всех значений таблицы deleted можно использовать инструкцию:

SELECT * FROM deleted; 

Триггеры DDL и триггеры входа собирают сведения о запускающих событиях с помощью функции EVENTDATA (Transact-SQL). Дополнительные сведения см. в разделе Использование функции EVENTDATA.

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

Типы данных ntext, text и image будут удалены в следующей версии Microsoft SQL Server. Следует избегать использования этих типов данных при новой разработке и запланировать изменение приложений, использующих их в настоящий момент. Вместо них следует использовать типы данных nvarchar(max), varchar(max)и varbinary(max) . Как триггеры AFTER, так и триггеры INSTEAD OF поддерживают данные типов varchar(MAX), nvarchar(MAX) и varbinary(MAX) в таблицах inserted и deleted.

Для триггеров в таблицах, оптимизированных для памяти, единственной инструкцией sql_statement, разрешенной на верхнем уровне, является блок ATOMIC. В блоке ATOMIC допускается только T-SQL, разрешенный в процедурах, компилируемых в собственном коде.

<>method_specifier применимо к SQL Server 2008 (10.0.x) и более поздним версиям.

Указывает метод сборки для связывания с CLR-триггером. Этот метод не должен принимать аргументы и возвращать значения void. class_name должен быть допустимым идентификатором SQL Server и должен существовать как класс в сборке с видимостью сборки. Если класс имеет имя, содержащее точки (.) для разделения частей пространства имен, имя класса должно быть заключено в квадратные скобки ([ ]) или двойные кавычки (» «). Класс не может быть вложенным.

По умолчанию возможность выполнения кода СРЕДЫ CLR в SQL Server отключена. Можно создавать, изменять и удалять объекты базы данных, ссылающиеся на модули управляемого кода, но эти ссылки не выполняются в экземпляре SQL Server, если параметр clr не включен с помощью sp_configure.

Примечания о триггерах DML

Триггеры DML часто используются для применения бизнес-правил и обеспечения целостности данных. SQL Server предоставляет декларативную целостность ссылок (DRI) с помощью инструкций ALTER TABLE и CREATE TABLE. Но декларативное ограничение ссылочной целостности не обеспечивает ссылочную целостность между базами данных. Ограничение ссылочной целостности подразумевает выполнение правил связи между первичными и внешними ключами таблиц. Для обеспечения ограничений ссылочной целостности используйте в инструкциях ALTER TABLE и CREATE TABLE ограничения PRIMARY KEY и FOREIGN KEY. Если ограничения распространяются на таблицу триггера, они проверяются после выполнения триггера INSTEAD OF, но до выполнения триггера AFTER. Если будет обнаружено нарушение ограничений, для триггера INSTEAD OF выполняется откат, а триггер AFTER не срабатывает.

Вы можете указать, какой триггер AFTER будет выполняться для таблицы первым, а какой последним, с помощью sp_settriggerorder. Для таблицы можно определить только один первый и один последний триггер для каждой из операций INSERT, UPDATE и DELETE. Если для таблицы определены другие триггеры AFTER, они выполняются в случайном порядке.

Если инструкция ALTER TRIGGER изменяет первый или последний триггер, для него удаляется метка первого или последнего триггера и порядок сортировки нужно установить заново с помощью sp_settriggerorder.

Триггер AFTER выполняется только после того, как вызывающая срабатывание триггера инструкция SQL успешно выполняется. Успешное выполнение также подразумевает завершение всех ссылочных каскадных действий и проверки ограничений, связанных с измененными или удаленными объектами. Триггер AFTER не вызывает рекурсивное срабатывание триггера INSTEAD OF для той же таблицы.

Если определенный для таблицы триггер INSTEAD OF выполняет в этой таблице какую-либо инструкцию, которая обычно приводит к срабатыванию триггера INSTEAD OF, этот триггер не вызывается рекурсивно. Вместо этого инструкция обрабатывается так, как если бы у таблицы отсутствовал триггер INSTEAD OF и начинается последовательность применения ограничений и выполнения триггеров AFTER. Для примера предположим, что для таблицы определен триггер INSTEAD OF INSERT. Этот триггер выполняет инструкцию INSERT в той же таблице, и в этом случае выполненная в триггере INSTEAD OF инструкция INSERT не приводит к новому срабатыванию триггера. Выполняемая триггером команда INSERT начинает процесс применения ограничений и срабатывания всех триггеров AFTER INSERT, определенных для этой таблицы.

Если определенный для представления триггер INSTEAD OF выполняет по отношению к этому представлению какую-либо инструкцию, которая обычно приводит к срабатыванию триггера INSTEAD OF, триггер рекурсивно не вызывается. Вместо этого инструкция выполняет изменение базовых таблиц, на которых основано представление. В данном случае определение представления должно удовлетворять всем ограничениям, установленным для обновляемых представлений. Определение обновляемых представлений см. в разделе Изменение данных через представление.

Для примера предположим, что для представления определен триггер INSTEAD OF UPDATE. Этот триггер выполняет инструкцию UPDATE в том же представлении, и в этом случае выполненная в триггере INSTEAD OF инструкция UPDATE не приводит к новому срабатыванию триггера. Выполняемая в триггере инструкция UPDATE обрабатывает представление так, как если бы у него не было триггера INSTEAD OF. Столбцы, измененные с помощью инструкции UPDATE, должны принадлежать одной базовой таблице. Каждая модификация базовой таблицы вызывает применение последовательности ограничений и взвод триггеров AFTER, определенных для данной таблицы.

Проверка действий инструкций UPDATE или INSERT на указанные столбцы

Триггер Transact-SQL можно настроить для выполнения некоторых действий при изменении определенных столбцов в инструкциях UPDATE или INSERT. Используйте для этих целей в теле триггера конструкции UPDATE() или COLUMNS_UPDATED. Конструкция UPDATE() проверяет действие инструкций UPDATE или INSERT на одном столбце. COLUMNS_UPDATED проверяет выполнение операций UPDATE или INSERT над множеством столбцов. Эта функция возвращает битовый шаблон с информацией о том, какие столбцы были вставлены или обновлены.

Ограничения триггеров

Инструкция CREATE TRIGGER должна быть первой инструкцией в пакете и может применяться только к одной таблице.

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

Если для уточнения триггера указано имя схемы, имя таблицы необходимо уточнить таким же образом.

Одно и то же действие триггера может быть определено более чем для одного действия пользователя (например, INSERT и UPDATE) в одной и той же инструкции CREATE TRIGGER.

Триггеры INSTEAD OF DELETE и INSTEAD OF UPDATE нельзя определить для таблицы, у которой есть внешний ключ с каскадным действием для операции DELETE или UPDATE.

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

Во время срабатывания триггера результаты возвращаются вызывающему приложению так же, как и в случае с хранимыми процедурами. Чтобы при срабатывании триггера в приложение не возвращались результаты, не включайте в триггер инструкции SELECT, которые возвращают результаты или инструкции присвоения переменных. Если триггер содержит инструкции SELECT, которые возвращают результаты пользователю, либо инструкции присвоения значения переменным, для него требуется особый подход. Возвращаемые результаты нужно будет передать в каждое приложение, которому разрешено изменять таблицу триггера. Если в триггере происходит присвоение переменной, следует использовать инструкцию SET NOCOUNT в начале триггера, чтобы предотвратить возвращение каких-либо результирующих наборов.

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

Инструкция WRITETEXT (с ведением журнала и без него) не запускает триггеры.

Следующие инструкции языка Transact-SQL не разрешены в триггерах DML:

• ALTER DATABASE
• СОЗДАТЬ БАЗУ ДАННЫХ
• DROP DATABASE
• RESTORE DATABASE
• RESTORE LOG
• RECONFIGURE

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

• CREATE INDEX (в т.ч CREATE SPATIAL INDEX и CREATE XML INDEX)
• ALTER INDEX
• DROP INDEX
• DROP TABLE
• DBCC DBREINDEX
• ALTER PARTITION FUNCTION
• ALTER TABLE при использовании для выполнения следующих действий:
  • Добавление, изменение или удаление столбцов.
  • Переключение секций.
  • Добавление или удаление ограничений PRIMARY KEY и UNIQUE.

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

  Оптимизация триггеров DML

  Триггеры работают в транзакциях (в том числе неявных) и блокируют ресурсы на весь период, в течение которого транзакция открыта. Такая блокировка действует, пока транзакция не будет зафиксирована (COMMIT) или отклонена (ROLLBACK). Чем дольше выполняется триггер, тем выше вероятность блокирования другого процесса. Старайтесь создавать такие триггеры, которые выполняются максимально быстро. Один из способов сократить время выполнения — освободить триггер, если инструкция DML изменяет 0 строк.

  Чтобы освободить триггер для команды, которая не изменяет ни одной строки, используйте системную переменную ROWCOUNT_BIG.

  В следующем фрагменте кода T-SQL триггер освобождается для команды, которая не изменяет ни одной строки. Этот код нужно добавить в начале каждого триггера DML:

  IF (ROWCOUNT_BIG() = 0) RETURN; 

  Примечания о триггерах DDL

  Триггеры DDL, как и стандартные триггеры, запускают хранимые процедуры в ответ на какое-либо событие. В отличие от стандартных триггеров, они не срабатывают при выполнении инструкций UPDATE, INSERT или DELETE для таблицы или представления. Вместо этого они обычно срабатывают в ответ на инструкции языка определения данных (DDL). К ним относятся инструкции CREATE, ALTER, DROP, GRANT, DENY, REVOKE и UPDATE STATISTICS. Системные хранимые процедуры, выполняющие операции, подобные операциям DDL, также могут запускать триггеры DDL.

  Протестируйте триггеры DDL, чтобы получить ответ на выполнение системных хранимых процедур. Например, инструкция CREATE TYPE и хранимые процедуры sp_addtype и sp_rename вызовут срабатывание триггера DDL, созданного для события CREATE_TYPE.

  Дополнительные сведения о триггерах DDL см. в разделе Триггеры DDL.

  Триггеры DDL не срабатывают в ответ на события, влияющие на локальные или глобальные временные таблицы и хранимые процедуры.

  В отличие от триггеров DML, триггеры DDL не ограничены областью схемы. Это означает, что для запроса метаданных о триггерах DDL нельзя воспользоваться такими функциями, как OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY и OBJECTPROPERTYEX. Используйте вместо них представления каталога. Дополнительные сведения см. в статье Получение сведений о триггерах DDL.

  Триггеры DDL сервера появляются в обозревателе объектов среды SQL Server Management Studio в папке Triggers . Эта папка находится под папкой Объекты сервера . Триггеры DDL, доступные в области базы данных, находятся в папке Триггеры базы данных. Эта папка находится в папке Программирование соответствующей базы данных.

  Триггеры входа

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

  Если проверка подлинности завершается сбоем, триггеры входа не срабатывают.

  Распределенные транзакции не поддерживаются в триггерах входа. Если триггер содержит распределенную транзакцию, при его срабатывании возвращается ошибка 3969.

  Отключение триггера входа

  Триггер входа может эффективно предотвратить успешные подключения к ядро СУБД для всех пользователей, включая членов предопределенных ролей сервера sysadmin. Если триггер входа запрещает подключения, члены предопределенной роли сервера sysadmin могут подключаться с помощью выделенного подключения администратора или запуска ядро СУБД в минимальном режиме конфигурации (-f). Дополнительные сведения см. в разделе Параметры запуска службы Database Engine.

  Общие соглашения о триггерах

  Возвращаемые результаты

  Возможность возвращать результаты из триггеров будет исключена из следующей версии SQL Server. Триггеры, которые возвращают результирующие наборы, могут привести к непредвиденному поведению в приложениях, не предназначенных для работы с ними. Старайтесь не возвращать результирующие наборы из триггеров во всех новых проектах и постепенно исправляйте такое поведение в существующих приложениях. Чтобы триггеры не возвращали результирующие наборы, для параметра disallow results from triggers необходимо установить значение 1.

  Триггеры входа всегда запрещают возврат результирующих наборов, и это нельзя изменить. Если триггер входа формирует результирующий набор, его не удастся запустить и любая попытка входа, при которой срабатывает такой триггер, будет запрещена.

  Несколько триггеров

  SQL Server позволяет создавать несколько триггеров для каждого события DML, DDL или LOGON. Например, если CREATE TRIGGER FOR UPDATE выполняется для таблицы, которая уже имеет триггер UPDATE, будет создан дополнительный триггер для обновлений. В более ранних версиях SQL Server для каждого события изменения данных INSERT, UPDATE или DELETE допускается только один триггер для каждой таблицы.

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

  SQL Server также поддерживает рекурсивное вызов триггеров, если параметр RECURSIVE_TRIGGERS включен с помощью ALTER DATABASE.

  В рекурсивных триггерах могут возникать следующие типы рекурсии:

  • Косвенная рекурсия При косвенной рекурсии приложение обновляет таблицу T1. Это событие вызывает срабатывание триггера TR1, обновляющего таблицу T2. Затем срабатывает триггер T2, который обновляет таблицу T1.
  • Прямая рекурсия При прямой рекурсии приложение обновляет таблицу T1. Это событие вызывает срабатывание триггера TR1, обновляющего таблицу T1. Поскольку таблица T1 уже была обновлена, триггер TR1 срабатывает снова и т. д.

  В следующем примере используются оба типа рекурсий: прямая и косвенная. Допустим, для таблицы T1 определены два триггера: TR1 и TR2. Триггер TR1 рекурсивно обновляет таблицу T1. Инструкция UPDATE выполняет TR1 и TR2 по одному разу. Кроме того, запуск TR1 вызывает выполнение триггеров TR1 (рекурсивно) и TR2. В таблицах inserted и deleted триггера содержатся строки, которые относятся только к инструкции UPDATE, вызвавшей срабатывание триггера.

  Описанная ситуация имеет место только в том случае, если настройка RECURSIVE_TRIGGERS включена с помощью инструкции ALTER DATABASE. Не существует определенного порядка для выполнения нескольких триггеров, определенных для одного события. Каждый триггер должен быть самодостаточным.

  Отключение настройки RECURSIVE_TRIGGERS предотвращает выполнение только прямых рекурсий. Чтобы отключить косвенную рекурсию, с помощью хранимой процедуры sp_configure присвойте параметру сервера nested triggers значение 0.

  Если один из триггеров (независимо от уровня вложенности) выполняет инструкцию ROLLBACK TRANSACTION, никакие другие триггеры не выполняются.

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

  Для триггеров допускается не более 32 уровней вложенности. Если триггер изменяет таблицу, для которой определен другой триггер, активируется этот второй триггер. Он может, в свою очередь, вызвать третий триггер и так далее. Если любой из триггеров в цепочке отключает бесконечный цикл, то уровень вложенности превышает допустимый предел, и срабатывание триггера отменяется. Когда триггер Transact-SQL запускает управляемый код ссылкой на подпрограмму CLR, тип, или статистическое выражение, такая ссылка считается одним из 32 допустимых уровней вложенности. Это ограничение не распространяется на методы, вызываемые из управляемого кода.

  Чтобы отменить вложенные триггеры, присвойте значение 0 параметру nested triggers хранимой процедуры sp_configure. Конфигурация по умолчанию поддерживает вложенные триггеры. Если вложенные триггеры отключены, отключаются и рекурсивные триггеры, независимо от значения RECURSIVE_TRIGGERS, которое установлено с помощью инструкции ALTER DATABASE.

  Первый триггер AFTER, вложенный в триггер INSTEAD OF, срабатывает даже в том случае, если для сервера настроен нулевой уровень вложенных триггеров. Но в таком случае остальные триггеры AFTER не сработают. Проверьте все приложения на наличие вложенных триггеров, чтобы определить соблюдение бизнес-правил, прежде чем устанавливать значение 0 для параметра nested triggers (вложенные триггеры). Если правила не соблюдаются, внесите соответствующие изменения.

  Отложенная интерпретация имен

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

  Разрешения

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

  Чтобы создать триггер DDL в области сервера (ON ALL SERVER) или триггера входа, требуется разрешение CONTROL SERVER для этого сервера. Чтобы создать триггер DDL в области базы данных (ON DATABASE), требуется разрешение ALTER ANY DATABASE DDL TRIGGER для текущей базы данных.

  Примеры

  А. Использование триггера DML с предупреждающим сообщением

  Следующий триггер DML выводит сообщение клиенту, когда любой пользователь пытается добавить или изменить данные в таблице в Customer базе данных AdventureWorks2022.

  CREATE TRIGGER reminder1 ON Sales.Customer AFTER INSERT, UPDATE AS RAISERROR ('Notify Customer Relations', 16, 10); GO 

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

  В следующем примере указанному пользователю ( MaryM ) по электронной почте отправляется сообщение при изменении таблицы Customer .

  CREATE TRIGGER reminder2 ON Sales.Customer AFTER INSERT, UPDATE, DELETE AS EXEC msdb.dbo.sp_send_dbmail @profile_name = 'AdventureWorks2022 Administrator', @recipients = 'danw@Adventure-Works.com', @body = 'Don''t forget to print a report for the sales force.', @subject = 'Reminder'; GO 

  C. Использование триггера DML AFTER для принудительного применения бизнес-правил между таблицами PurchaseOrderHeader и Vendor

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

  В следующем примере создается триггер DML в AdventureWorks2022 базе данных. Этот триггер проверяет оценку кредитоспособности для поставщика (оценка не равна 5) при попытке добавить новый заказ на покупку в таблицу PurchaseOrderHeader . Чтобы получить оценку кредитоспособности поставщика, требуется ссылка на таблицу Vendor . Если рейтинг кредитоспособности слишком низок, поступает сообщение об этом и вставка не выполняется.

  USE AdventureWorks2022; GO IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL DROP TRIGGER Purchasing.LowCredit; GO -- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table -- when the credit rating of the specified vendor is set to 5 (below average). CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader AFTER INSERT AS IF (ROWCOUNT_BIG() = 0) RETURN; IF EXISTS (SELECT 1 FROM inserted AS i JOIN Purchasing.Vendor AS v ON v.BusinessEntityID = i.VendorID WHERE v.CreditRating = 5 ) BEGIN RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1); ROLLBACK TRANSACTION; RETURN END; GO -- This statement attempts to insert a row into the PurchaseOrderHeader table -- for a vendor that has a below average credit rating. -- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back. INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID, VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight) VALUES ( 2 ,3 ,261 ,1652 ,4 ,GETDATE() ,GETDATE() ,44594.55 ,3567.564 ,1114.8638 ); GO 

  D. Использование триггера DDL уровня базы данных

  В следующем примере триггер DDL используется для предотвращения удаления синонимов в базе данных.

  CREATE TRIGGER safety ON DATABASE FOR DROP_SYNONYM AS IF (@@ROWCOUNT = 0) RETURN; RAISERROR ('You must disable Trigger "safety" to remove synonyms!', 10, 1) ROLLBACK GO DROP TRIGGER safety ON DATABASE; GO 

  Д. Использование триггера DDL уровня сервера

  В следующем примере триггер DDL используется для вывода сообщения при возникновении на данном экземпляре сервера любого из событий CREATE DATABASE, а функция EVENTDATA используется для получения текста соответствующей инструкции на языке Transact-SQL. Примеры использования функции EVENTDATA в триггерах DDL см. в разделе Использование функции EVENTDATA.

  Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

  CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS PRINT 'Database Created.' SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') GO DROP TRIGGER ddl_trig_database ON ALL SERVER; GO 

  F. Использование триггера входа

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

  Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

  USE master; GO CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE, CHECK_EXPIRATION = ON; GO GRANT VIEW SERVER STATE TO login_test; GO CREATE TRIGGER connection_limit_trigger ON ALL SERVER WITH EXECUTE AS 'login_test' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'login_test' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'login_test') > 3 ROLLBACK; END; 

  G. Просмотр событий, вызвавших срабатывание триггера

  В следующем примере выполняются запросы к представлениям каталога sys.triggers и sys.trigger_events с целью определения, какие события языка Transact-SQL вызывали срабатывание триггера safety . Триггер safety , созданный в примере Г, приведен выше.

  SELECT TE.* FROM sys.trigger_events AS TE JOIN sys.triggers AS T ON T.object_id = TE.object_id WHERE T.parent_class = 0 AND T.name = 'safety'; GO 

  3.4. Триггеры

  

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

  Существуют три события, на которые могут реагировать триггеры – добавление, изменение и вставка данных, т.е. любые попытки повлиять на данные. Когда происходит попытка вставки, обновления или удаления данных в таблице, и для этого действия этой таблицы объявлен триггер, он вызывается автоматически. Его нельзя обойти. В отличие от встроенных процедур, триггеры не могут вызываться напрямую и не получают или принимают параметры.

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

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

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

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

  В отличие от ограничения CHECK, триггеры могут ссылаться на поля в другой таблице. Для примера, вы можете поместить триггер на добавления данных для таблицы tbPosition, который будет искать главную должность для добавляемой и проверяет наличие работника с соответствующей должностью.

  3.4.1. Создание триггера

  Для создания триггеров используйте оператор CREATE TRIGGER. В операторе указывается таблица, для которой объявляется триггер, событие, для которого триггер выполняется и индивидуальные инструкции для триггера. В общем команда показана в листинге 3.2.

  Листинг 3.2. Общий вид команды CREATE TRIGGER

  CREATE TRIGGER trigger_name ON < table | view >[ WITH ENCRYPTION ] < < < FOR | AFTER | INSTEAD OF > < [ INSERT ] [ , ] [ UPDATE ] >[ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ < IF UPDATE ( column ) [ < AND | OR >UPDATE ( column ) ] [ . n ] | IF (COLUMNS_UPDATED() updated_bitmask) < comparison_operator >column_bitmask [ . n ] > ] sql_statement [ . n ] > >

  Прежде чем мы рассмотрим реальный пример, давайте рассмотрим два замечания. Когда вы создаете триггер, информация о триггере вставляется в системные таблицы sysobjects и syscomments. Если триггер создается с таким же именем, как и существующий, новый триггер перезаписывает существующий. Сервер SQL не поддерживает добавления триггеров объявленных пользователем на системные таблицы, поэтому вы не можете создавать их для системных таблиц.

  Сервер SQL не позволяет использовать следующие операторы в теле триггера:

  • ALTER DATABASE;
  • CREATE DATABASE;
  • DISK INIT;
  • DISK RESIZE;
  • DROP DATABASE;
  • LOAD DATABASE;
  • LOAD LOG;
  • RECONFIGURE;
  • RESTORE DATABASE;
  • RESTORE LOG.

  Чтобы не запоминать все эти операторы, проще запомнить, что нельзя изменять структуру базы данных.

  3.4.2. Откат изменений в триггере

  Объявление триггера может содержать оператор ROLLBACK TRANSACTION даже если не существует соответствующего BEGIN TRANSACTION. Как мы уже говорили, для любого изменения SQL сервер требует транзакции. Если она не указано явно, то создается неявная транзакция. Если выполняется оператор ROLLBACK TRANSACTION, то все изменения в триггере и изменения, которые стали причиной срабатывания триггера — откатываются.

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

  • Если срабатывает оператор ROLLBACK TRANSACTION, содержимое транзакции откатывается. Если есть операторы, следующие за ROLLBACK TRANSACTION, операторы выполняются. Это может быть не обязательным при использовании команды RETURN;
  • Если триггер откатывает транзакцию, определенную пользователем, то она откатывается полностью. Если триггер сработал, на выполнение модуля, для модуля команды также отменяются. Последующие операторы модуля не выполняются;
  • Вы должны минимизировать использование ROLLBACK TRANSACTION в коде триггера. Откат транзакции создает дополнительную работу, потому что все работы, которые не были закончены на данный момент в транзакции, будут незавершенными. Это будет негативно сказываться на производительности. Запускайте транзакцию после того, как все проверено, чтобы не пришлось ничего откатывать в триггере.

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

  CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS ROLLBACK TRANSACTION

  Как всегда, я разбил все действия на строки, чтобы их лучше было видно и легче было читать и изучать тему. В первой строке, после оператора CREATE TRIGGER стоит название. При именовании триггеров я следую следующему правилу:

  • имя начинается одной или сочетания букв u (update или обновление), i (insert или вставка) или d (delete или удаление). По этим буквам вы легко можете определить, на какие действия срабатывает триггер;
  • после подчеркивания идет имя таблицы, для которого создается триггер.

  После имени идет ключевое слово ON и имя таблицы, для которой создается триггер.

  Во второй строке идет ключевое слово FOR и событие, на которое срабатывает триггер. В данном примере указано действие UPDATE, т.е. обновление. И, наконец, после ключевого слова AS идет тело триггера, т.е. команды, которые должны выполняться. В данном примере выполняется только одна команда — ROLLBACK TRANSACTION, т.е. откат.

  Теперь попробуем изменить данные в таблице tbPeoples, чтобы сработал триггер:

  UPDATE tbPeoples SET vcFamil='dsfg'

  В данном примере мы пытаемся изменить содержимое поля «vcFamil» для всех записей таблицы tbPeoples. Почему пытаемся? Да потому что при изменении срабатывает триггер с откатом транзакции. Выполните выборку данных, чтобы убедиться, что все данные на месте и не изменились:

  SELECT * FROM tbPeoples

  Не смотря на то, что при обновлении данных мы не запускали транзакцию, оператор ROLLBACK TRANSACTION был выполнен без ошибок, и изменения отменились.

  3.4.3. Изменение триггера

  Если вы хотите изменить объявление существующего триггера, вы можете изменить его без удаления и воссоздания. Вы можете ссылаться в объявлении триггера на объекты, которые не существуют. Если во время создания объявления, какой-то объект не существует, то вы увидите только предупреждение.

  Для обновления триггера используется оператор ALTER TRIGGER. Общий вид оператора можно увидеть в листинге 3.3.

  Листинг 3.3. Оператор обновления триггера

  ALTER TRIGGER trigger_name ON ( table | view ) [ WITH ENCRYPTION ] < < ( FOR | AFTER | INSTEAD OF ) < [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] >[ NOT FOR REPLICATION ] AS sql_statement [ . n ] > | < ( FOR | AFTER | INSTEAD OF ) < [ INSERT ] [ , ] [ UPDATE ] >[ NOT FOR REPLICATION ] AS < IF UPDATE ( column ) [ < AND | OR >UPDATE ( column ) ] [ . n ] |IF(COLUMNS_UPDATED() < bitwise_operator >updated_bitmask) < comparison_operator >column_bitmask [ . n ] > sql_statement [ . n ] > >

  Давайте изменим наш триггер u_tbPeoples так, чтобы он реагировал и при добавлении записей. Для этого выполняем следующий запрос:

  ALTER TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE, INSERT AS ROLLBACK TRANSACTION

  Как видите, оператор обновления похож на создание триггера. Разница в том, что в первой строке стоит оператор ALTER TRIGGER. Во второй строке произошло изменение, и теперь триггер будет срабатывать не только на обновление (UPDATE), но и на добавление (INSERT).

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

  INSERT INTO tbPeoples(vcFamil) VALUES('ПЕТЕЧКИН')

  Вы можете включать и выключать определенный триггер или все триггеры на таблицу. Когда триггер отключен, он все еще существует в таблице, однако не выполняется на указанные события. Вы можете отключить триггер с помощью команды ALTER TABLE. В общем виде оператор выглядит следующим образом:

  ALTER TABLE table TRIGGER

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

  ALTER TABLE tbPeoples DISABLE TRIGGER u_tbPeoples

  В первой строке мы пишем оператор ALTER TABLE и имя изменяемой таблицы. Во второй строке нужно указать ключевое слово DISABLE (отключить) или ENABLE (включить) и ключевое слово TRIGGER. И, наконец, имя триггера.

  Попробуйте теперь добавить запить в таблицу tbPeoples. На этот раз, все пройдет успешно.

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

  ALTER TABLE tbPeoples ENABLE TRIGGER ALL

  3.4.4. Удаление триггеров

  Для удаления триггера вы можете воспользоваться оператором DROP TRIGGER. Он удаляется автоматически, когда связанная с ним таблица удаляется.

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

  DROP TRIGGER u_tbPeoples

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

  3.4.5. Как работают триггеры?

  В данной главе мы более глубоко рассмотрим, как работают различные типы триггеров. Для этого мы напишем множество примеров, максимально приближенных к реальности, а заодно получим хорошую практику программирование на языке Transact-SQL и создания триггеров.

  Триггер INSERT

  Что происходит, когда срабатывает триггер добавления записей? Давайте рассмотрим выполняемые сервером шаги:

  • Пользователем выполняется оператор INSERT для добавления записей;
  • Сервер сохраняет информацию о запросе в журнале транзакций;
  • Вызывается триггер;
  • Подтверждение изменений и физическое изменение данных.

  Во время вызова триггера, физического изменения в базе еще не произошло. В теле триггера вы можете увидеть добавляемые записи в виде таблицы inserted. Нет, такой таблицы в базе данных не существует, inserted – это логическая таблица, которая содержит копию строк, которые должны быть вставлены в таблицу. Если быть точнее, она содержит журнал активности оператора INSERT. Вы можете использовать данные из этой таблицы для определения вставляемых данных. Строки из таблицы inserted всегда дублируют одну или несколько строк таблицы триггера.

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

  Таблица inserted всегда содержит такую же структуру, что и у таблицы, на которую установлен триггер.

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

  Листинг 3.4. Использование таблицы inserted

  CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples FOR INSERT AS DECLARE @Name varchar(50) SELECT @Name=vcName FROM inserted IF @Name='ВАСЯ' BEGIN PRINT 'ОШИБКА' ROLLBACK TRANSACTION END

  В данном примере мы создаем триггер на добавление записей. Внутри триггера мы объявляем переменную @Name типа varchar длиной в 50 символов. В эту переменную мы сохраняем содержимое поля «vcName» таблицы inserted. Далее проверяем, если имя равно Вася, то сообщаем об ошибке и откатываем транзакцию. Иначе, строка будет удачно добавлена.

  Давайте для закрепления материала, напишем триггер, который запретит нулевые значения для поля «vcName». Код такого триггера можно увидеть в листинге 3.5.

  Листинг 3.5. Запрет нулевых значений в поле с помощью триггера

  CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples FOR INSERT AS IF EXISTS (SELECT * FROM inserted WHERE vcName is NULL) BEGIN PRINT 'ОШИБКА, вы должны заполнить поле vcName' ROLLBACK TRANSACTION END

  В этом примере мы проверяем, если в таблице inserted есть записи с нулевым значением поля «vcName», то откатываем попытку добавления.

  Триггер DELETE

  • Пользователем выполняется оператор DELETE для добавления записей;
  • Сервер сохраняет информацию о запросе в журнале транзакций;
  • Вызывается триггер;
  • Подтверждение изменений и физическое изменение данных.

  Удаляемые строки помещаются в таблицу deleted, с помощью которой вы можете увидеть удаляемые строки. Это логическая таблицf, которая ссылается на данные журнала оператора DELETE.

  Вы должны учитывать:

  • когда строки добавляются в таблицу deleted, они еще существуют в таблице базы данных;
  • для таблицы deleted выделяется память, поэтому она всегда в кэше;
  • триггер удаления не выполняется на операцию TRUNCATE TABLE (очистка таблицы) потому что эта операция не заносится в журнал и не удаляет строк.

  Давайте попробуем создать триггер, который запретит удаление пользователя с определенным именем. Пример такого триггера можно увидеть в листинге 3.6.

  Листинг 3.6. Пример запрета удаления с помощью триггера

  CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF EXISTS (SELECT * FROM deleted WHERE vcName='рлр') BEGIN PRINT 'ОШИБКА, нельзя удалить этого пользователя' ROLLBACK TRANSACTION END

  В этом примере мы проверяем, если в таблице deleted существует запись с именем «рлр», то откатываем удаление. Добавьте в таблице запись с именем «рлр» и попытайтесь ее удалить. В ответ вы должны увидеть ошибку.

  А что если попытаться удалить несколько записей? Например, в следующем примере удаляются записи две записи:

  DELETE FROM tbPeoples WHERE vcName='рлр' or vcName='ВАСИЛИЙ'

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

  Посмотрим на еще один пример в котором запрещается удаление генерального директора. Без триггера такое сделать невозможно:

  CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF EXISTS (SELECT * FROM deleted WHERE idPosition=1) BEGIN PRINT 'ОШИБКА, нельзя удалить этого пользователя' ROLLBACK TRANSACTION END

  В этом примере, запрещается удаление записи, если поле «idPosition» равно 1. Попробуйте удалить такую запись:

  DELETE FROM tbPeoples WHERE idPosition=1

  Самое интересное, что вы увидите ошибку не триггера, а ограничение внешнего ключа. У генерального директора есть номера телефонов, а запись нельзя удалять, если есть внешняя связь, иначе нарушиться целостность. Значит, триггеры срабатывают после проверки всех ограничений CHECK и внешних ключей. Вполне логично, ведь ограничения работают быстрее и желательно проверить сначала их. Если быстрая проверка даст отрицательный результат, зачем выполнять более сложные проверки в триггере.

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

  Триггер UPDATE

  Обновление происходит в два этапа – удаление и вставка. Нет, физически в базе данных происходит изменение, это триггер видит два этапа. Поэтому существующие строки помещаются в таблицу deleted (то есть то, что было), а новые данные помещаются в таблицу inserted. Триггер может проверять эти таблицы для определения, какие строки и как могут измениться.

  Вы можете объявить триггер для мониторинга обновления определенного поля с помощью указания опции IF UPDATE. Это позволяет триггеру изолировать активность определенной колонки. Когда обнаруживается обновление определенной колонки, триггер может выполнить определенные действия, такие как выброс сообщения об ошибке, которое сообщит о невозможности обновления колонки.

  Давайте создадим триггер на таблицу tbPeoples, который будет выводить на экран сообщение, если изменяется поле «vcName»

  CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS IF UPDATE (vcName) PRINT 'Я надеюсь, что вы правильно указали имя'

  После оператора IF UPDATE, в скобках указано поле, которое необходимо проверить, было ли оно изменено. Если да, то будет выполнен следующий за проверкой оператор. В данном случае, это вывод на экран сообщения с помощью PRINT. Когда указанное поле не изменяется, то оператор конечно же не выполняется. Если нужно выполнить несколько операторов, то объедините их с помощью BEGIN и END.

  Следующий запрос тестирует триггер:

  UPDATE tbPeoples SET vcName='ИВАНУШКА' WHERE vcFamil='ПОЧЕЧКИН'

  Убедитесь, что сообщение из триггера выводится на экран.

  Давайте с помощью триггера попробуем запретить изменение полей, составляющих ФИО («vcFamil», «vcName» и «vcSurName»). Для этого, если изменено одно из этих полей, то выводим на экран сообщение о запрете и откатываем транзакцию:

  CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS IF UPDATE (vcName) OR UPDATE (vcFamil) OR UPDATE (vcSurname) BEGIN PRINT 'Нельзя изменять фамилию, имя и отчество' ROLLBACK TRANSACTION END

  С помощью такого запроса легко увидеть, как проверять обновление сразу нескольких полей и выводить несколько операторов. Обратите внимание, что проверку делает именно оператор UPDATE, а не IF UPDATE. Я даже не знаю, почему разработчики SQL Server объединяют эти два оператора. Первый, это логический оператор, а второй – проверка, было ли обновлено поле.

  3.4.6. INSTEAD OF

  Вы можете указать триггер INSTEAD OF для таблиц и просмотрщиков. Действия такого триггера выполняются вместо операторов, сгенерировавших триггер. Не понятно? Рассмотрим пример. Допустим, что у вас есть триггер INSTEAD OF на событие обновления таблицы. Если пользователь выполняет обновление, то выполняется триггер, но при этом, оператор, запущенный пользователем, только генерирует событие. Реальное обновление данных должно происходить с помощью операторов триггера.

  Каждая таблица или просмотрщик ограничены одним триггером INSTEAD OF на каждое событие. Вы не можете создавать триггеры INSTEAD OF на просмотрщик у которого включена опция CHECK OPTIONS.

  Как можно использовать INSTEAD OF? Допустим, что у нас есть объект просмотра, который выбирает данные их двух таблиц. Как мы уже знаем, данные вьюшки можно изменять, только если все они принадлежат одной таблице. Но с помощью триггера можно сделать обновление любого количества таблиц.

  Давайте создадим объект просмотра, который будет выбирать фамилию работника и название должности. Назовем этот объект просмотра Peoples:

  CREATE VIEW People AS SELECT vcFamil, vcPositionName FROM tbPosition ps, tbPeoples pl WHERE ps.idPosition=pl.idPosition

  Теперь создадим триггер INSTEAD OF на этот объект просмотра, с помощью которого, можно будет добавлять записи и при этом, они корректно будут прописываться, каждая в свою таблицу:

  Листинг 3.7. Триггер INSTEAD OF для вставки данных

  CREATE TRIGGER i_People ON dbo.People INSTEAD OF INSERT AS BEGIN -- Добавление должности INSERT INTO tbPosition (vcPositionName) SELECT vcPositionName FROM inserted i -- Добавление работника INSERT INTO tbPeoples (vcFamil, idPosition) SELECT vcFamil, idPosition FROM inserted i,tbPosition pn WHERE i.vcPositionName=pn.vcPositionName END

  В этом примере интересности начинаются прямо со второй строки. Здесь указывается оператор INSTEAD OF и событие, на которое нужно реагировать. В данном случае в качестве события выступает вставка (INSERT).

  В качестве кода триггера мы выполняем два SQL запроса: добавление должности работника и самого работника. Первый запрос достаточно прост, потому что достаточно просто выбрать все имена должностей из таблицы inserted и вставить их в таблицу tbPosition. А вот во втором запросе, помимо вставки фамилии работника, нужно найти должность и навести связь, иначе нет смысла затевать такие сложные махинации. Вот как я решаю эту проблему:

  INSERT INTO tbPeoples (vcFamil, idPosition) SELECT vcFamil, idPosition FROM inserted i,tbPosition pn WHERE i.vcPositionName=pn.vcPositionName

  Попробуйте выполнить следующий запрос на добавление записей в объект просмотра:

  INSERT INTO People VALUES('ИВАНУШКИН', 'Клерк')

  Выполните следующий запрос и убедитесь, что новая запись добавлена:

  SELECT * FROM People

  При обновлении таблицы есть одна проблема – нужно связать обновляемые данные с существующими. Первым на ум приходит запрос типа:

  UPDATE tbPosition SET vcPositionName=i.vcPositionName FROM tbPosition pn, inserted i WHERE i.vcPositionName = pn.vcPositionName

  Здесь мы связываем таблицу должностей с таблицей inserted. Но такой запрос никогда не будет выполнен. Почему? В inserted находятся новые значения, а в tbPosition еще старые и названия должностей никогда не свяжутся. Если связать с таблицей deleted, то записи свяжутся, но мы не будем знать новых значений, которые нужно занести в таблицу. Проблему можно решить, но лучшим вариантом будет добавление в объект просмотра ключевых полей:

  ALTER VIEW People AS SELECT idPeoples, pl.idPosition, vcFamil, vcPositionName FROM tbPosition ps, tbPeoples pl WHERE ps.idPosition=pl.idPosition

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

  Листинг 3.8. Обновление связанной вьюшки с помощью триггера

  CREATE TRIGGER u_People ON dbo.People INSTEAD OF UPDATE AS BEGIN UPDATE tbPosition SET vcPositionName=i.vcPositionName FROM tbPosition pn, inserted i WHERE i.idPosition=pn.idPosition UPDATE tbPeoples SET vcFamil=i.vcFamil FROM tbPeoples pl, inserted i WHERE i.idPeoples=pl.idPeoples END

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

  UPDATE People SET vcFamil='ИВАНУШКИН', vcPositionName='Генеральный директор' WHERE idPeoples=40 AND idPosition=13

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

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

  3.4.7. Дополнительно о триггерах

  Вы можете использовать триггеры для обеспечения комплексной целостности ссылок с помощью:

  • Выполнения действий или каскадного обновления или удаления. Целостность ссылок может отличаться при использовании ограничений FOREIGN KEY и REFERENCE в операторе CREATE TABLE. Но триггер выгоден для гарантирования необходимых действий, когда должны быть произведены каскадные удаления или обновления, потому что триггеры более мощные. Если ограничение существует для таблицы с триггером, оно проверяется до выполнения триггера. Если ограничение нарушено, то триггер не работает. Если ограничение не сработает, то с помощью триггера можно реализовать более сложные проверки, которые уж точно будут гарантировать, что данные не нарушат целостность и пользователь внесет только те данные, которые разрешены;
  • Вы должны учитывать, что в таблицу может вставляться сразу несколько строк. Вы должны учитывать это при написании триггеров, как мы это делали при создании примеров с использованием INSTEAD OF;
  • Ограничения, правила и значения по умолчанию могут генерировать только стандартные системные ошибки. Если вам нужны собственные сообщения, вы должны использовать триггеры.

  При разработке триггеров, вы должны учитывать, что таблицы могут иметь несколько триггеров для любого действия. Каждый триггер может быть объявлен для нескольких или одного действия. Например, в следующем примере обрабатывается два события INSERT и UPDATE:

  CREATE TRIGGER iu_tbPeoples ON dbo.tbPeoples FOR INSERT, UPDATE AS Действие

  Если на одно действие назначено несколько триггеров, чтобы не конфликтовали имена можно к имени добавить слово, которое будет описывать выполняемые действия или назначение.

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

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

  Теперь поговорим о производительности триггеров. Они выполняются достаточно быстро, потому что:

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

  Используйте триггеры только там, где это необходимо. Старайтесь возложить основные операции по обеспечению целостности на ограничения. Если нельзя найти другого выхода, то для повышения производительности сервера делайте объявление операторов триггеров простыми, на сколько это возможно. Так как триггер является частью транзакции, блокировки сохраняются, пока транзакция не завершится, поэтому здесь скорость обработки наиболее важна.

  3.4.8. Практика использования триггеров

  Давайте рассмотрим несколько примеров, чтобы закрепить знания и заодно увидеть готовые решения, которые могут вам пригодиться в будущем.

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

  Итак, давайте создадим триггер, который при изменении или удалении строк в таблице tbPeoples будет копировать их в таблицу истории tbpeoplesHistory. Если бы первичный ключ был в виде уникального идентификатора, то задача решалась бы следующим образом:

  CREATE TRIGGER ud_tbPeoples ON dbo. tbPeoples FOR UPDATE, DELETE AS INSERT INTO tbPeoplesHistory SELECT newid(), del.* FROM Deleted del

  Таблица tbPeoplesHistory один к одному повторяет таблицу tbPeoples, но у нее свой первичный ключ, то есть к структуре tbPeoples мы добавили в начало одно поле. Зачем, когда можно было бы использовать поле первичного ключа основной таблицы? Дело в том, что этот ключ лучше всего сохранить не тронутым, чтобы в любой момент можно было восстановить связь записи из истории с другими таблицами базы данных.

  В данном примере содержимое таблицы Deleted копируется в таблице tbPeoplesHistory. Запрос упрощается тем, что первичный ключ можно сгенерировать с помощью функции newid().

  Но в нашей задаче первичный ключ автоматически увеличиваемый и его нельзя генерировать. Придется перечислять все поля:

  CREATE TRIGGER ud_tbPeoplesHistory ON dbo.tbPeoples FOR UPDATE, DELETE AS INSERT INTO tbPeoplesHistory (idPeoples, vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) SELECT del.* FROM Deleted del

  Теперь посмотрим, как можно запретить удаление более чем одной строки:

  CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF (SELECT count(*) FROM deleted)>1 BEGIN PRINT 'Нельзя удалять более одной строки' ROLLBACK TRANSACTION END

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

  Вложение при инсталляции включено, но вы можете отключить и снова включить с помощью системной процедуры sp_configure. Например, следующий пример отключает вложенные триггеры:

  sp_configure ‘nested triggers’, 0

  Триггеры могут иметь вложения до 32 уровней. Если какой-нибудь триггер зациклится, то будет превышен предел. Триггер прерывается и транзакция откатывается.

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

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

  Вложенные триггеры сложны в разработке, потому что требует комплексный и хорошо планированный дизайн. Каскадное изменение может изменить данные, на которые вы не хотели воздействовать. Именно поэтому, иногда вложенные триггеры проще отключить.

  Любой триггер может воздействовать на другие таблицы или ту же самую. Если включена опция рекурсивного вызова, триггер, который изменяет данные в таблице, может активировать себя снова. По умолчанию эта опция отключена, когда база данных создается. Вы можете включить эту опцию с помощью оператора ALTER DATABASE. Пример включения рекурсивных триггеров:

  ALTER DATABASE FlenovSQLBook SET RECURSIVE_TRIGGERS ON

  Если опция вложения отключена, то и рекурсия тоже отключена, и это необходимо всегда помнить.

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

  CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS DELETE pn FROM tbPhoneNumbers pn, inserted i WHERE pn.idPeoples=i.idPeoples

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

  1. При добавлении записи в таблицу телефонов увеличиваем значение поля в таблицы работников;
  2. При удалении номера телефона, уменьшаем значения поля.

  Попробуйте реализовать это самостоятельно, чтобы закрепить знания и потренироваться в работе с SQL запросами.

  Для определения таблиц с триггером, выполните процедуру sp_depends. Например, выполните следующую команду, чтобы увидеть все зависимости для таблицы tbPeoples:

  EXEC sp_depends 'tbPeoples'

  Для определения, какие триггеры существуют на определенную таблицу, и на какие действия выполните процедуру sp_helptrigger. Следующий пример отображает все триггеры, которые принадлежат объекту просмотра People (если нужно просмотреть триггеры таблицы, то укажите ее имя):

  EXEC sp_helptrigger People

  Для просмотра кода существующего триггера используйте sp_helptext. Например, следующая команда позволяет увидеть текст триггера u_People, которую мы создавали для объекта просмотра:

  EXEC sp_helptext u_People

  Триггеры в MySQL

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

  Синтаксис создания триггера:

  CREATE TRIGGER trigger_name trigger_time trigger_event
  ON tbl_name FOR EACH ROW trigger_stmt * This source code was highlighted with Source Code Highlighter .

  trigger_name — название триггера
  trigger_time — Время срабатывания триггера. BEFORE — перед событием. AFTER — после события.
  trigger_event — Событие:
  insert — событие возбуждается операторами insert, data load, replace
  update — событие возбуждается оператором update
  delete — событие возбуждается операторами delete, replace. Операторы DROP TABLE и TRUNCATE не активируют выполнение триггера
  tbl_name — название таблицы
  trigger_stmt выражение, которое выполняется при активации триггера

  Применение

  Лог

  Исходные данные:

  — таблица, за которой мы будем следить
  CREATE TABLE `test` (
  `id` INT ( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `content` TEXT NOT NULL
  ) ENGINE = MYISAM
  — лог
  CREATE TABLE `log` (
  `id` INT ( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `msg` VARCHAR ( 255 ) NOT NULL ,
  ` time ` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `row_id` INT ( 11 ) NOT NULL
  ) ENGINE = MYISAM
  — триггер
  DELIMITER |
  CREATE TRIGGER `update_test` AFTER INSERT ON `test`
  FOR EACH ROW BEGIN
  INSERT INTO log Set msg = ‘insert’ , row_id = NEW .id;
  END ; * This source code was highlighted with Source Code Highlighter .

  Теперь добавьте запись в таблицу test. В таблице log тоже появится запись, обратите внимание на поле row_id, в нем хранится id вставленной вами строки.

  Расширенный лог:

  Исходные данные:

  — Удаляем триггер
  DROP TRIGGER `update_test`;
  — Cоздадим еще одну таблицу,
  — в которой будут храниться резервные копии строк из таблицы test
  CREATE TABLE `testing`.` backup ` (
  `id` INT ( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `row_id` INT ( 11 ) UNSIGNED NOT NULL ,
  `content` TEXT NOT NULL
  ) ENGINE = MYISAM
  — триггеры
  DELIMITER |
  CREATE TRIGGER `update_test` before update ON `test`
  FOR EACH ROW BEGIN
  INSERT INTO backup Set row_id = OLD .id, content = OLD .content;
  END ;

  CREATE TRIGGER `delete_test` before delete ON `test`
  FOR EACH ROW BEGIN
  INSERT INTO backup Set row_id = OLD .id, content = OLD .content;
  END * This source code was highlighted with Source Code Highlighter .

  Теперь если мы отредактируем или удалим строку из test она скопируется в backup.

  зы: надеюсь статья была интересной и полезной
  UPD: для создания триггеров в версии до 5.1.6 требуются полномочия суперпользователя.

  Похожие публикации:

  1. Как отключить т9 на андроиде xiaomi
  2. Как писать с нижним подчеркиванием в ворде
  3. Как продлить подписку в гугл плей
  4. Как снять ремешок с apple watch