Триггеры
Триггеры представляют специальный тип хранимой процедуры, которая вызывается автоматически при выполнении определенного действия над таблицей или представлением, в частности, при добавлении, изменении или удалении данных, то есть при выполнении команд 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
Где хранятся триггеры в MS SQL: их расположение и структура
Триггеры в MS SQL хранятся в системной таблице sys.triggers базы данных. Вы можете использовать следующий SQL-запрос, чтобы получить информацию о триггерах:
SELECT OBJECT_NAME(parent_id) AS Таблица, name AS Триггер, type_desc AS Тип FROM sys.triggers
Детальный ответ
Мне очень приятно помочь вам понять, где хранятся триггеры в MS SQL. Триггеры — это особые объекты базы данных, которые выполняются автоматически в ответ на определенное событие, происходящее в базе данных. Они могут использоваться для выполнения дополнительной логики или проверки перед или после внесения изменений в таблицы. В MS SQL, триггеры хранятся в системных таблицах базы данных. Теперь давайте подробнее рассмотрим, где на самом деле хранятся триггеры в MS SQL. 1. **sys.triggers**: Таблица sys.triggers является одной из главных системных таблиц, где хранятся метаданные о триггерах базы данных. Она содержит информацию о каждом триггере, включая его имя, тип, принадлежность к таблице и другие связанные атрибуты. Можно использовать запросы SQL для извлечения информации о триггерах из этой таблицы, например:
SELECT * FROM sys.triggers WHERE [name] = 'название_триггера'
2. **sys.objects**: Другая важная таблица, где хранятся триггеры в MS SQL, это sys.objects. Она содержит информацию обо всех объектах базы данных, включая триггеры. В этой таблице можно найти триггер по его имени и типу, например:
SELECT * FROM sys.objects WHERE [name] = 'название_триггера' AND type = 'TR'
3. **sys.sql_modules**: Таблица sys.sql_modules сохраняет определение триггера в виде текста SQL. Важно отметить, что текст триггера может быть распределенным на несколько строк. Для получения полного определения триггера, можно использовать следующий запрос:
SELECT definition FROM sys.sql_modules WHERE [object_id] = (SELECT [object_id] FROM sys.objects WHERE [name] = 'название_триггера')
Вышеупомянутые запросы позволяют получить метаданные и определение триггера в MS SQL. Это очень полезно для понимания и анализа существующих триггеров в базе данных. Теперь давайте рассмотрим пример использования триггера в MS SQL. Предположим, у нас есть таблица «Orders» с полями «OrderID», «OrderDate» и «TotalAmount». Мы хотим создать триггер, который будет автоматически обновлять поле «TotalAmount» при вставке или обновлении записей в таблице «Orders». Вот как это можно сделать:
CREATE TRIGGER UpdateTotalAmount ON Orders AFTER INSERT, UPDATE AS BEGIN UPDATE Orders SET TotalAmount = (SELECT SUM(TotalAmount) FROM inserted WHERE Orders.OrderID = inserted.OrderID) END
В этом примере мы создали триггер «UpdateTotalAmount» на таблице «Orders», который будет запускаться после операций вставки и обновления. Внутри триггера мы обновляем поле «TotalAmount», используя функцию SUM для вычисления суммы новых значений. Здесь «inserted» — это виртуальная таблица, которая содержит только вставленные или обновленные значения. Триггеры могут быть очень полезными для автоматизации и поддержки целостности данных в базе данных MS SQL. С их помощью можно выполнять дополнительные проверки, расчеты или изменения данных при определенных событиях. Надеюсь, этот подробный объяснительный материал помог вам лучше понять, где хранятся триггеры в MS SQL. Если у вас возникнут дополнительные вопросы, не стесняйтесь задавать их! Я всегда готов помочь вам в изучении баз данных и веб-разработке. Удачи в вашем учебном пути! ��
Найти все триггеры базы данных
вас какие триггеры интересуют, вообще все что существует в БД? или обычные insert-update-delete триггеры к таблицам этой БД?
12 сен 2018 в 7:09
Те что относятся к уровню бд
12 сен 2018 в 20:24
3 ответа 3
Сортировка: Сброс на вариант по умолчанию
Ваш запрос вернёт информацию о DDL-триггерах (если они, конечно, есть в БД).
Чтобы найти DML-триггеры фильтруйте по parent_class_desc = ‘OBJECT_OR_COLUMN’
select schemaName = s.name, objectName = o.name, triggerName = tr.name from sys.triggers tr join sys.objects o on o.object_id = tr.parent_id join sys.schemas s on s.schema_id = o.schema_id where tr.parent_class_desc = 'OBJECT_OR_COLUMN' order by s.name, o.name, tr.name Отслеживать
ответ дан 12 сен 2018 в 7:26
8,531 2 2 золотых знака 20 20 серебряных знаков 35 35 бронзовых знаков
Не особо помню тему, но разве недостаточно просто искать записи с parent_class = 0 или 1 ? ну вот например в доках: docs.microsoft.com/ru-ru/previous-versions/sql/sql-server-2014/…
12 сен 2018 в 7:47
@AK, достаточно, но parent_class_desc читабельнее (на мой взгляд), вдруг этот же код будет кто-то читать после меня, это хорошо здесь две константы (0 и 1) — можно запомнить, а если какой-нибудь type в sys.indexes.
12 сен 2018 в 8:23
Мысль понял, спасибо за пояснения.
12 сен 2018 в 8:25
Системные таблицы для каждой базы данных свои. Поэтому нужно сначала выбрать БД:
use database select name from sys.triggers; Отслеживать
ответ дан 12 сен 2018 в 7:22
11.5k 16 16 серебряных знаков 16 16 бронзовых знаков
Коллега, формально вроде как и ответ (привет из очереди проверки), но я бы рекомендовал пояснить словами ваш код, он мало чем отличается от приведённого в запросе. Хотя я не уверен ещё, что топикстартер точно понимает чего именно он хочет в запросе видеть.
12 сен 2018 в 7:51
Пояснил + 4 символа.
12 сен 2018 в 9:33
Скажем так, у меня на уровне баз (5 шт.) висят ddl триггеры, мне необходимо получить все эти триггеры , и увидеть какие из них включены, а какие нет. В sys.triggers видно, что они на базе, видно, что он вкл или выкл, но не видно какая бд.
12 сен 2018 в 20:21
@idrees дак БД — текущая выбранная. Вам нужно триггеры со всех баз собрать?
13 сен 2018 в 5:08
@teran необходимо выбрать ddl триггеры со всех доступных баз. либо указанной базы без использования use
13 сен 2018 в 23:58
Поскольку sys.triggers у каждой базы свой, а вам нужны триггеры со всех баз на сервере, то как вариант, можно сначала пройтись по всем базам и все записи о триггерах впихать во временную таблицу. Тут собственно, либо составить через union динамический sql запрос, либо выполнить запрос с помощью недокументированной sp_MSForEachDB .
Возможно есть пути и проще, но за неимением других ответов:
DECLARE @sql nvarchar(MAX); IF(OBJECT_ID('tempdb.dbo.#tdata') IS NOT NULL) DROP TABLE #tdata; CREATE TABLE #tdata (dbname varchar(255), trname varchar(255)); SELECT @sql = STUFF( (SELECT char(10) + 'UNION' + char(10)+ ' select ''' + name + ''' as dbname, name COLLATE Cyrillic_General_CI_AS as trname from [' + name + '].sys.triggers' FROM master.sys.databases WHERE state = 0 -- базы, которые онлайн FOR XML PATH ('')) , 1, 6, '') SET @sql = 'insert into #tdata ' + @sql; --PRINT @sql; exec sp_executesql @sql; SELECT * from #tdata; DROP TABLE #tdata; К сожалению, структуру временной таблицы приходится описывать, т.к. select * into не будет работать из-за ограничений области видимости.
Поскольку базы могут быт созданы с разным COLLATION, то приходится явно указывать collate при выборке, хотя может обойдетесь и без.
с sp_MSForEachDB проще будет, но я про нее вспомнил когда уже написал остальной запрос.
если же вы заведомо знаете имя БД, то не надо опрашивать все базы, просто изначально сформировать запрос на выборку из @dbname + ‘.sys.triggers’
Триггеры DDL
Триггеры DDL активируются в ответ на различные события языка DDL. Эти события в основном соответствуют инструкциям Transact-SQL, начинающимся с ключевых слов CREATE, ALTER, DROP, GRANT, DENY, REVOKE или UPDATE STATISTICS. Системные хранимые процедуры, выполняющие операции, подобные операциям DDL, также могут запускать триггеры DDL.
Используйте триггеры DDL, если хотите сделать следующее.
- Предотвращать внесение определенных изменений в схему базы данных.
- Настроить выполнение в базе данных некоторых действий в ответ на изменения в схеме базы данных.
- Записывать изменения или события схемы базы данных.
Тестировать триггеры DDL, чтобы определить, как они отвечают на запущенные системные хранимые процедуры. Например: как инструкция CREATE TYPE, так и хранимая процедура sp_addtype вызывают срабатывание триггера DDL, созданного на событии CREATE_TYPE.
Типы триггеров DDL
Триггер DDL языка Transact-SQL
Специальный тип хранимой процедуры Transact-SQL, которая выполняет одну или несколько инструкций Transact-SQL в ответ на событие с областью действия сервера или базы данных. Например, триггер DDL может активироваться, если выполняется такая инструкция, как ALTER SERVER CONFIGURATION, или если происходит удаление таблицы с использованием команды DROP TABLE.
Триггер DDL среды CLR
Вместо выполнения хранимой процедуры Transact-SQL триггер CLR выполняет один или несколько методов, написанных в управляемом коде, которые являются членами сборки, созданной в .NET Framework и переданной в SQL Server.
Триггеры DDL срабатывают только после выполнения соответствующих инструкций DDL. Триггеры DDL нельзя использовать в качестве триггеров INSTEAD OF. Триггеры DDL не срабатывают в ответ на события, влияющие на локальные или глобальные временные таблицы и хранимые процедуры.
Триггеры DDL не создают специальные таблицы inserted и deleted .
Сведения о событии, приведшем к срабатыванию триггера DDL, и последующих изменениях, выполненных триггером, можно получить при помощи функции EVENTDATA.
Для каждого события DDL должно быть создано несколько триггеров.
В отличие от триггеров DML, триггеры DDL не ограничены областью схемы. Поэтому для запроса метаданных о триггерах DDL нельзя воспользоваться такими функциями как OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY и OBJECTPROPERTYEX. Используйте вместо них представления каталога.
Триггеры DDL сервера появляются в обозревателе объектов среды SQL Server Management Studio в папке Triggers . Эта папка находится под папкой Объекты сервера . Триггеры DDL, доступные в области базы данных, находятся в папке Триггеры базы данных . Эта папка находится в папке Программирование соответствующей базы данных.
Вредоносный программный код внутри триггеров может быть запущен с расширенными правами доступа. Дополнительные сведения о том, как уменьшить эту угрозу, см. в статье Управление безопасностью триггеров.
Область действия триггера DDL
Триггеры DDL могут запускаться в ответ на событие Transact-SQL, обработанное в текущей базе данных или на текущем сервере. Область триггера зависит от события. Например, триггер DDL, созданный для срабатывания на событие CREATE TABLE, может срабатывать каждый раз, когда в базе данных или в экземпляре сервера возникает событие CREATE_TABLE. Триггер DDL, созданный для запуска в ответ на событие CREATE_LOGIN, может выполнять это только при возникновении события CREATE_LOGIN в экземпляре сервера.
В следующем примере триггер DDL safety будет срабатывать каждый раз, когда в базе данных будет выполняться инструкция DROP_TABLE или происходить событие ALTER_TABLE .
CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger "safety" to drop or alter tables!' ROLLBACK; В следующем примере триггер DDL выводит сообщение, если в текущем экземпляре сервера происходит любое событие CREATE_DATABASE . В примере используется EVENTDATA функция для получения текста соответствующей инструкции Transact-SQL. Дополнительные сведения об использовании EVENTDATA с триггерами DDL см. в статье Использование функции EVENTDATA.
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'ddl_trig_database') DROP TRIGGER ddl_trig_database ON ALL SERVER; GO CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS PRINT 'Database Created.' SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') GO DROP TRIGGER ddl_trig_database ON ALL SERVER; GO Списки, сопоставляющие инструкции Transact-SQL с областями, которые можно указать для них, доступны по ссылкам, указанным в разделе «Выбор конкретного оператора DDL для запуска триггера DDL», далее в этом разделе.
Триггеры DDL масштаба базы данных хранятся как объекты в базах данных, в которых они создаются. Триггеры DDL можно создавать и в базе данных master , и они будут работать точно так же, как триггеры, созданные в пользовательских базах данных. Чтобы получить сведения о триггерах DDL, можно послать запрос к представлению каталога sys.triggers . Запрос к sys.triggers можно выполнить в контексте базы данных, где были созданы триггеры. Или можно задать имя базы данных в качестве идентификатора, например master.sys.triggers.
Триггеры DDL масштаба триггера хранятся как объекты в базе данных master . Однако для получения сведений о триггерах DDL сервера можно направить запрос к представлению каталога sys.server_triggers в любом контексте базы данных.
Определение инструкции или группы инструкций Transact-SQL
Выбор определенной инструкции DDL для запуска триггера DDL
Триггеры DDL можно использовать для запуска одной или нескольких конкретных инструкций Transact-SQL. В предыдущем примере триггер safety срабатывает после любого события DROP_TABLE или ALTER_TABLE . Список инструкций Transact-SQL, которые можно указать для запуска триггера DDL, и области, с которой триггер может срабатывать, см. в разделе «События DDL».
Выбор предопределенной группы инструкций DDL для запуска триггера DDL
Триггер DDL может срабатывать после выполнения любого события Transact-SQL, которое относится к предопределенной группе аналогичных событий. Например, если нужно, чтобы триггер DDL срабатывал после выполнения любой инструкции CREATE TABLE, ALTER TABLE или DROP TABLE, можно указать FOR DDL_TABLE_EVENTS в инструкции CREATE TRIGGER. После выполнения CREATE TRIGGER события, входящие в группу событий, добавляются в представление каталога sys.trigger_events .
В SQL Server 2005 (9.x), если триггер создается в группе событий, sys.trigger_events не содержит сведения о группе событий, sys.trigger_events содержит сведения только о отдельных событиях, охваченных этой группой. В SQL Server 2008 (10.0.x) и более поздних версий sys.trigger_events сохраняет метаданные о группе событий, в которой создаются триггеры, а также о отдельных событиях, охватывающих группу событий. Поэтому изменения событий, охватываемых группами событий в SQL Server 2008 (10.0.x) и более поздних версий, не применяются к триггерам DDL, созданным на этих группах событий в SQL Server 2005 (9.x).
Список стандартных групп инструкций DDL для триггеров DDL, инструкции, входящие в эти группы событий, а также области, где можно программировать эти группы событий, приводятся в разделе DDL Event Groups.
Связанные задачи
| Задача | Раздел |
|---|---|
| Описывает, как создать, изменить, удалить или отключить триггеры DDL. | Реализация триггеров DDL |
| Описывает, как создать триггер DDL CLR. | Создание триггеров CLR |
| Описывает, как возвратить сведения о триггерах DDL. | Получение сведений о триггерах DDL |
| Описывает, как возвратить сведения о событии, которое активирует триггер DDL с использованием функции EVENTDATA. | Использование функции EVENTDATA |
| Описывает, как управлять безопасностью триггеров. | Управление безопасностью триггеров |