Как создать триггер

SQL-Ex blog

Триггеры являются одними из наименее понимаемыми вещами для новичков в SQL Server. Возможно, это объясняется тем фактом, что они допускают почти всю функциональность, которой обладают хранимые процедуры, что вызывает затруднения неопытных разработчиков при выборе между хранимыми процедурами и триггерами.

Что такое триггер SQL Server?

Триггер SQL Server — это часть процедурного кода, как и хранимая процедура, но который выполняется только при наступлении определенного события. Имеются различные типы событий, которые могут вызвать срабатывание триггера. Например, вставка строки в таблицу, изменение структуры таблицы или авторизация пользователя в экземпляре SQL Server.

• Пользователь не может выполнить триггеры вручную.
• Триггеры не принимают параметры.
• Вы не можете зафиксировать или откатить транзакцию внутри триггера.

Классы триггеров SQL Server

• Триггеры DDL (язык определения данных). Этот класс триггеров срабатывает на событиях, которые изменяют структуру (типа создания, модификации или удаления таблицы), или на определенных событиях, относящихся к серверу, например, событиях изменения безопасности или обновления статистики.
• Триггеры DML (язык модификации данных). Это наиболее часто используемый класс событий. Это события, которые вызывают срабатывание триггера при модификации данных; это может быть оператор insert, update или delete, применяемый к таблице или представлению.

• FOR или AFTER [INSERT, UPDATE, DELETE]: Эти типы триггеров выполняются после завершения выполнения оператора, вызвавшего срабатывание триггера (insert, update или delete).
• INSTEAD OF [INSERT, UPDATE, DELETE]: в отличие от типа FOR (AFTER) триггеры INSTEAD OF выполняются вместо оператора, вызвавшего срабатывание триггера. Другими словами, этот тип триггера заменяет вызвавший его оператор. Это очень полезно в случаях, когда вам нужно обеспечить ссылочную целостность между базами данных.

Почему триггеры важны?

Одной из фундаментальных характеристик реляционных баз данных является согласованность данных. Это означает, что информация, хранимая в базе данных должна быть согласована все время для каждой сессии и каждой транзакции. Способ, которым ядро реляционной системы баз данных типа SQL Server обеспечивает согласованность, заключается во введении ограничений, как-то: первичные и внешние ключи. Но иногда этого оказывается недостаточно.

В SQL Server нет возможности обеспечить ссылочную целостность между двумя таблицами посредством внешних ключей, если эти таблицы находятся в разных базах данных или на разных серверах. В таких случаях можно применить только один способ — использование триггеров.

Как узнать, какие строки были обновлены, вставлены или удалены, используя триггер DML в SQL Server?

В случае триггеров DML при их выполнении создаются две виртуальных таблицы, которые содержат данные, на которые влияет выполнение триггера. Эти таблицы называются inserted и deleted, и они имеют ту же структуру, что и структура базовой таблицы.

Следует иметь в виду, что таблицы inserted и deleted не всегда доступны вместе (т.е. вы можете иметь таблицу inserted, но не иметь таблицы deleted, и наоборот). Вы можете найти больше информации об этих таблицах в следующей статье.

Синтаксис триггера DML в SQL Server

Вот базовый синтаксис команды создания триггера CREATE TRIGGER.

CREATE TRIGGER trigger_name
ON < Table name or view name >
[ WITH ]
< FOR | AFTER | INSTEAD OF >

В следующей таблице описывается каждый из аргументов синтаксиса CREATE TRIGGER.

Сценарии использования триггеров в SQL Server

Имеется два понятных сценария, когда триггеры являются лучшим выбором: аудит и соблюдение бизнес-правил. Используя триггер, вы можете отслеживать изменения на заданной таблице путем записи в журнал информации о том, кто выполнил изменение, и какие изменения были сделаны.

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

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

Простой триггер SQL Server DML

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

Сначала мы должны создать таблицу Employees.

CREATE TABLE Employees 
( 
EmployeeID integer NOT NULL IDENTITY(1, 1) , 
EmployeeName VARCHAR(50) , 
EmployeeAddress VARCHAR(50) , 
MonthSalary NUMERIC(10, 2) 
PRIMARY KEY CLUSTERED (EmployeeID) 
) 
GO

