Sql вывести все триггеры

SQL-Ex blog

Новости сайта «Упражнения SQL», статьи и переводы

Руководство по триггерам в SQL: настройка отслеживания базы данных в PostgreSQL

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

В мире стартапов с открытыми исходными кодами и разработкой полного стека (типа Django, Rails, Javascript, PHP, MySQL, Postgres. ) очень популярны ORM, а средства типа триггеров SQL не так востребованы.

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

В этой статье я поделюсь информацией о том, как эффективно использовать триггеры SQL.

Содержание

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

Триггеры SQL, также называемые триггерами баз данных, позволяют вам сказать движку SQL (например PostgreSQL) выполнить часть кода при наступлении некоторого события, или даже перед наступлением события.

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

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

Преимущества использования триггеров SQL

Поддержание целостности данных

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

Разделение бизнес-логики

Размещение критичной бизнес-логики в коде приложения также представляет проблему, когда бизнес-логика обновляется. Если вашим бизнес-требованием являлось умножение входящих номеров на 10, а теперь вы захотели умножать это число на 20, изменение логики в SQL гарантировало бы, что каждые данные, начиная точно с момента развертывания, будут обрабатываться новой логикой.

Сервер SQL действует как единственная точка истины. Если логика внедрена на множестве серверов приложений, вы не сможете ожидать определенного изменения в поведении, выполненного чисто.

Атомарные транзакции

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

Как создать триггер SQL: синтаксис PostgreSQL

Типы событий триггера

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

Триггер базы данных допускает также перечисление более одного из этих событий.

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

Триггер BEFORE (до) или AFTER (после)

Триггер может выполняться либо до, либо после события.

Если вы хотите заблокировать событие типа INSERT, вы захотите выполнять действие до (BEFORE). Если вы хотите быть уверенным, что событие действительно произойдет, идеальный вариант — после (AFTER).

Воздействие триггера

Триггер может выполняться либо на строку, либо на оператор. Скажем, вы выполняете один оператор UPDATE, который изменяет 5 строк в таблице.

Если вы укажете в триггере FOR EACH ROW, тогда триггер выполнится 5 раз. Если вы укажете FOR EACH STATEMENT, тогда он выполнится только раз.

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

Пример триггера #1: создание таймера

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

Настройка схемы базы данных

Структура схемы предполагает каждый вход и выход отдельными событиями. Каждое событие — это строка в таблице time_punch. Как альтернативу вы можете также сделать каждую «рабочую смену» сотрудника событием и хранить время как входа, так и выхода в одной строке.

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

Для нашего примера я уже разработал схему таблиц. Нижеприведенный код создает таблицы employee и time_punch и вставляет некоторые данные по времени прохода для нового сотрудника Bear.

Bear зашел в 10:00 и вышел в 11:30 (длинный рабочий день). Давайте напишем запрос SQL для вычисления рабочего времени Bear.

Остановитесь и подумайте, как бы вы решили эту задачу при данной схеме и с помощью одного SQL.

Использование SQL для вычисления рабочего времени

Решение, которое я предлагаю, ищет на каждый «выход» соответствующий ему «вход».

В этом запросе я выбираю все выходы, затем я соединяю их с наиболее близким «входом». Беру разность временных меток и получаю количество часов, которое отработал Bear в каждой смене!

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

Пример триггера INSERT BEFORE: сохранение целостности данных

Нам требуется то, что не позволит нарушить шаблон вход/выход. К сожалению, ограничения check только отслеживают вставляемую или обновляемую строку и не могут учитывать данные из других строк.

Это идеальная ситуация для использования триггера баз данных!

Давайте создадим триггер для предотвращения события INSERT, которое нарушает наш шаблон. Сначала мы создадим «триггерную функцию». Эта функция есть то, что будет выполнять триггер при наступлении события.

Триггерная функция создается как обычная функция PostgreSQL за тем исключением, что возвращает триггер.

Ключевое слово new представляет значения вставляемой строки. Это также объект, который вы можете вернуть, чтобы позволить продолжиться вставке. Напротив, возвращение null остановит вставку.

Этот запрос сначала находит в time_punch предыдущее значение и гарантирует, что это значение входа/выхода не совпадает с вставляемым значением. Если значения совпадают, то триггер возвращает null, и time_punch не записывается. В противном случае, триггер возвращает new и оператор insert продолжается.

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

Давайте попробуем вставить еще один «выход»:

Как можно видеть по выводу, триггер предотвратил вставку двух последовательных выходов для одного и того же сотрудника.

Можно также вызвать исключение из триггера с тем, чтобы ваше приложение (или лицо, выполняющее запрос SQL) получило уведомление об отказе вместо 0 как числа вставленных строк.

Пример триггера в PostgreSQL #2: создание таблицы аудита

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

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

Таблица аудита выполняет эту роль, отслеживая каждое изменение основной таблицы. Когда в главной таблице обновляется строка, в таблицу аудита вставляется строка в ее предыдущем состоянии.

Я буду использовать нашу таблицу time_punch для демонстрации создания и автоматического обновления таблицы аудита с помощью триггеров.

Создание таблицы аудита

В эту таблицу записывается:

• Время обновления прохождения.
• Сотрудник, который выполнил обновление.
• ID прохода, который был изменен.
• Время прохода до того, как было сделано обновление.

Прежде чем создавать триггер, сначала нам нужно добавить столбец change_employee_id в таблицу time_punch. Тогда триггер будет знать, какой сотрудник сделал каждое изменение в таблице time_punch.

(Как альтернативное решение без добавления каких-либо столбцов в time_punch, можно аннулировать права update на эту таблицу и заставить пользователей базы данных использовать пользовательскую функцию типа update_time_punch(id, change_user_id, . ))

