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

Триггеры

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

Формальное определение триггера:

CREATE TRIGGER имя_триггера ON  [INSERT | UPDATE | DELETE] AS выражения_sql

Для создания триггера применяется выражение CREATE TRIGGER , после которого идет имя триггера. Как правило, имя триггера отражает тип операций и имя таблицы, над которой производится операция.

Каждый триггер ассоциируется с определенной таблицей или представлением, имя которых указывается после слова ON .

Затем устанавливается тип триггера. Мы можем использовать один из двух типов:

• AFTER : выполняется после выполнения действия. Определяется только для таблиц.
• INSTEAD OF : выполняется вместо действия (то есть по сути действие — добавление, изменение или удаление — вообще не выполняется). Определяется для таблиц и представлений

После типа триггера идет указание операции, для которой определяется триггер: INSERT , UPDATE или DELETE .

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

И затем после слова AS идет набор выражений SQL, которые собственно и составляют тело триггера.

Создадим триггер. Допустим, у нас есть база данных productsdb со следующим определением:

CREATE DATABASE productdb; GO USE productdb; CREATE TABLE Products ( Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL );

Определим триггер, который будет срабатывать при добавлении и обновлении данных:

USE productdb; GO CREATE TRIGGER Products_INSERT_UPDATE ON Products AFTER INSERT, UPDATE AS UPDATE Products SET Price = Price + Price * 0.38 WHERE Id FROM inserted)

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

Таким образом, триггер будет срабатывать при любой операции INSERT или UPDATE над таблицей Products. Сам триггер будет изменять цену товара, а для получения того товара, который был добавлен или изменен, находим этот товар по Id. Но какое значение должен иметь Id такой товар? Дело в том, что при добавлении или изменении данные сохраняются в промежуточную таблицу inserted. Она создается автоматически. И из нее мы можем получить данные о добавленных/измененных товарах.

И после добавления товара в таблицу Products в реальности товар будет иметь несколько большую цену, чем та, которая была определена при добавлении:

Удаление триггера

Для удаления триггера необходимо применить команду DROP TRIGGER :

DROP TRIGGER Products_INSERT_UPDATE

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

Бывает, что мы хотим приостановить действие триггера, но удалять его полностью не хотим. В этом случае его можно временно отключить с помощью команды DISABLE TRIGGER :

DISABLE TRIGGER Products_INSERT_UPDATE ON Products

А когда триггер понадобится, его можно включить с помощью команды ENABLE TRIGGER :

ENABLE TRIGGER Products_INSERT_UPDATE ON Products

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.

Triggers in SQL Server

DML triggers in SQL Server are fired when a DML event occurs. i.e. when data is inserted/ updated/deleted in the table by a user.

Creating triggers for a DML event

Let us create some sample tables and triggers in SQL Server.

CREATE TABLE Locations ( LocationID int , LocName varchar ( 100 ) )
CREATE TABLE LocationHist ( LocationID int , ModifiedDate DATETIME )

We can create a DML trigger for a specific event or multiple events. The triggers in SQL Server(DML) fire on events irrespective to the number of rows affected.

Below is the sample syntax for creating a DML trigger for update event.

CREATE TRIGGER TR_UPD_Locations ON Locations
FOR UPDATE
NOT FOR REPLICATION
INSERT INTO LocationHist
SELECT LocationID
FROM inserted

These triggers are created at the table level. Upon successful creation of trigger, we can see the triggers by navigating to Triggers folder at table level. Please refer to the below image.

Instead of triggers in SQL Server

These triggers are fired before the DML event and the actual data is not modified in the table.

For example, if we specify an instead of trigger for delete on a table, when delete statement is issued against the table, the instead of trigger is fired and the T-SQL block inside the triggers in SQL Server is executed but the actual delete does not happen.

T-SQL Syntax for creating an instead of trigger

CREATE TRIGGER TR_DEL_Locations ON Locations
INSTEAD OF DELETE
Select ‘Sample Instead of trigger’ as [ Message ]

• If there are multiple triggers along with instead of trigger on the table, the instead of trigger is fired first in the order
• INSTEAD of triggers can be created on views
• we can define only one instead of trigger per INSERT, UPDATE, or DELETE statement on a table or view

Enabling and disabling DML triggers on a table

Navigate to triggers folder at the table level, select the trigger, Right click on trigger and Click on Enable/Disable to Enable or disable the trigger using SSMS.

Disabling specific SQL Server trigger on a table using T-SQL.

DISABLE TRIGGER TR_UPD_Locations2 on Locations

Enabling specific trigger on the table using T-SQL.

ENABLE TRIGGER TR_UPD_Locations2 on Locations

To enable all triggers on a table, use below syntax.

ENABLE TRIGGER ALL ON Locations

To disable all triggers on a table, use below syntax. This statement is not supported if the table is part of merge replication.

DISABLE TRIGGER ALL ON Locations

Dropping a trigger on a table.

