Руководство по SQLServer. Руководство по ms sql server

И затем после слова AS идет набор выражений SQL, которые собственно и составляют тело триггера.
Создадим триггер. Допустим, у нас есть база данных productsdb со следующим определением:
CREATE DATABASE productdb;
GO
USE productdb;
CREATE TABLE Products
(
Id INT IDENTITY PRIMARY KEY,

MS SQL Server и T-SQL | Определение триггеров
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. Но какое значение должен иметь Id такой товар?
Дело в том, что при добавлении или изменении данные сохраняются в промежуточную таблицу inserted. Она создается автоматически. И из нее мы можем получить данные о добавленных/
измененных товарах.
И после добавления товара в таблицу Products в реальности товар будет иметь несколько большую цену, чем та, которая была определена при добавлении:
Удаление триггера
Для удаления триггера необходимо применить команду DROP TRIGGER:
DROP TRIGGER Products_INSERT_UPDATE

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

MS SQL Server и T-SQL | Триггеры для операций INSERT, UPDATE, DELETE
Триггеры для операций 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 INSERT
AS
INSERT INTO History (ProductId, Operation)
SELECT Id, 'Добавлен товар ' + ProductName + ' фирма ' + Manufacturer
FROM INSERTED
Этот триггер будет добавлять в таблицу History данные о добавлении товара, которые берутся из виртуальной таблицы INSERTED.
Выполним добавление данных в Products и получим данные из таблицы History:

MS SQL Server и T-SQL | Триггеры для операций INSERT, UPDATE, DELETE
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

MS SQL Server и T-SQL | Триггеры для операций INSERT, UPDATE, DELETE
Изменение данных
Триггер обновления данных срабатывает при выполнении операции 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
И при обновлении данных сработает данный триггер:

MS SQL Server и T-SQL | Триггер INSTEAD OF
SELECT * FROM Products;
Триггер INSTEAD OF
Триггер INSTEAD OF срабатывает вместо операции с данными. Он определяется в принципе также,
как триггер AFTER, за тем исключением, что он может определяться только для одной операции INSERT, DELETE или UPDATE. И также он может применяться как для таблиц, так и для представлений (триггер AFTER применяется только для таблиц).
Например, создадим следующие базу данных и таблицу:
CREATE DATABASE prods;
GO
USE prods;
CREATE TABLE Products
(
Id INT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(30) NOT NULL,
Manufacturer NVARCHAR(20) NOT NULL,
Price MONEY NOT NULL,
IsDeleted BIT NULL
);
Здесь таблица содержит столбец IsDeleted, который указывает, удалена ли запись. То есть вместо жесткого удаления полностью из базы данных мы хотим выполнить мягкое удаление, при котором запись остается в базе данных.
Определим триггер для удаления записи:
USE prods
GO
CREATE TRIGGER products_delete
ON Products
INSTEAD OF DELETE
AS
UPDATE Products
SET IsDeleted = 1
WHERE ID =(SELECT Id FROM deleted)
Добавим некоторые данные в таблицу и выполним удаление из нее:
USE prods;
INSERT INTO Products(ProductName, Manufacturer, Price)
VALUES ('iPhone X', 'Apple', 79000),
('Pixel 2', 'Google', 60000);
DELETE FROM Products
WHERE ProductName='Pixel 2';

MS SQL Server и T-SQL | Триггер INSTEAD OF
Таким образом, удаляемые записи на самом деле не будут удаляться, просто у них будет устанавливаться значение для столбца IsDeleted:

1   2   3   4   5   6   7   8   9   10