Затем мы должны создать таблицу EmployeesAudit для хранения записей аудита. Эта таблица имеет ту же структуру, что и таблица Employees, плюс включает столбец AuditId в качестве первичного ключа, ModifiedDate для хранения даты модификации, ModifiedBy для того, чтобы мы могли узнать, кто модифицировал таблицу Employees, и, наконец, Operation, где будет указываться операция DML, которая сгенерировала запись аудита, одной из трех букв ( I для вставки, U для обновления и D для удаления).

CREATE TABLE EmployeesAudit 
( 
AuditID INTEGER NOT NULL IDENTITY(1, 1) , 
EmployeeID INTEGER , 
EmployeeName VARCHAR(50) , 
EmployeeAddress VARCHAR(50) , 
MonthSalary NUMERIC(10, 2) , 
ModifiedBy VARCHAR(128) , 
ModifiedDate DATETIME , 
Operation CHAR(1) 
PRIMARY KEY CLUSTERED ( AuditID ) 
) 
GO

Чтобы протестировать триггер, нам потребуется добавить некоторые данные в таблицу Employees.

INSERT INTO dbo.Employees 
( EmployeeName , 
EmployeeAddress , 
MonthSalary 
) 
SELECT 'Mark Smith', 'Ocean Dr 1234', 10000 
UNION ALL 
SELECT 'Joe Wright', 'Evergreen 1234', 10000 
UNION ALL 
SELECT 'John Doe', 'International Dr 1234', 10000 
UNION ALL 
SELECT 'Peter Rodriguez', '74 Street 1234', 10000 
GO

Теперь, когда мы имеем все для тестирования, пора создать наш триггер Посмотрите код ниже.

CREATE TRIGGER TR_Audit_Employees ON dbo.Employees 
FOR INSERT, UPDATE, DELETE 
AS 
DECLARE @login_name VARCHAR(128) 
SELECT @login_name = login_name 
FROM sys.dm_exec_sessions 
WHERE session_id = @@SPID 
IF EXISTS ( SELECT 0 FROM Deleted ) 
BEGIN 
IF EXISTS ( SELECT 0 FROM Inserted ) 
BEGIN 
INSERT INTO dbo.EmployeesAudit 
( EmployeeID , 
EmployeeName , 
EmployeeAddress , 
MonthSalary , 
ModifiedBy , 
ModifiedDate , 
Operation 
) 
SELECT D.EmployeeID , 
D.EmployeeName , 
D.EmployeeAddress , 
D.MonthSalary , 
@login_name , 
GETDATE() , 
'U' 
FROM Deleted D 
END 
ELSE 
BEGIN 
INSERT INTO dbo.EmployeesAudit 
( EmployeeID , 
EmployeeName , 
EmployeeAddress , 
MonthSalary , 
ModifiedBy , 
ModifiedDate , 
Operation 
) 
SELECT D.EmployeeID , 
D.EmployeeName , 
D.EmployeeAddress , 
D.MonthSalary , 
@login_name , 
GETDATE() , 
'D' 
FROM Deleted D 
END 
END 
ELSE 
BEGIN 
INSERT INTO dbo.EmployeesAudit 
( EmployeeID , 
EmployeeName , 
EmployeeAddress , 
MonthSalary , 
ModifiedBy , 
ModifiedDate , 
Operation 
) 
SELECT I.EmployeeID , 
I.EmployeeName , 
I.EmployeeAddress , 
I.MonthSalary , 
@login_name , 
GETDATE() , 
'I' 
FROM Inserted I 
END 
GO

В начале код содержит получение пользователя, который модифицирует таблицу Employees, обращаясь к динамическому административному представлению sys.dm_exec_sessions для получения сессии по текущему
ИД сессии (SPID). Потом триггер вставляет одну запись в таблицу EmployeesAudit для каждой вставленной, обновленной или удаленной записи в таблице Employees, а так же текущее время и операцию DML, которая вызвала срабатывание триггера.

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

