Как создать триггер в sql server management studio

SQL Server — How to Create a Trigger?

This article aims to provide comprehensive details about SQL Server Triggers . It will cover what a trigger is, its usage, the advantages and limitations of using a trigger, the restrictions and permissions required, different types of triggers, and the difference between INSTEAD OF and AFTER triggers. The main focus will be on how to create a trigger using two different tools: sqlcmd and DbSchema .

Prerequisites

• Basic knowledge of SQL Server
• Familiarity with SQL commands
• Installed SQL Server Management Studio (SSMS)
• Installed DbSchema

For installation and establishing connection you can read our article SQL Server-How to create a database?

What is a Trigger?

In SQL Server, a trigger is a special type of stored procedure that automatically executes when an event occurs in the database server. Triggers are used to maintain the integrity of the data on the database.

Usage of Triggers

Triggers are commonly used to perform the following operations:

1. Log historical data
2. Enforce business rules and data integrity
3. Replicate data
4. Prevent invalid transactions
5. Maintain complex integrity constraints

Advantages and Limitations of Using a Trigger

Advantages	Limitations
Maintain data consistency	Hard to view the business logic because it’s encapsulated in the trigger
Perform complex checks of updates	Can lead to decreased performance
Can respond to data modifications automatically	Debugging can be difficult
Ensure complex business rules are enforced	Can cause unexpected side effects if not properly managed

Restrictions on Using a Trigger

Triggers must be defined on a table and can’t be defined on views. Also, the functionality of a trigger should not be coded to affect other objects in the database that have related triggers.

Permissions Required for Using a Trigger

To create a trigger, you require ALTER permission on the table or view on which the trigger is being defined. If the trigger is created on a schema or the database, then the CONTROL permission is required.

Types of Triggers

Trigger Type	Description
DML Triggers	Responds to Data Manipulation Language (DML) events
DDL Triggers	Responds to Data Definition Language (DDL) events
LOGON Triggers	Responds to LOGON events

Difference Between INSTEAD OF and AFTER Triggers

INSTEAD OF	AFTER
Definition	Defined on a table or view and replaces the triggering event	Defined on a table to respond to a triggering event
When it Fires	Fires instead of the triggering event	Fires after the triggering event
Where Used	Commonly used for views	Commonly used for tables

How to Create a Trigger in sqlcmd

sqlcmd is a command-line utility that comes with SQL Server. You can interact with your database by writing SQL queries directly in your terminal.

Syntax

CREATE TRIGGER trigger_name ON table_name FOR/AFTER/INSTEAD OF [INSERT/UPDATE/DELETE] AS BEGIN -- Trigger body END; 

Sample Database:

we will consider an initial Employees table and an initial LogTable. The Employees table and LogTable are represented as follows:

EmpID	_Name_	_Department_	_Salary_
1	Alice	HR	5000
2	Bob	IT	6000

LogID	LogInfo
1	Record Updated
2	Record Deleted

DML Trigger

Step 1: Connect to your SQL Server instance with the following command:

sqlcmd -S -U -P

Replace server name with your server’s name , username with your username and password with your password.

Step 2: Use the database where you want to create the trigger:

USE ; GO 

Replace with your database’s name

Step 3: Write a query to create a DML Trigger. For instance, to create an AFTER INSERT trigger:

CREATE TRIGGER tr_AfterInsert ON Employees AFTER INSERT AS BEGIN INSERT INTO LogTable (LogInfo) VALUES ('New record inserted into Employees table'); END; GO 

In the above query, tr_AfterInsert is a trigger which fires after an INSERT operation on the Employees table.

Results from the Query:

Following result will be obtained by executing the above query on our sample database:

LogID	LogInfo
1	Record Updated
2	Record Deleted
3	New record inserted into Employees table

DDL Trigger

For creating a DDL trigger that logs all CREATE_TABLE operations in the database, follow the same first two steps to connect to your server and select your database. Then, write your DDL trigger creation query:

CREATE TRIGGER tr_DDL ON DATABASE FOR CREATE_TABLE AS BEGIN INSERT INTO DDLLogTable (Event) VALUES ('CREATE_TABLE event has occurred'); END; GO 

