Триггеры в Microsoft SQL Server

Триггеры в Microsoft SQL Server

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

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

CREATE TRIGGER имя_триггера

ON {имя_таблицы | имя_представления}

{AFTER | INSTEAD OF} [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 = (SELECT Id FROM inserted)

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

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

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

При добавлении или изменении данные сохраняются в промежуточную таблицу i nserted . Она создается автоматически. И из нее мы можем получить данные о добавленных/измененных товарах .

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

Для удаления триггера необходимо применить команду  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

Триггеры для операций INSERT, UPDATE, DELETE

Пусть дана база данных productsdb

• CREATE DATABASE productsdb;
• GO
• USE productsdb;
• 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
• );
• CREATE TABLE History
• (
•      Id INT IDENTITY PRIMARY KEY,
•      ProductId INT NOT NULL,
•      Operation NVARCHAR(200) NOT NULL,
•      CreateAt DATETIME NOT NULL DEFAULT GETDATE(),
• );

Здесь определены две таблиц: Products - для хранения товаров и History - для хранения истории операций с товарами.

Добавление

При добавлении данных (при выполнении команды  INSERT ) в триггере можно получить добавленные данные из виртуальной таблицы  INSERTED .

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

• USE productsdb
• GO
• CREATE TRIGGER Products_INSERT
• ON Products
• AFTER INSER

6. AS

7. INSERT INTO History (ProductId, Operation)

8. SELECT Id, 'Добавлен товар ' + ProductName + '   фирма ' + Manufacturer

9. FROM INSERTED

Этот триггер будет добавлять в таблицу History данные о добавлении товара, которые берутся из виртуальной таблицы INSERTED.

Выполним добавление данных в Products и получим данные из таблицы History:

• USE productsdb;
• INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price)
• VALUES('iPhone X', 'Apple', 2, 79900)
•  
• SELECT * FROM History

Удаление данных

При удалении все удаленные данные помещаются в

виртуальную таблицу  DELETED :

• USE productsdb
• GO
• CREATE TRIGGER Products_DELETE
• ON Products
• AFTER DELETE
• AS
• INSERT INTO History (ProductId, Operation)
• SELECT Id, 'Удален товар ' + ProductName + '   фирма ' + Manufacturer
• FROM DELETED

Здесь, как и в случае с предыдущим триггером, помещаем

информацию об удаленных товарах в таблицу History.

Выполним команду на удаление:

• USE productsdb;
• DELETE FROM Products
• WHERE Id=2
• SELECT * FROM History

Изменение данных

Триггер обновления данных срабатывает при выполнени

и операции UPDATE. И в таком триггере мы можем

использовать две виртуальных таблицы.

Таблица INSERTED хранит значения строк после

обновления, а таблица DELETED хранит те же строки,

но до обновления.

Изменение данных

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

Создадим триггер обновления:

• USE productsdb
• GO
• CREATE TRIGGER Products_UPDATE
• ON Products
• AFTER UPDATE
• AS
• INSERT INTO History (ProductId, Operation)
• SELECT Id, 'Обновлен товар ' + ProductName + '   фирма ' + Manufacturer
• FROM INSERTED

И при обновлении данных сработает данный триггер:

Триггер INSTEAD OF

Триггер INSTEAD OF срабатывает вместо операции с данными. Он определяется также, как триггер AFTER, за исключением, что он может определяться только для одной операции - INSERT, DELETE или UPDATE . И также он может применяться как для таблиц, так и для представлений (триггер AFTER применяется только для таблиц).

Например, создадим следующие базу данных и таблицу:

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

Определим триггер для удаления записи:

Добавим некоторые данные в таблицу и выполним удаление из нее:

Таким образом, удаляемые записи на самом деле не будут удаляться, просто у них будет устанавливаться значение для столбца IsDeleted :