После того, как произойдет обновление таблицы time_punch, выполнится этот триггер и запишет OLD (старое) значение времени прохода в нашу таблицу аудита.

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

Для триггера на обновление объект NEW представляет те значения, которые будут содержаться
в строке при успешном обновлении. Вы можете использовать триггер для «перехвата» вставки или обновления простым присвоением своих собственных значений в объект NEW. Объект OLD содержит значения строки до обновления.

Проверим, работает ли это! Я добавил второго пользователя с именем Daniel, который будет редактором времени прохода Bear.

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

А вот таблица аудита, отражающая прошлые времена прохода:

Дополнительные сообщажения относительно триггеров

Обслуживание триггеров с течением времени

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

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

Связанная логика триггера

Триггеры также могут запускать другие триггеры, быстро усложняя результаты казалось бы невинных INSERT или UPDATE. Этот риск также может привести к побочным эффектам кода приложения.

Опыт разработчиков

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

Изначально SQL может показаться неуклюжим и неудобным языком для изучения, поскольку многие из шаблонов, которые вы изучаете для построения запроса, «вывернуты наизнанку» по сравнению с тем, как вы извлекаете данные на процедурном языке.

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

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

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

Комментарии

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

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

Источник

Как найти все триггеры, связанные с таблицей с SQL Server?

Я создал триггер для таблицы в SQL Server и это работает для меня.

моя проблема: как найти его и изменить его?

Я использую этот запрос, чтобы найти свои триггеры:

это найти все триггеры, но как открыть его и изменить триггеры?

11 ответов

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

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

Это может быть полезным

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

здесь просто скопируйте определение и измените триггер.

иначе вы можете просто перейти к SSMS и развернуть свою БД и в разделе программируемость развернуть триггеры базы данных, затем щелкните правой кнопкой мыши на определенном триггере и нажмите Изменить там также вы можете изменить.

используйте sp_helptrigger, чтобы найти triggerlist для связанных таблиц

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

Я считаю, что вы работаете в sqlserver следующие шаги, чтобы получить изменить триггеры

для изменения триггера

разверните группу серверов, а затем разверните сервер.

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

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

в поле » Имя » выберите имя триггера.

при необходимости измените текст триггера в текстовом поле. Нажимать CTRL + TAB отступ текста диспетчера SQL Server Enterprise Manager спусковой крючок.

чтобы проверить синтаксис триггера, щелкните проверить синтаксис.

найти триггеры на таблице:

и вы можете найти процедуру хранения, которая имеет ссылку на таблицу:

С помощью этого запроса Вы можете найти все триггеры во всех таблицах и всех представлениях.

Источник

Получение сведений о триггерах DML

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure

В этом разделе описывается получение сведений о триггерах DML в SQL Server с помощью среды SQL Server Management Studio или Transact-SQL. К таким сведениям относятся типы триггеров для таблицы, имя триггера, владелец триггера и дата создания или изменения триггера. Если триггер не был зашифрован во время создания, то можно получить его определение. По определению вы можете понять, каким образом триггер влияет на таблицу, для которой он определен. Кроме того, можно определить, какие объекты используются данным триггером. Эти сведения могут быть использованы для выявления объектов, которые воздействуют на триггер, если они изменяются или удаляются из базы данных.

В этом разделе

Перед началом работы

Для получения сведений о триггерах DML используется:

Перед началом

безопасность

Permissions

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

В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine и разверните его.

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

Разверните узел Триггеры, щелкните правой кнопкой мыши нужный триггер и выберите команду Изменить. В окне запроса появится определение триггера DML.

Просмотр зависимостей триггера DML

В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine и разверните его.

Разверните нужную базу данных, разверните узел Таблицы, а затем разверните таблицу, содержащую триггер и зависимости, которые нужно просмотреть.

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

Для просмотра объектов, зависящих от триггера DML, в окне Зависимости объектов выберите Объекты, зависящие от . Объекты отображаются в области Зависимости .

Чтобы просмотреть объекты, от которых зависит триггер DML, выберите пункт Объекты, от которых зависит . Объекты отображаются в области Зависимости . Разверните каждый узел, чтобы просмотреть все объекты.

Чтобы получить сведения об объекте, который появляется в области Зависимости , щелкните его. В поле Выбранный объект сведения указываются в полях Имя, Тип и Тип зависимости .

Нажмите кнопку ОК , чтобы закрыть окно Зависимости объекта.

Использование Transact-SQL

Просмотр определения триггера DML

Установите соединение с компонентом Компонент Database Engine.

На панели «Стандартная» нажмите Создать запрос.

Скопируйте и вставьте один из следующих примеров в окно запроса и нажмите кнопку Выполнить. В каждом примере показано, как можно просмотреть определение триггера iuPerson .

Просмотр зависимостей триггера DML

Установите соединение с компонентом Компонент Database Engine.

На панели «Стандартная» нажмите Создать запрос.

Скопируйте и вставьте один из следующих примеров в окно запроса и нажмите кнопку Выполнить. В каждом примере показано, как можно просмотреть зависимости триггера iuPerson .

Просмотр сведений о триггерах DML в базе данных

Установите соединение с компонентом Компонент Database Engine.

На панели «Стандартная» нажмите Создать запрос.

Скопируйте и вставьте один из следующих примеров в окно запроса и нажмите кнопку Выполнить. В каждом примере показано, как можно просмотреть сведения о триггерах DML ( TR ) в базе данных.

Просмотр сведений о событиях, которые вызывают срабатывание триггера DML

Установите соединение с компонентом Компонент Database Engine.

На панели «Стандартная» нажмите Создать запрос.

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

Источник