Sample Database:

Sure, let’s assume that initially the DDLLogTable is as follows:

EventID	Event
1	Database Created
2	Table Deleted

Results from the Query:

Following result will be obtained by executing the above query on our sample database:

_EventID_	_Event_
1	Database Created
2	Table Deleted
3	CREATE_TABLE event has occurred

This shows that the trigger tr_DDL successfully captured the CREATE TABLE operation and logged it into the DDLLogTable .

LOGON Trigger

Similarly, for creating a LOGON trigger that logs all successful logins to your server:

CREATE TRIGGER tr_Logon ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN INSERT INTO LogonLogTable (Event) VALUES ('Successful login'); END; GO 

Sample Database:

Let’s assume we have an initial LogonLogTable that looks like this:

LogID	Event	_EventTime_
1	Unsuccessful login	2023-07-01 08:00:00
2	Successful login	2023-07-01 09:00:00

Results from the Query:

Following result will be obtained by executing the above query on our sample database:

After a successful login, the LogonLogTable will look like this:

LogID	Event	EventTime
1	Unsuccessful login	2023-07-01 08:00:00
2	Successful login	2023-07-01 09:00:00
3	Successful login	2023-07-01 10:00:00

The tr_Logon trigger has successfully captured the successful login event and logged it into the LogonLogTable .

How to Create a Trigger in DbSchema

DbSchema is a visual database designer and management tool that allows you to interact with your database in a more user-friendly manner.

DML Trigger

Step 1: Open DbSchema and connect to your database.

Step 2: In the Schemas panel, select the table where you want to add a trigger.

Step 3: Right-click on the table and select Create Trigger .

Step 4: In the dialog box, provide the necessary details (name, timing, event).

Step 5: Write the SQL statements in the Trigger Code editor:

BEGIN INSERT INTO LogTable (LogInfo) VALUES ('New record inserted into Employees table'); END 

Step 6: Click Apply .

DDL Trigger

In DbSchema, creating DDL triggers follows a similar process, but you’ll need to select Schema or Database in the Create Trigger dialog box. Write your SQL in the editor:

BEGIN INSERT INTO DDLLogTable (Event) VALUES ('CREATE_TABLE event has occurred'); END 

LOGON Trigger

As of the last update in 2021, DbSchema does not natively support the creation of LOGON triggers through its GUI. However, you can still execute a SQL command to create LOGON triggers within DbSchema’s SQL editor, similar to sqlcmd:

CREATE TRIGGER tr_Logon ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN INSERT INTO LogonLogTable (Event) VALUES ('Successful login'); END; 

And there you have it! You’ve successfully created DML, DDL, and LOGON triggers using sqlcmd and DbSchema. Please note that you must have the appropriate permissions to create and manage triggers in SQL

Visually Manage SQL Server using DbSchema

DbSchema is a SQL Server client and visual designer . DbSchema has a free Community Edition, which can be downloaded here.

Key Features of DbSchema:

Following are the key features of DbSchema which distinguish it from other database GUI tools.

Conclusion

Triggers play a crucial role in enforcing business rules and maintaining data integrity in SQL Server databases. Despite their advantages, triggers must be used judiciously due to their potential impact on database performance and complexity.

References

1. DbSchema Documentation
2. Sqlcmd utility

Триггеры

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

Triggers in SQL Server

The trigger is a database object similar to a stored procedure that is executed automatically when an event occurs in a database. There are different kinds of events that can activate a trigger like inserting or deleting rows in a table, a user logging into a database server instance, an update to a table column, a table is created, altered, or dropped, etc.

For example, consider a scenario where the salary of an employee in the Employee table is updated. You might want to preserve the previous salary details in a separate audit table before it gets updated to its new value. You can create a trigger to automatically insert updated employee data to the new audit table whenever the Employee table’s value is updated.

There are three types of triggers in SQL Server

