Страницы

Поиск по вопросам

понедельник, 24 сентября 2018 г.

Определение триггеров в SQL

Триггеры представляют специальный тип хранимой процедуры, которая вызывается автоматически при выполнении определенного действия над таблицей или представлением, в частности, при добавлении, изменении или удалении данных, то есть при выполнении команд INSERT, UPDATE, DELETE.
Формальное определение триггера:
1
2
3
4
CREATE TRIGGER имя_триггера
ON {имя_таблицы | имя_представления}
{AFTER | INSTEAD OF} [INSERT | UPDATE | DELETE]
AS выражения_sql
Для создания триггера применяется выражение CREATE TRIGGER, после которого идет имя триггера. Как правило, имя триггера отражает тип операций и имя таблицы, над которой производится операция.
Каждый триггер ассоциируется с определенной таблицей или представлением, имя которых указывается после слова ON.
Затем устанавливается тип триггера. Мы можем использовать один из двух типов:
  • AFTER: выполняется после выполнения действия. Определяется только для таблиц.
  • INSTEAD OF: выполняется вместо действия (то есть по сути действие - добавление, изменение или удаление - вообще не выполняется). Определяется для таблиц и представлений
После типа триггера идет указание операции, для которой определяется триггер: INSERTUPDATE или DELETE.
Для триггера AFTER можно применять сразу для нескольких действий, например, UPDATE и INSERT. В этом случае операции указываются через запятую. Для триггера INSTEAD OF можно определить только одно действие.
И затем после слова AS идет набор выражений SQL, которые собственно и составляют тело триггера.
Создадим триггер. Допустим, у нас есть база данных productsdb со следующим определением:
1
2
3
4
5
6
7
8
9
10
11
12
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
);
Определим триггер, который будет срабатывать при добавлении и обновлении данных:
1
2
3
4
5
6
7
8
9
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. Но какое значение должен иметь Id такой товар? Дело в том, что при добавлении или изменении данные сохраняются в промежуточную таблицу inserted. Она создается автоматически. И из нее мы можем получить данные о добавленных/измененных товарах.
И после добавления товара в таблицу Products в реальности товар будет иметь несколько большую цену, чем та, которая была определена при добавлении:
Триггеры в MS SQL Server

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

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

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

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

Комментариев нет:

Отправить комментарий