Первый из этих запросов выполняет обновление.

BEGIN TRANSACTION 
SELECT * 
FROM dbo.Employees 
WHERE EmployeeID = 1 
UPDATE Employees 
SET EmployeeName = 'zzz' 
WHERE EmployeeID = 1 
SELECT * 
FROM dbo.Employees 
WHERE EmployeeID = 1 
SELECT * 
FROM dbo.EmployeesAudit 
ROLLBACK TRANSACTION

На следующем скриншоте вы увидите обновленную запись в таблице Employees и новую запись таблице EmployeesAudit, которая отслеживает операцию DML в таблице Employees.

Второй запрос вставляет две строки в таблицу Employees.

BEGIN TRANSACTION 
INSERT INTO dbo.Employees 
( EmployeeName , 
EmployeeAddress , 
MonthSalary 
) 
SELECT 'zz' , 
'dsda' , 
10000 
UNION ALL 
SELECT 'Markus Rubius' , 
'dsda' , 
6000 
SELECT * 
FROM dbo.Employees 
SELECT * 
FROM dbo.EmployeesAudit 
ROLLBACK TRANSACTION

На скриншоте ниже вы можете увдеть две вставленные записи в таблице Employees и соответствующие записи аудита в таблице EmployeesAudit.

Наконец, третий запрос — оператор удаления из таблицы Employees.

BEGIN TRANSACTION 
SELECT * 
FROM dbo.Employees 
WHERE EmployeeID = 1 
DELETE FROM dbo.Employees 
WHERE EmployeeID = 1 
SELECT * 
FROM dbo.EmployeesAudit 
SELECT * 
FROM dbo.Employees 
WHERE EmployeeID = 1 
ROLLBACK TRANSACTION

На скриншоте видно удаление строки из таблицы Employees и соответствующую запись аудита в таблице EmployeesAudit.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Триггеры

Триггеры представляют специальный тип хранимой процедуры, которая вызывается автоматически при выполнении определенного действия над таблицей или представлением, в частности, при добавлении, изменении или удалении данных, то есть при выполнении команд 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

7. Создание триггеров в PostgreSql

Триггер определяет операцию, которая должна выполняться при наступлении некоторого события в базе данных. Триггеры срабатывают при выполнении с таблицей команды SQL INSERT , UPDATE или DELETE .
В PostgreSQL триггеры создаются на основе существующих функции, т.е. сначала командой CREATE FUNCTION определяется триггерная функция, затем на ее основе командой CREATE TRIGGER определяется собственно триггер.

Синтаксис определения триггера

CREATE TRIGGER триггер
BEFORE | AFTER > < событие [ OR событие ] > ON таблица
FOR EACH < ROW | STATEMENT >
EXECUTE PROCEDURE функция ( аргументы )

Ниже приводятся краткие описания компонентов этого определения.

• CREATE TRIGGER триггер. В аргументе триггер указывается произвольное имя создаваемого триггера. Имя может совпадать с именем триггера, уже существу ющего в базе данных при условии, что этот триггер установлен для другой таблицы. Кроме того, по аналогии с большинством других несистемных объек тов баз данных, имя триггера (в сочетании с таблицей, для которой он устанав ливается) должно быть уникальным лишь в контексте базы данных, в которой он создается
• < BEFORE | AFTER >. Ключевое слово BEFORE означает, что функция должна выпол няться перед попыткой выполнения операции, включая все встроенные про верки ограничений данных, реализуемые при выполнении команд INSERT и DELETE . Ключевое слово AFTER означает, что функция вызывается после завершения операции, приводящей в действие триггер.
• < событие [ OR событие . ] >. События SQL , поддерживаемые в PostgreSQL : INSERT , UPDATE или DELETE . При перечислении нескольких событий в качестве разделителя используется ключевое слово OR .
• ON таблица. Имя таблицы, модификация которой заданным событием приво дит к срабатыванию триггера.
• FOR EACH < ROW | STATEMENT >. Ключевое слово, следующее за конструкцией FOR EACH и определяющее количество вызовов функции при наступлении указанного события. Ключевое слово ROW означает, что функция вызывается для каждой модифицируемой записи. Если функция должна вызываться всего один раз для всей команды, используется ключевое слово STATEMENT .
• EXECUTE PROCEDURE функция ( аргументы ). Имя вызываемой функции с аргу ментами. На практике аргументы при вызове триггерных функций не используются.