• DML triggers are automatically fired when an INSERT, UPDATE or DELETE event occurs on a table.
• DDL triggers are automatically invoked when a CREATE, ALTER, or DROP event occurs in a database. It is fired in response to a server scoped or database scoped event.
• Logon trigger is invoked when a LOGON event is raised when a user session is established.

DML Triggers

DML (Data Manipulation Language) trigger is automatically invoked when an INSERT, UPDATE or DELETE statement is executed on a table.

Use the CREATE TRIGGER statement to create a trigger in SQL Server.

Syntax: Create Trigger

CREATE TRIGGER [schema_name.]trigger_name ON < table_name | view_name > < FOR | AFTER | INSTEAD OF > [NOT FOR REPLICATION] AS

In the above syntax:

• schema_name (optional) is the name of the schema where the new trigger will be created.
• trigger_name is the name of the new trigger.
• ON < table_name | view_name >keyword specifies the table or view name on which the trigger will be created.
• AFTER clause specifies the INSERT, UPDATE or DELETE event which will fire the trigger. The AFTER clause specifies that the trigger fires only after SQL Server successfully completes the execution of the action that fired it. All other actions and constraints should be successfully executed before the trigger is fired.
• INSTEAD OF clause is used to skip an INSERT, UPDATE or DELETE statement to a table and instead, executes other statements defined in the trigger. So, the actual INSERT, UPDATE or DELETE statement does not happen at all. INSTEAD OF clause cannot be used on DDL triggers.
• [NOT FOR REPLICATION] clause is specified to instruct the SQL Server not to invoke the trigger when a replication agent modifies the table.
• sql_statements specifies the action to be executed when an event occurs.

DML triggers use two special temporary tables called inserted tables and deleted tables. SQL Server automatically creates and manages these tables. SQL Server uses these tables to find the state of a table before and after a data modification and take action based on that difference.

INSERTED Table	DELETED Table
Holds the new rows to be inserted during an INSERT or UPDATE event.	Holds copies of the affected rows during a DELETE or UPDATE event.
No records for the DELETE statements.	No records for the INSERT statements.

Let’s create a trigger that fires on INSERT, UPDATE and DELETE operation on the Employee table. For that, create a new table EmployeeLog to log all operation performed on the Employee table.

Example: Create Log Table

CREATE TABLE EmpLog ( LogID int IDENTITY(1,1) NOT NULL, EmpID int NOT NULL, Operation nvarchar(10) NOT NULL, UpdatedDate Datetime NOT NULL ) 

In the above table, LogID is the serial number with auto increment, UpdatedDate is the date on which the Employee table was updated. The Operation column stores the type of operation made to the table; either «INSERT», «UPDATE», or «DELETE».

FOR Triggers

The FOR triggers can be defined on tables or views. It fires only when all operations specified in the triggering SQL statement have initiated successfully. All referential cascade actions and constraint checks must also succeed before this trigger fires.

The following FOR trigger fires on the INSERT operation on the Employee table.

Example: FOR Trigger

CREATE TRIGGER dbo.trgEmployeeInsert ON dbo.Employee FOR INSERT AS INSERT INTO dbo.EmpLog(EmpID, Operation, UpdatedDate) SELECT EmployeeID ,'INSERT',GETDATE() FROM INSERTED; --virtual table INSERTED 

The above will create the trgEmployeeInsert trigger in the -> Triggers folder, as shown below.

Execute the select statements on Employee and EmpLog tables to see the existing records.

The following is EmpLog table.

Now, execute the following INSERT statement that will fire the trgEmployeeInsert trigger.

Example: INSERT Data

INSERT INTO Employee(FirstName ,LastName ,EMail ,Phone ,HireDate ,ManagerID ,Salary ,DepartmentID) VALUES('Manisha' ,'Dutt' ,'[email protected]' ,6799878453 ,'11/07/2015' ,5 ,50000 ,20) 

The above will insert a new row in the Employee table, as shown below.

The trgEmployeeInsert will be fired and insert a row in the EmpLog table, as shown below.

You can see that a new row is inserted in the EmpLog table for each INSERT statement for the Employee table.

Note: For any reason, if the FOR triggers fails then the INSERT will also fail and no rows will be inserted.

