Триггеры в SQL
Здравствуйте, уважаемые читатели. Подходим к завершающей статье по основам SQL. В этой статье разберем такое понятие, как триггеры в SQL.
Общие сведения
Итак, разберем такую сущность SQL как триггеры. Также как представления и процедуры — триггеры в SQL создаются и хранятся отдельно до момента их удаления. Триггеры по своей сути представляют обработчики событий. Они выполняются при наступлении какого-либо простого действия в SQL. Такими действиями обычно являются: удаление, вставка и обновление данных. То есть, триггер — это по сути ловушка, которая срабатывает при определенном действии. Триггер позволяет автоматизировать некоторые расчетные рутинные действия. Примеры мы разберем дальше.
Создание триггеров в SQL
Напомню, что мы работаем в MySQL. Триггеры создаются также, как и хранимые процедуры в SQL. Либо во вкладке SQL с помощью кода, либо с помощью графического редактора во вкладке триггеры. Оператор для создания следующий:
CREATE TRIGGER name_trigger
- BEFORE INSERT
- BEFORE UPDATE
- BEFORE DELETE
- AFTER INSERT
- AFTER UPDATE
- AFTER DELETE
То есть триггер срабатывает либо до, дибо после вставки, обновления, удаления данных из БД в SQL.
Пример работы в SQL
Если вы не знакомы со структурой нашей БД, то советуем почитать предыдущие уроки.
Рассмотрим тестовую задачу, которая покажет возможности триггеров. Предположим, что в таблице orders нам нужно поменять цену (поле amt), а новое значение, которое мы введем, увеличить еще на 20%. Задача бывает полезна, когда нужно сделать наценку на товар.
Чтобы нам не высчитывать 20% вручную от новой цены — создадим триггер. Он автоматически будет увеличивать новую цену на 20%.
Вот код создания такого триггера:
DELIMITER // CREATE TRIGGER Before_Update_amt BEFORE UPDATE ON orders FOR EACH ROW BEGIN SET NEW.amt = NEW.amt * 1.2; END // DELIMITER ;
Заметьте, что название триггера (Before_Update_amt) лучше всего давать такое, чтобы было понятно при каком случае он срабатывает. Триггер срабатывает перед обновлением потому, что сначала мы должны узнать новое значение, а только потом его занести в поле.
Отметим также ключевого слово NEW — это то значение, которое должно было попасть в таблицу, но мы создали триггер и теперь это значение еще увеличивается на 20%.
Следующий момент — цикл FOR EACH ROW. Он необходим потому, что одновременно может изменяться не одно значение, а несколько строк. Вот, для каждой измененной строчки мы и увеличиваем значение на 20%.
Триггер на взаимодействие таблиц
Рассмотрим еще одну задачу: у нас есть продавец (в таблице salespeople), и его продажи отражены в таблицы orders. Представим теперь, что продавец увольняется и все его продажи тоже следует удалить. Если таких продаж много, то легче всего воспользоваться триггером.
DELIMITER // CREATE TRIGGER After_Delete_salespeople AFTER DELETE ON salespeople FOR EACH ROW BEGIN DELETE FROM orders WHERE orders.snum = OLD.snum; END // DELIMITER ;
Итак, после удаления продавца из salespeople берется его уникальный номер snum — он записан в коде как OLD.snum. Затем, по этому уникальному номеру удаляются все строчки из таблицы orders.
Можете проверить этот код, или его аналог. После удаления продавца триггер в SQL удаляет все записи из таблицы orders.
Ключевые слова OLD и NEW
На всякий случай, еще раз разберем употребление этих ключевых слов.
NEW — это значение, которое может появиться только после обновления или вставки данных. Оно содержит то значение, которое должно появиться в таблице. С помощью триггера можно изменить это новое значение, как было сделано в первом примере этой статьи.
OLD — это значение, которое уже было в таблице, либо перед удалением, либо перед обновлением. Обращаться к этому значению имеет смысл, чтобы получить id, и по этому id в другой таблице удалить связанные записи. Так было сделано во втором примере.
Заключение
На этом мы закончим. Небольшая статья, но все основные моменты триггеров в SQL были продемонстрированы. Если у вас остались вопросы, то оставляйте их в комментариях.
Поделиться ссылкой:
Получение сведений о триггерах DML
В этом разделе описывается, как получить сведения о триггерах DML в SQL Server с помощью SQL Server Management Studio или Transact-SQL. К таким сведениям относятся типы триггеров для таблицы, имя триггера, владелец триггера и дата создания или изменения триггера. Если триггер не был зашифрован во время создания, то можно получить его определение. По определению вы можете понять, каким образом триггер влияет на таблицу, для которой он определен. Кроме того, можно определить, какие объекты используются данным триггером. Эти сведения могут быть использованы для выявления объектов, которые воздействуют на триггер, если они изменяются или удаляются из базы данных.
В этом разделе
- Перед началом: Безопасность
- Для получения сведений о триггерах DML используется:Среда SQL Server Management StudioTransact-SQL
Перед началом
Безопасность
Разрешения
sys.sql.modules, sys.object, sys.triggers, sys.events, sys.trigger_events
Видимость метаданных в представлениях каталогов ограничивается защищаемыми объектами, которыми пользователь владеет или на которые ему были предоставлены разрешения. Дополнительные сведения см. в разделе Metadata Visibility Configuration.
OBJECT_DEFINITION, OBJECTPROPERTY, sp_helptext
Необходимо быть членом роли public. Определения пользовательских объектов видимы владельцу объекта и получателям любого из следующих разрешений: ALTER, CONTROL, TAKE OWNERSHIP и VIEW DEFINITION. Эти разрешения неявно предоставляются членам предопределенных ролей базы данных db_owner, db_ddladminи db_securityadmin .
sys.sql_expression_dependencies
Необходимо разрешение VIEW DEFINITION в базе данных и разрешение SELECT на представление sys.sql_expression_dependencies в базе данных. По умолчанию разрешение SELECT предоставляется только членам предопределенной роли базы данных db_owner . Если разрешения SELECT и VIEW DEFINITION предоставлены другому пользователю, он может просматривать все зависимости в базе данных.
Использование среды SQL Server Management Studio
Просмотр определения триггера DML
- В обозревателе объектов подключитесь к экземпляру ядра СУБД, а затем разверните этот экземпляр.
- Разверните нужную базу данных, разверните узел Таблицы, а затем разверните таблицу, содержащую триггер, для которого нужно просмотреть определение.
- Разверните узел Триггеры, щелкните правой кнопкой мыши нужный триггер и выберите команду Изменить. В окне запроса появится определение триггера DML.
Просмотр зависимостей триггера DML
- В обозревателе объектов подключитесь к экземпляру ядра СУБД, а затем разверните этот экземпляр.
- Разверните нужную базу данных, разверните узел Таблицы, а затем разверните таблицу, содержащую триггер и зависимости, которые нужно просмотреть.
- Разверните узел Триггеры, щелкните правой кнопкой мыши нужный триггер и выберите команду Просмотреть зависимости.
- В окне зависимостей объектов просмотрите объекты, зависящие от триггера DML, выберите объекты, зависящие от триггера DML. Объекты отображаются в области Зависимости . Чтобы просмотреть объекты, от которых зависит DML, выберите объекты, от которых имя> триггера DML. Объекты отображаются в области Зависимости . Разверните каждый узел, чтобы просмотреть все объекты.
- Чтобы получить сведения об объекте, который появляется в области Зависимости , щелкните его. В поле Выбранный объект сведения указываются в полях Имя, Типи Тип зависимости .
- Нажмите кнопку ОК , чтобы закрыть окно Зависимости объекта.
Использование Transact-SQL
Просмотр определения триггера DML
- Соединитесь с ядром СУБД .
- На панели «Стандартная» нажмите Создать запрос.
- Скопируйте и вставьте один из следующих примеров в окно запроса и нажмите кнопку Выполнить. В каждом примере показано, как можно просмотреть определение триггера iuPerson .
USE AdventureWorks2022; GO SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID(N'Person.iuPerson'); GO USE AdventureWorks2022; GO SELECT OBJECT_DEFINITION (OBJECT_ID(N'Person.iuPerson')) AS ObjectDefinition; GO USE AdventureWorks2022; GO EXEC sp_helptext 'Person.iuPerson' GO Просмотр зависимостей триггера DML
- Соединитесь с ядром СУБД .
- На панели «Стандартная» нажмите Создать запрос.
- Скопируйте и вставьте один из следующих примеров в окно запроса и нажмите кнопку Выполнить. В каждом примере показано, как можно просмотреть зависимости триггера iuPerson .
USE AdventureWorks2022; GO SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name, o.type_desc AS referencing_desciption, COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, referencing_class_desc, referenced_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name, is_caller_dependent, is_ambiguous FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id WHERE referencing_id = OBJECT_ID(N'Person.iuPerson'); GO Просмотр сведений о триггерах DML в базе данных
- Соединитесь с ядром СУБД .
- На панели «Стандартная» нажмите Создать запрос.
- Скопируйте и вставьте один из следующих примеров в окно запроса и нажмите кнопку Выполнить. В каждом примере показано, как можно просмотреть сведения о триггерах DML ( TR ) в базе данных.
USE AdventureWorks2022; GO SELECT name, parent_id, create_date, modify_date, is_instead_of_trigger FROM sys.triggers WHERE type = 'TR'; GO USE AdventureWorks2022; GO SELECT name, object_id, schema_id, parent_object_id, type_desc, create_date, modify_date, is_published FROM sys.objects WHERE type = 'TR'; GO USE AdventureWorks2022; GO SELECT OBJECTPROPERTY(OBJECT_ID(N'Person.iuPerson'), 'ExecIsInsteadOfTrigger'); GO Просмотр сведений о событиях, которые вызывают срабатывание триггера DML
- Соединитесь с ядром СУБД .
- На панели «Стандартная» нажмите Создать запрос.
- Скопируйте и вставьте один из следующих примеров в окно запроса и нажмите кнопку Выполнить. В каждом примере показано, как можно просмотреть события, которые вызывают срабатывание триггера iuPerson .
USE AdventureWorks2022; GO SELECT object_id, type, type_desc, is_trigger_event, event_group_type, event_group_type_desc FROM sys.events WHERE object_id = OBJECT_ID('Person.iuPerson'); GO USE AdventureWorks2022; GO SELECT object_id, type,is_first, is_last FROM sys.trigger_events WHERE object_id = OBJECT_ID('Person.iuPerson'); GO Как определить вызван ли триггер действиями процедуры
суть: нужно определить вызван ли триггер действиями хранимой процедуры, и если да то ничего с этим не делать. отключать триггер на время исполнения процедуры нельзя Вот код которым я пытаюсь поймать процедуру
CREATE TRIGGER dbo.TR_PRC_CATCHER ON dbo.TABLE FOR INSERT, DELETE, UPDATE AS BEGIN IF OBJECT_NAME(@@PROCID) not like '%procedure%' BEGIN RAISERROR ('Поймал!', 16, 1) WITH SETERROR; END END
правильный ли это метод, или можно решить данный вопрос лаконичнее upd: проверил данный метод, @@procid выдает object_id самого триггера а не процедуры, которой он был вызван. Данный вариант рабочим не является
Отслеживать
задан 16 окт 2020 в 15:44
23 4 4 бронзовых знака
Нужно чтобы триггер срабатывал только для конкретной процедуры? Или чтобы для процедуры (любой) срабатывал, а без процедуры не срабатывал?
19 окт 2020 в 10:41
Да, чтобы триггер пропускал действия определенной процедуры
20 окт 2020 в 21:03
Оформите пожалуйста ответ с примером, поставлю что вопрос решен, я так понимаю что речь о «context_info()»?
23 окт 2020 в 12:34
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
нужно определить вызван ли триггер действиями хранимой процедуры
В SqlServer нет встроенных средств, которые помогли бы понять, что триггер выполняется в контексте определённой процедуры. На Feedback.Azure размещён запрос (датирующийся аж 2006 годом) на добавление функции, которая давала бы информацию о стеке вызовов. Текущий статус запроса — UNPLANNED. Поэтому в данном случае придётся что-то изобретать и, видимо, без изменений в процедуре не обойтись.
Предположим, что [Table] — таблица, на которой будет триггер.
CREATE TABLE [Table] ( Id int IDENTITY(1, 1) NOT NULL, SomeDate datetime2(0) NOT NULL, CONSTRAINT PK_Table PRIMARY KEY (Id) ); Для примера я буду использовать AFTER INSERT триггер, а процедура, соответственно, будет делать INSERT в эту таблицу.
Нужно сообщить триггеру каким-либо способом, что его вызов происходит в контексте определённой процедуры.
Вариант #1 — Использование временной таблицы
Внутри процедуры создаём временную таблицу
CREATE OR ALTER PROCEDURE TestProc AS BEGIN SET NOCOUNT ON; CREATE TABLE #TestProc_Context(Dummy int); INSERT INTO [Table] (SomeDate) VALUES (SYSDATETIME()); END а в триггере проверяем её наличие и делаем (или не делаем) что-то, в зависимости от этого
CREATE OR ALTER TRIGGER Table_AfterInsert ON [Table] AFTER INSERT AS BEGIN SET NOCOUNT ON; IF OBJECT_ID('tempdb..#TestProc_Context') IS NOT NULL BEGIN PRINT 'In TestProc.'; RETURN; END ELSE BEGIN PRINT 'Not in TestProc'; RETURN; END; END INSERT INTO [Table] (SomeDate) VALUES (SYSDATETIME()); GO EXEC TestProc; GO Этот вариант видится мне наименее проблемным, т.к. после выхода из процедуры, что бы ни случилось, временная таблица будет уничтожена автоматически. Если нет высоких требований к throughput, то я бы порекомендовал остановиться на этом варианте. Если требования к производительности высокие, и в этом варианте ощутимо сказывается tempdb contention, то следующий вариант будет более предпочтительным.
Вариант #2 — Использование контекста сессии
В начале процедуры устанавливаем контекст, а перед выходом сбрасываем
CREATE OR ALTER PROCEDURE TestProc AS BEGIN SET NOCOUNT ON; EXEC sp_set_session_context @key = N'TestProc_Context', @value = 1; INSERT INTO [Table] (SomeDate) VALUES (SYSDATETIME()); EXEC sp_set_session_context @key = N'TestProc_Context', @value = NULL; END в триггере, аналогично, проверяем наличие контекста
CREATE OR ALTER TRIGGER Table_AfterInsert ON [Table] AFTER INSERT AS BEGIN SET NOCOUNT ON; IF SESSION_CONTEXT(N'TestProc_Context') IS NOT NULL BEGIN PRINT 'Not in TestProc'; RETURN; END ELSE BEGIN PRINT 'In TestProc.'; RETURN; END; END В первом варианте автоматическое уничтожение временной таблицы после выхода из процедуры так же автоматически сбрасывает контекст. В этом же варианте контекст сбрасывается вручную, поэтому очень важно, чтобы сброс произошёл. В реальной процедуре с более сложным кодом может потребоваться блок TRY. CATCH. , если возможно возникновение ошибок в промежутке между установкой и сбросом контекста.
Функционал sp_set_session_context и SESSION_CONTEXT доступен в SqlServer 2016 и более поздних версиях. В более ранних версиях, теоретически, можно воспользоваться SET CONTEXT_INFO и CONTEXT_INFO , но я бы этот вариант не рекомендовал, если нет уверенности, что не будет пересечений ни с какими другими процессами, которые гипотетически также могут использовать CONTEXT_INFO .
Как проверить работу триггера
Доброе время суток!! Помогите новичку!! Написала код триггера, запустился без ошибок. Но где проверить его? Преподавателю нужен скрин, что выдается ошибка. Где изменить стоимость на 0?
Код:
CREATE TRIGGER TRIG
IF (SELECT STOIMOST FROM INSERTED)
PRINT’НЕЛЬЗЯ ВСТАВЛЯТЬ ЗАПИСЬ С ОТРИЦАТЕЛЬНОЙ СТОИМОСТЬЮ’
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:
Как проверить работу триггеров?
Помогите пожалуйста с проверкой работы двух триггеров? CREATE TRIGGER update_1 ON AFTER UPDATE.
Как проанализировать работу J-K триггера?
Не знаю как проанализировать работу схем. В файлах элемент 2И-не и J-K триггер. Не понятно куда и.
Как проверить столкновение триггера без OnTriggerEnter?
доброго времени суток, у меня в функции update есть цикл. Он перемещает объект, и нужно проверять.
Как проверить прикосновение триггера к коллайдеру без Rigidbody?
Как проверить прикосновение триггера к коллайдеру без Rigidbody? То есть прикосновение статичного.
414 / 265 / 25
Регистрация: 03.10.2011
Сообщений: 1,079
1. Зачем там нужен ON CHET?
2. Уверены, что триггер должен быть с типом AFTER, a не INSTEAD OF (хотя допускаю, что в обоих случая будет работать).
3. Логическое условие в IF задано не верно. С левой стороны табличное выражение (с одним столбцом), а с правой стороны скалярное значение — «0». Сравнивать такие величины тоже самое, что сравнивать кг и литры.
4. Нет обращения к табличкам deleted и inserted, что указывает на несвязанность запроса с триггером для таблички.
5. Нафига в конце триггера ROLLBACK?
Проверить триггер можно написав запрос на обновление, для любой из тестовых записей в таблице.
1312 / 944 / 144
Регистрация: 17.01.2013
Сообщений: 2,348
1 2 3 4 5 6 7 8 9 10 11
CREATE TRIGGER TRIG ON CHET AFTER update AS BEGIN SET NOCOUNT ON; IF EXISTS(SELECT * FROM inserted WHERE STOIMOST0) BEGIN PRINT'НЕЛЬЗЯ ВСТАВЛЯТЬ ЗАПИСЬ С ОТРИЦАТЕЛЬНОЙ СТОИМОСТЬЮ' ROLLBACK END END GO
update chet set STOIMOST=10 where . update chet set STOIMOST=-20 where .
414 / 265 / 25
Регистрация: 03.10.2011
Сообщений: 1,079
Я бы лучше не ответил.
В exists после select лучше ставить 1 (мелочь конечно но «пару» байт сэкономит).
p.s. так и не понял для чего ON CHET в триггере.