To drop a DML trigger on the table using SQL Server management studio, navigate to the Triggers folder under the table. Select the table you want to drop, Right click on the trigger and click on Delete. Click Ok.

T-SQL to drop a trigger on the table.

DROP TRIGGER TRL_UPD_Locations2

Dropping a table will drop all the SQL Server triggers on the table along with the table.

DDL Triggers

DDL triggers in SQL Server are fired on DDL events. i.e. against create, alter and drop statements, etc. These triggers are created at the database level or server level based on the type of DDL event.

These triggers are useful in the below cases.

• Prevent changes to the database schema
• Audit database schema changes
• To respond to a change in the database schema

Creating a DDL trigger

Below is the sample syntax for creating a DDL trigger for ALTER TABLE event on a database which records all the alter statements against the table. You can write your custom code to track or audit the schema changes using EVENTDATA().

CREATE TABLE TableSchemaChanges ( ChangeEvent xml , DateModified datetime )
CREATE TRIGGER TR_ALTERTABLE ON DATABASE
FOR ALTER_TABLE
INSERT INTO TableSchemaChanges
SELECT EVENTDATA ( ) , GETDATE ( )

You can specify an event group which consists of different DDL events. If we specify an event group while creating a DDL trigger, the trigger is fired when a DDL event in the group occurs.

For example, if we want to create a trigger for all DDL events at the database level, we can just specify the DDL_DATABASE_LEVEL_EVENTS event group as shown in the below image.

To view database level triggers, Login to the server using SQL Server management studio and navigate to the database. Expand the database and navigate to Programmability -> Database Triggers.

To view triggers at the server level, Login to Server using SSMS and navigate to Server Objects and then Triggers folder.

Enabling and disabling DDL triggers

Use below T-SQL syntax to disable or enable the DDL trigger at the database level.

Не могу создать триггеры

Я пытался сам сделать, он не смог, а чтобы все изучить нужно время, но у меня к сожалению нет, поэтому попросил у вас помощи, заранее спасибо !

Лучшие ответы ( 1 )
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

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

Не могу создать триггеры.
Использую Interbase 6.5, пытаюсь создать 3 триггера: SET TERM ^; CREATE TRIGGER STAFFInsert FOR.

Не могу создать триггеры. InterBase
Использую Interbase 6.5, пытаюсь создать 3 триггера: SET TERM ^ ; CREATE TRIGGER STAFFInsert.

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

1134 / 615 / 129
Регистрация: 13.02.2009
Сообщений: 3,543

Сообщение от Mger

1. Мне нужно создать такой триггер, если удалить данные из таблицы «техники», то автоматический удалить эту технику из таблицы «накладные»

триггер почему ? можно при создание таблицу «накладные» написать

Constraint FK_Name foreign key(id_texnik) references техники(id_техник) on delete cascade

Почитайте каскадные ограничения https://technet.microsoft.com/. 05%29.aspx
Регистрация: 24.04.2013
Сообщений: 40
Спасибо за ответ, но мне нужны именно триггеры!

1134 / 615 / 129
Регистрация: 13.02.2009
Сообщений: 3,543

Сообщение было отмечено Mger как решение

Решение

Сообщение от Mger

но мне нужны именно триггеры
Ну, тогда без constraint

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

create table Tab1(id int identity, name nvarchar(30) constraint pk_tab1 primary key(id) ); ------------------ create table tab2(id int identity, telefon nvarchar(30), Tab1_id int ); ------------------- insert into Tab1(name) values (N'Васия'), (N'Петия'), (N'Руслан') select * from Tab1 ------------------------------ insert into tab2(telefon, Tab1_id) values(N'571.4444444444', 1), (N'571.3333333333', 1), (N'571.5555555555', 1), (N'571.6456456546', 2), (N'571.6456456546', 2), (N'571.6456488888', 3), (N'571.6450000888', 3), (N'571.6450000888', 3) select * from tab2 ------------------------------- create trigger MyTR on Tab1 after delete as begin delete t2 from tab2 t2 inner join DELETED D on t2.Tab1_id=D.id end ------------ delete from Tab1 where id=1

Если с constraint-ом ! то on delete cascade и не нужно триггер

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

create table Tab1(id int identity, name nvarchar(30) constraint pk_tab1 primary key(id) ); ------------------ --drop table tab2 create table tab2(id int identity, telefon nvarchar(30), Tab1_id int constraint FK_tab2 foreign key(Tab1_id) references Tab1(id) on delete cascade ); ------------------- insert into Tab1(name) values (N'Васия'), (N'Петия'), (N'Руслан') select * from Tab1 ------------------------------ insert into tab2(telefon, Tab1_id) values(N'571.4444444444', 1), (N'571.3333333333', 1), (N'571.5555555555', 1), (N'571.6456456546', 2), (N'571.6456456546', 2), (N'571.6456488888', 3), (N'571.6450000888', 3), (N'571.6450000888', 3) select * from tab2 ---------------- delete from Tab1 where id=1