AFTER Triggers

The AFTER trigger fires only after the specified triggering SQL statement completed successfully. AFTER triggers cannot be defined on views.

For example, the following trigger will be fired after each UPDATE statement on the Employee table.

Example: AFTER Trigger

CREATE TRIGGER dbo.trgEmployeeUpdate ON dbo.Employee AFTER UPDATE AS INSERT INTO dbo.EmpLog(EmpID, Operation, UpdatedDate) SELECT EmployeeID,'UPDATE', GETDATE() FROM DELETED; 

To test this trigger, execute the following UPDATE statement.

Example: INSERT Data

UPDATE Employee SET salary = 55000 WHERE EmployeeID = 2; 

Now, select rows from the EmpLog table. The trgEmployeeUpdate trigger should have inserted a new row in the EmpLog table, as shown below.

INSTEAD OF Triggers

An INSTEAD OF trigger allows you to override the INSERT, UPDATE, or DELETE operations on a table or view. The actual DML operations do not occur at all.

The INSTEAD OF DELETE trigger executes instead of the actual delete event on a table or view. In the Instead Of delete trigger example below, when a delete command is issued on the Employee table, a new row is created in the EmpLog table storing the operation as ‘Delete’, but the row doesn’t get deleted.

Example: INSTEAD OF Trigger

CREATE TRIGGER dbo.trgInsteadOfDelete ON dbo.Employee INSTEAD OF DELETE AS INSERT INTO dbo.EmpLog(EmpID, Operation, UpdatedDate) SELECT EmployeeID,'DELETE', GETDATE() FROM DELETED; 

Now, execute the following delete statement to test the above trigger.

Example: INSTEAD OF Trigger

DELETE FROM Employee WHERE EmployeeID = 16; 

The above statement will fire the trgInsteadOfDelete trigger which will insert a new row in the EmpLog table instead of deleting a row in the Employee table.

The INSTEAD OF DELETE trigger works in the same manner for bulk deletes also. When you run an SQL statement deleting multiple rows, the rows will not be deleted, but equal number of rows gets inserted in the EmpLog table.

Multiple Triggers

In SQL Server, multiple triggers can be created on a table for the same event. There is no defined order of execution for these triggers.

The order of the triggers can be set to First or Last using the stored procedure sp_settriggerorder. There can be only one first or last trigger for a table. All triggers that are fired between the first defined trigger and the last defined trigger are not fired in any guaranteed order. Consider a scenario where there are four or more triggers. After the first defined trigger is fired, there is no defined order of firing for the other triggers until finally, the Last defined trigger is fired.

sp_settriggerorder [ @triggername = ] 'triggername', [ @order = ] 'value', [ @stmttype = ] 'statement_type', [ @namespace = < 'DATABASE' | 'SERVER' | NULL >] 

• Triggername is the name of the trigger to be ordered
• @order = Order of the trigger. First, Last or None
• @stmttype = Statement type. INSERT UPDATE, DELETE, LOGON or any TSQL statement event listed in DDL events.
• @namespace specifies whether the DDL trigger was created on Database or Server.

Assume that you have multiple triggers that fire on the update statement on the Employee table. The following example specifies that trigger trgEmployeeUpdate be the first trigger to fire after an UPDATE operation occurs on the Employee table.

Example: Set Trigger Order

sp_settriggerorder @triggername= 'dbo.trgEmployeeUpdate', @order='First', @stmttype = 'UPDATE'; 

Create a DML Trigger using SSMS

Step 1: Open SSMS and log in to the database server. In Object Explorer, expand the database instance and select the database where you want to create a trigger.

Step 2: Expand the table where you want to create a trigger. Right-click on the Triggers folder and select New Trigger. The CREATE TRIGGER syntax for a new trigger will open in Query Editor.

Step 3: In the Query menu, click Specify Values for Template Parameters.

In the dialog box, specify the trigger name, date created, schema name, author of the trigger, and fill the other parameters. Click Ok.

Step 4: In the Query Editor, enter the SQL statements for the trigger in the commented section – insert statements for trigger here.