Синтаксис определения триггерной функции

CREATE FUNCTION функция () RETURNS trigger AS ‘
DECLARE
объявления ;
BEGIN
команды ;
END ; ‘
LANGUAGE plpgsql ;

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

Имя
Тип
Описание

К отдельным полям записей NEW и OLD в триггерных процедурах обращаются следующим образом: NEW.names , OLD.rg.

Примеры создания триггеров

Пример 1. Триггер выполняется перед удалением записи из таблицы поставщиков s. Триггер проверяет наличие в таблице поставок spj записей, относящихся к удаляемому поставщику, и, если такие записи есть, удаляет их.

— Создание триггерной функции
CREATE FUNCTION trigger_s_before_del () RETURNS trigger AS ‘
BEGIN
if (select count(*) from spj a where trim(a.ns)=trim(OLD.ns))>0
then delete from spj where trim(spj.ns)=trim(OLD.ns);
end if;
return OLD;
END;
‘ LANGUAGE plpgsql;

— Создание триггера
CREATE TRIGGER tr_s_del_befor
BEFORE DELETE ON s FOR EACH ROW
EXECUTE PROCEDURE trigger_s_before_del();

—Проверка работы триггера
Delete from s where ns=’S2’;

Пример 2. Создание триггера-генератора для таблицы поставщиков s.

Триггер выполняется перед вставкой новой записи в таблицу поставщиков s. Триггер проверяет значения, которые должна содержать новая запись (record NEW) и может их изменить:

• eсли не указан номер поставщика – он генерируется по схеме – S+ уникальный номер из последовательности;
• eсли не указано имя поставщика – оно генерируется по схеме – Postawchik_ + уникальный номер из последовательности;
• eсли не указан город – ставится значение по умолчанию – “Novosibirsk” ;
• если не указан рейтинг или рейтинг

— Создание последовательности
CREATE SEQUENCE s_seq INCREMENT BY 1 START WITH 25;

— Создание триггерной функции
— в этой функции вызывается перегружаемая функция nvl, ее определение здесь
CREATE FUNCTION trigger_s_before_lns () RETURNS trigger AS ‘
BEGIN
NEW.ns=nvl(NEW.ns,’S’||trim(to_char(nextval(‘s_seq’),’99999′)));
NEW.names=nvl(NEW.names,’Postawchik_’||trim(to_char(currval(‘s_seq’),’99999′)));
NEW.town = nvl(NEW. town, ‘Novosibirsk’ );
if (nvl(NEW.rg,0) <=0) then
If NEW.town= ‘Novosibirsk’ then NEW.rg=10;
else NEW.rg=0;
end if;
end if;
return NEW;
END;
‘ LANGUAGE plpgsql;

— Создание триггера
CREATE TRIGGER s_bi
BEFORE INSERT ON s FOR EACH ROW
EXECUTE PROCEDURE trigger_s_before_lns ()

—Проверка работы триггера
insert into s values(null,null,null,null);
insert into s values(null,null,null,null);
insert into s values(null,null,null,null);
insert into s values(null,’Ivanov’,null,null);
insert into s values(null,’Sidorov’,50,null);
insert into s values(null,’Petrov’,null,’Moskva’);

Как сделать презентацию с триггерами и поделиться ей с учениками

В этой статье мы расскажем, как вставить триггеры в PowerPoint-презентацию, сделать с их помощью игру, загрузить ее в блог или отправить по email всему классу.

Триггер в PowerPoint — это инструмент, который позволяет создать «горячую клавишу» для запуска анимации, аудио- и видеоэффектов.

Один клик мышью — и выбранный объект (картинка, фигура, отдельное слово) приходит в движение на слайде. Исчезает, выезжает справа или слева, меняет цвет — действует по заданному сценарию. Например, вот так:

Как сделать триггер в презентации

Посмотрите нашу короткую видеоинструкцию:

Рассмотрим на конкретном примере, как сделать триггер в PowerPoint или в конструкторе онлайн-курсов iSpring Suite. Мне нужно, чтобы при нажатии на портрет Пушкина всплывала подсказка — окошко с фамилией и инициалами поэта. А при нажатии изображения Лермонтова появлялся текст «М.Ю. Лермонтов». Итак, цель задана. Рассмотрим создание триггеров в презентации пошагово.

1. Создаем пустой слайд

Запускаем PowerPoint. В нашем случае это версия 13-го года. Перед нами первый слайд с графами Заголовок и Подзаголовок . Для удобства сделаем его пустым. Наведите курсор на свободное место на слайде, кликните по нему один раз правой кнопкой мыши, выберите пункт Макет и Пустой слайд .

2. Добавляем изображения для анимации

Для этого на вкладке Вставка в группе Изображения нажмите кнопку Рисунки .

В открывшемся диалоговом окне найдите нужное изображение, выберите его и нажмите кнопку Вставить . СОВЕТ: если вы хотите одновременно вставить несколько рисунков, выберите необходимые файлы, удерживая клавишу CTRL.

3. Вставляем подсказки

Для этого выбираем меню Вставка — Фигуры — Выноска .

После вставляем в фигуру текст с подсказкой. Выполняем данные действия для двух фото. Должно получиться так:

4. Создаем анимацию

Для этого выделяем фигуру с текстом, выбираем меню Анимация — Добавить анимацию — Появление .

5. Настраиваем триггер

Для этого выделяем подсказку, далее Анимация — Область анимации — Триггер — По щелчку — Рисунок 3 .

Для второй подсказки применяем ту же схему. Только в конце выбираем Рисунок 4 . В итоге должно получиться следующее:

• игры «Лабиринт»;
• игры «Интеллектуальный тир»;
• игры «Времена года»;
• игры «Силуэты»;
• игры «Найди 10 отличий»;
• викторины с «эффектом наведения мыши»;
• викторины «Столицы мира»;
• тренажера по правилам дорожного движения «Светофор».

Как поделиться интерактивной презентацией с учениками

Вы создали презентацию с триггерами, курс или тест и хотите поделиться ими с учениками. Можно провести урок в компьютерном классе или показать презентацию через проектор. А можно расширить «географию». Например, залить материал в интернет и скинуть ссылку школьникам соцсетях или по почте. Тогда дети смогут ознакомиться с презентацией в удобное время.

Простой способ поделиться презентацией в интернете — загрузить материалы в «облачный» сервис iSpring Cloud. Программа предлагает четыре удобных варианта распространения презентаций, которые можно будет открыть на любом компьютере:

1. Короткая ссылка — вы можете просто скопировать ссылку и отправить ее ученикам по смс, через Skype или любой другой сервис для обмена сообщениями. Вставьте ссылку в рекламный баннер или промо материалы.
2. Email — укажите адрес электронной почты получателя, и из iSpring Cloud придет приглашение к просмотру материала.
3. Социальные сети — делитесь презентацией с учениками в социальных сетях (Facebook, Twitter, LinkedIn, Вконтакте).
4. Embed-код — скопируйте код и разместите презентацию на сайте или в блоге.

В iSpring Cloud можно не только делиться курсами, но и смотреть по ним статистику: кто открывал, сколько слайдов просмотрено, сколько в среднем тратят времени на изучение.

Никто не испортит вашу презентацию. В iSpring Cloud она защищена от редактирования. Сохранить авторские права помогут настройки приватности: установите пароль, и доступ к материалам смогут получить только те пользователи, кому вы доверяете.

Читайте подробную инструкцию о том, как поделиться презентацией с помощью iSpring Cloud.

Если вам понравилась статья, дайте нам знать — нажмите кнопку Поделиться .

А если у вас есть идеи для полезных статей на тему электронного обучения — напишите нам в комментариях, и мы будем рады поработать над новым материалом.