Step 5: You can verify the syntax by clicking on Parse under the Query menu.

Step 6: Click Execute to create the trigger.

Step 7: Refresh the table. The new trigger will be created under the Triggers folder of the table.

Thus, you can create triggers in SSMS.

How to View Triggers in SQL Server Management Studio

SQL Server has many types of triggers that can be created, but finding them using SQL Server Management Studio (SSMS) may not be easy if you are not sure where to look. In this tip we look at how to use SSMS to find and manage both DML triggers and DDL triggers.

Solution

SQL Server Management Studio is a graphical interface that allows the user to configure, manage and also edit scripts. Although the GUI is easy to use, we must recognize that knowing where to find objects is not always that easy and this is true with triggers because there are different types of triggers and they are not all in the same place in SSMS.

Triggers in SQL Server Management Studio

There are two types of triggers that can be created:

• DML (Data Manipulation Language) triggers and
• DDL (Data Definition Language) triggers.

The DML triggers are those that fire when a SQL statement tries to change the data of a given table or view. These can be created on tables and views.

On the other hand, DDL triggers fire when a SQL statement tries to change the physical structure of the database (i.e. create, alter or delete database objects). Additionally, there are DDL triggers that fire when there are changes to server objects (i.e. create, alter or drop linked servers or databases).

In the next sections I will show you how to access to each type of trigger within SSMS.

Table Scoped SQL Server DML Triggers

If we need to see the triggers on a specific table, we can use SSMS in the following way. First expand Databases, then expand the database that contains the table. Next expand the Tables folder and find the table you are looking for then expand the table and expand Triggers to see a list of triggers for the table as shown below.

Now that we found the trigger, right click on the trigger to see a menu of things you can do from SSMS. If you click on Script Trigger as you can see the different scripts you can create from SSMS as shown below.

That context menu gives you the chance to modify, script, view dependencies, enable or disable and delete the trigger. The modify item opens a new script window in the SSMS editor with the trigger’s source code scripted as an ALTER TRIGGER statement.

View Scoped SQL Server DML Triggers

Additionally, SSMS can be used to look at triggers that are scoped to views. Follow the same steps as if you were looking at a table scoped trigger, but instead of expanding the Table folder expand the Views folder. The next screen capture shows those steps in order.

Also, if you right click on the trigger you will see a menu similar to the trigger scoped tables.

SQL Server Database Scoped DDL Triggers

If you want to view these triggers go to the Programmability folder within the database and look for a subfolder named Database Triggers as shown below.

You will notice on the next screen capture that if you right click on a database trigger the context menu is slightly different to the one of table and view scoped triggers. There isn’t a Modify item, but still we have the chance to script the trigger as DROP and CREATE statements. Also, like on the table and view scoped triggers, we have the options to view the trigger dependencies, enable or disable and delete the trigger.

Server Scoped SQL Server DDL Triggers

In case we want to see DDL triggers that affect the entire server we need to look at the Server Objects folder in the server tree view. You will see a child branch Triggers. Expand the Triggers folder to see a list of server scoped DDL triggers.

When we right click on the trigger name, we will see a menu with the same items as the database scoped triggers.

Next Steps

• This tip was written using SQL Server Management Studio v17.9. If you still using an older version of SSMS I suggest you read the following tip to see if it’s worth upgrading New Features in SQL Server Management Studio v17. Additionally take a look at this tip SQL Server Management Studio 17.x Important Features.
• If you don’t have SSMS installed take a look at this tip for a quick guide on How to Install SQL Server Management Studio on your Local Computer.
• If you know the trigger name you can use the object search feature of SSMS. You can learn more about this here Using Object Explorer Details and Object Search Feature of SSMS 2008.
• If you don’t know the trigger’s name you can use the scripts from this tip: Find All SQL Server Triggers to Quickly Enable or Disable.
• If you need to script triggers for any database you can take a look at the following tip Script triggers from any database in SQL Server.
• Stay tuned to the SQL Server Triggers tips category for more tips and tricks using triggers.
• For more tips related to SSMS you can browse the SQL Server Management Studio tips category.

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.