Триггер в базе данных: Триггеры в SQL на примере базы данных SQLite

Содержание

Вы не любите триггеры? / Хабр

Вы не любите кошек? Да вы просто не умеете их готовить! (с) Альф

При проектировании достаточно объёмных реляционных баз данных часто принимается решение об отступлении от нормальной формы — «денормализации».

Причины могут быть разными. От попытки ускорения доступа к определённым данным, ограничений используемой платформы/фреймворка/средств разработки и до недостатка квалификации разработчика/проектировщика БД.

Впрочем, строго говоря, ссылка на ограничения фреймфорка и т.п. — по сути попытка оправдать недостаток квалификации.

Денормализованные данные — слабое звено, через которое легко можно привести нашу базу в неконсистентное (нецелостное) состояние.

Что с этим делать?

Пример


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

Нужно всегда знать остаток средств на счёте.

В нормализованных данных остаток средств — всегда рассчитываемая величина. Суммируем все поступления минус списания.

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

Поэтому принято решение хранить актуальные остатки в отдельной таблице. Как обновлять данные в этой таблице?

Решение «как обычно»


Практически во всех информационных системах, с которыми мне приходилось работать, эту задачу выполняло внешнее приложение, в котором реализована бизнес логика. Хорошо, если приложение несложное и точек изменения данных — одна, из формы в пользовательском интерфейсе. А если есть какие-то импорты, API, сторонние приложения и так далее? И эти вещи делают разные люди, команды? А если не одна таблица с итогами, а их несколько в разных разрезах? А если ещё и не одна таблица с операциями (встречал и такое)?

Тут уследить за тем, что разработчик при обновлении операции не забыл обновить ещё кучку таблиц, становится всё сложнее и сложнее. Данные теряют целостность. Остатки по счёту не соответствуют операциям. Конечно, тестирование должно выявить такие ситуации. Но мы живём не в таком идеальном мире.

Кошки Триггеры


В качестве альтернативы для контроля целостности денормализованных данных «взрослой» СУБД используют триггеры.

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

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

Давайте разберёмся.

Тормоза


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

update totals 
set total = select sum(operations. amount) from operations where operations.account = current_account
where totals.account = current_account


Запрос обращается к таблице операций (operations) и суммирует все суммы операций (amount) для счёта (account).

Такой запрос с ростом базы данных будет съедать всё больше и больше времени и ресурсов. Но того же результата можно добиться, используя «лёгкий» запрос типа:

update totals 
set total = totals.total + current_amount
where totals.account = current_account


Такой триггер при добавлении новой строки просто увеличит итог по счёту, не рассчитывая его заново, он не зависит от объёма данных в таблицах. Рассчитывать итог заново нет смысла, так как мы можем быть уверены, что триггер срабатывает ВСЕГДА при добавлении новой операции.

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

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

Бизнес логика


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

Впрочем, есть мнение, что всю бизнес логику легко можно реализовать средствами современной СУБД, такой как PostgreSQL или Oracle. Подтверждение нахожу в своём just-for-fun проекте.

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

Конечно, я далёк от мысли, что всё здесь написанное, является истиной в последней инстанции. В реальной жизни, конечно же, всё сложнее. Поэтому решения в каждом конкретном случае принимать вам. Используйте своё инженерное мышление!

P.S.


Спасибо за аргументированные доводы «за» и «против».

По следам обсуждения статьи несколько замечаний.

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

  • Подход, описанный в статье, может позволить отказаться от индексов на таблице operations, что может ускорить вставку данных в эту таблицу. На больших объёмах этот эффект легко компенсирует в том числе и временнЫе затраты на работу триггера, не говоря уже о затратах памяти на индексы.

  • Важно понимать какой инструмент для чего использовать, тогда вы избежите многих проблем, например, со statement restart в BEFORE триггерах

  • Для логов триггеры — вообще мастхев

НОУ ИНТУИТ | Лекция | Триггеры: создание и применение

< Лекция 13 || Лекция 14: 123 || Лекция 15 >

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

Ключевые слова: триггер, исполнение, DML, базы данных, операции, SQL, достоверность, транзакция, откат, триггерное событие, ограничения целостности данных, формат команды, таблица, конфликт доступа, СУБД, server, сервер, шифрование, доступ, создание триггера, специальная таблица inserted, специальная таблица: deleted, %ROWCOUNT, обнаружение нарушений, удаление триггера, реализация ограничений на значение, статус ошибки

Определение триггера в стандарте языка SQL

Триггеры являются одной из разновидностей хранимых процедур.
Их исполнение происходит при выполнении для таблицы какого-либо
оператора языка манипулирования данными (DML). Триггеры используются для проверки целостности данных, а также для отката транзакций.

Триггер – это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных. Применение триггеров большей частью весьма удобно
для пользователей базы данных. И все же их использование часто связано с
дополнительными затратами ресурсов на операции ввода/вывода. В том
случае, когда тех же результатов (с гораздо меньшими непроизводительными затратами ресурсов) можно добиться с помощью хранимых процедур
или прикладных программ, применение триггеров нецелесообразно.

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

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

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

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

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

  • проверка корректности введенных данных и выполнение сложных
    ограничений целостности данных, которые трудно, если вообще
    возможно, поддерживать с помощью ограничений целостности, установленных для таблицы;
  • выдача предупреждений, напоминающих о необходимости выполнения некоторых действий при обновлении таблицы, реализованном
    определенным образом;
  • накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили;
  • intuit.ru/2010/edi»>поддержка репликации.

Основной формат команды CREATE TRIGGER показан ниже:

<Определение_триггера>::=
  CREATE TRIGGER имя_триггера
  BEFORE | AFTER <триггерное_событие>
  ON <имя_таблицы>
  [REFERENCING 
    <список_старых_или_новых_псевдонимов>]
  [FOR EACH { ROW | STATEMENT}]
  [WHEN(условие_триггера)]
  <тело_триггера>

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

Выполняемые триггером действия задаются для каждой строки ( FOR
EACH ROW ), охваченной данным событием, или только один раз для каждого события ( FOR EACH STATEMENT ).

Обозначение <список_старых_или_новых_псевдонимов> относится к таким компонентам, как старая или новая строка ( OLD / NEW )
либо старая или новая таблица ( OLD TABLE / NEW TABLE ). Ясно, что
старые значения не применимы для событий вставки, а новые – для событий удаления.

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

  • сложность: при перемещении некоторых функций в базу данных усложняются задачи ее проектирования, реализации и администрирования;
  • скрытая функциональность: перенос части функций в базу данных и
    сохранение их в виде одного или нескольких триггеров иногда приводит к сокрытию от пользователя некоторых функциональных возможностей. Хотя это в определенной степени упрощает его работу, но, к
    сожалению, может стать причиной незапланированных, потенциально нежелательных и вредных побочных эффектов, поскольку в этом
    случае пользователь не в состоянии контролировать все процессы,
    происходящие в базе данных;
  • влияние на производительность: перед выполнением каждой команды по изменению состояния базы данных СУБД должна проверить триггерное условие с целью выяснения необходимости запуска триггера для этой команды. Выполнение подобных вычислений
    сказывается на общей производительности СУБД, а в моменты пиковой нагрузки ее снижение может стать особенно заметным. Очевидно, что при возрастании количества триггеров увеличиваются и
    накладные расходы, связанные с такими операциями.

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

Дальше >>

< Лекция 13 || Лекция 14: 123 || Лекция 15 >

триггеров в SQL Server — javatpoint

следующий →
← предыдущая

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

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

Ниже приведены основные характеристики, отличающие триггеры от хранимых процедур:

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

Синтаксис триггера

Мы можем создать триггер в SQL Server с помощью инструкции CREATE TRIGGER следующим образом:

СОЗДАТЬ ТРИГГЕР схема.trigger_name
ON имя_таблицы
ПОСЛЕ {ВСТАВИТЬ, ОБНОВИТЬ, УДАЛИТЬ}
[НЕ ДЛЯ РЕПЛИКАЦИИ]
КАК
{SQL_Statements}

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

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

trigger_name: Это обязательный параметр, определяющий имя нового триггера.

имя_таблицы: Это обязательный параметр, определяющий имя таблицы, к которой применяется триггер. Рядом с именем таблицы нам нужно написать предложение AFTER, в котором могут быть перечислены любые события, такие как INSERT, UPDATE или DELETE.

НЕ ДЛЯ РЕПЛИКАЦИИ: Этот параметр указывает, что SQL Server не выполняет триггер, когда данные изменяются как часть процесса репликации.

SQL_Statements: Содержит одну или несколько инструкций SQL, которые используются для выполнения действий в ответ на происходящее событие.

Когда мы используем триггеры?

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

Пример триггера в SQL Server

Давайте разберемся, как мы можем работать с триггерами в SQL Server. Мы можем сделать это, сначала создав таблицу с именем « Сотрудник» , используя следующие операторы:

СОЗДАТЬ ТАБЛИЦУ Сотрудник
(
Идентификатор INT ПЕРВИЧНЫЙ КЛЮЧ,
Имя ВАРЧАР(45),
Зарплата ИНТ,
Пол VARCHAR(12),
Идентификатор отдела INT
)

Далее мы вставим в эту таблицу какую-нибудь запись следующим образом:

ВСТАВЬТЕ В ЗНАЧЕНИЯ СОТРУДНИКОВ (1, «Стефан», 82000, «Мужчина», 3),
(2, «Амели», 52000, «Женщина», 2),
(3, ‘Антонио’, 25000, ‘мужчина’, 1),
(4, «Марко», 47000, «Мужской», 2),
(5, «Элиана», 46000, «Женщина», 3)

Мы можем проверить операцию вставки с помощью инструкции SELECT. Мы получим следующий вывод:

ВЫБЕРИТЕ * ОТ Сотрудника;

Мы также создадим еще одну таблицу с именем « Employee_Audit_Test» для автоматического сохранения записей транзакций каждой операции, такой как INSERT, UPDATE или DELETE, в таблице Employee:

СОЗДАТЬ ТАБЛИЦУ Employee_Audit_Test
(
ID int IDENTITY,
Текст Audit_Action
)

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

СОЗДАТЬ ТРИГГЕР trInsertEmployee
ON Сотрудник
ДЛЯ ВСТАВКИ
КАК
НАЧИНАТЬ
Объявить @Id int
SELECT @Id = идентификатор из вставленного
ВСТАВИТЬ В Employee_Audit_Test
VALUES («Новый сотрудник с идентификатором = ‘ + CAST(@Id AS VARCHAR(10)) + ‘ добавляется в ‘ + CAST(Getdate() AS VARCHAR(22)))
КОНЕЦ

После создания триггера попробуем добавить в таблицу следующую запись:

ВСТАВЬТЕ В ЦЕННОСТИ Сотрудников (6, «Питер», 62000, «Мужской», 3)

Если ошибок не обнаружено, выполните оператор SELECT, чтобы проверить записи аудита. Мы получим вывод следующим образом:

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

СОЗДАТЬ ТРИГГЕР
ON Сотрудник
ДЛЯ УДАЛЕНИЯ
КАК
НАЧИНАТЬ
Объявить @Id int
ВЫБЕРИТЕ @Id = идентификатор из удаленного
ВСТАВИТЬ В Employee_Audit_Test
VALUES («Существующий сотрудник с Id = ‘ + CAST(@Id AS VARCHAR(10)) + ‘ удаляется в ‘ + CAST(Getdate() AS VARCHAR(22)))
КОНЕЦ

После создания триггера мы удалим запись из таблицы Employee:

УДАЛИТЬ ОТ Сотрудника, ГДЕ Id = 2;

Если ошибок не обнаружено, выдается следующее сообщение:

Наконец, выполните оператор SELECT, чтобы проверить записи аудита:

В коде обоих триггеров вы увидите следующие строки:

SELECT @Id = идентификатор из вставленного
ВЫБЕРИТЕ @Id = идентификатор из удаленного

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

Типы триггеров SQL Server

Мы можем классифицировать триггеры в SQL Server в основном по трем типам:

  1. Триггеры языка определения данных (DDL)
  2. Триггеры языка обработки данных (DML)
  3. Триггеры входа в систему

Триггеры DDL

Триггеры DDL запускаются в ответ на события DDL, такие как операторы CREATE, ALTER и DROP. Мы можем создавать эти триггеры на уровне базы данных или на уровне сервера, в зависимости от типа событий DDL. Его также можно выполнять в ответ на определенные системные хранимые процедуры, выполняющие операции, подобные DDL.

Триггеры DDL полезны в следующем сценарии:

  • Когда нам нужно предотвратить изменение схемы базы данных
  • Когда нам нужно проверить изменения, внесенные в схему базы данных
  • Когда нам нужно отреагировать на изменение, внесенное в схему базы данных

Триггеры DML

Триггеры DML запускаются в ответ на такие события DML, как инструкции INSERT, UPDATE и DELETE в пользовательской таблице или представлении. Он также может выполняться в ответ на операции, подобные DML, выполняемые системными хранимыми процедурами.

Триггеры DML можно разделить на два типа:

  • После запуска
  • Вместо триггеров

После запуска

После срабатывания триггера, когда SQL Server успешно завершает действие триггера, которое его инициировало. Как правило, этот триггер выполняется, когда таблица завершает операции вставки, обновления или удаления. Он не поддерживается в представлениях. Иногда его называют триггерами FOR.

Мы можем разделить этот триггер на три типа:

  1. Триггер ПОСЛЕ ВСТАВКИ
  2. Триггер ПОСЛЕ ОБНОВЛЕНИЯ
  3. Триггер ПОСЛЕ УДАЛЕНИЯ

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

Ниже показан синтаксис After Triggers в SQL Server:

СОЗДАТЬ ТРИГГЕР имя_схемы.имя_триггера
ON имя_таблицы
ПОСЛЕ {ВСТАВИТЬ | ОБНОВЛЕНИЕ | УДАЛИТЬ}
КАК
НАЧИНАТЬ
— Триггерные операторы
— Вставить, обновить или удалить операторы
КОНЕЦ

Вместо триггеров

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

Мы можем разделить этот триггер на три типа:

  1. ВМЕСТО ВСТАВКИ Триггер
  2. Триггер ВМЕСТО ОБНОВЛЕНИЯ
  3. Триггер ВМЕСТО УДАЛЕНИЯ

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

Ниже приведена иллюстрация синтаксиса вместо триггеров в SQL Server:

СОЗДАТЬ ТРИГГЕР имя_схемы.имя_триггера
ON имя_таблицы
ВМЕСТО {ВСТАВИТЬ | ОБНОВЛЕНИЕ | УДАЛИТЬ}
КАК
НАЧИНАТЬ
— триггерные операторы
— Вставить, обновить или удалить команды
КОНЕЦ

Триггеры входа в систему

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

Как показать триггеры в SQL Server?

Когда у нас много баз данных с несколькими таблицами, триггер show или list пригодится. Когда имена таблиц в нескольких базах данных совпадают, этот запрос чрезвычайно полезен. С помощью следующей команды мы можем увидеть список всех триггеров, доступных в SQL Server:

ВЫБЕРИТЕ имя, is_instead_of_trigger
ОТ sys.triggers
ГДЕ тип = ‘TR’;

Если мы используем SQL Server Management Studio очень легко показать или перечислить все триггеры, доступные в любой конкретной таблице. Мы можем сделать это, используя следующие шаги:

  • Перейдите в меню Базы данных , выберите нужную базу данных и разверните ее.
  • Выберите меню Tables и разверните его.
  • Выберите любую конкретную таблицу и разверните ее.

Здесь мы получим различные варианты. Когда мы выбираем опцию Triggers , она отображает все триггеры, доступные в этой таблице.

Как ОБНОВИТЬ триггеры в SQL Server?

Данные, хранящиеся в таблице, могут изменяться с течением времени. В этом случае нам также необходимо внести изменения в триггеры. Мы можем сделать это двумя способами в SQL Server. Первый — использовать SQL Server Management Studio, а второй — запрос Transact-SQL.

Изменение триггеров с помощью SSMS

Сначала откройте Management Studio, чтобы изменить триггер. Затем перейдите в базу данных, а затем в таблицу, в которой хранится триггер. Теперь щелкните правой кнопкой мыши триггер, который вы собираетесь изменить или обновить. Откроется контекстное меню, где вы выберете Изменить вариант:

При выборе опции Modify вы увидите новое окно запроса с автоматически сгенерированным кодом ALTER TRIGGER. Мы можем изменить его в соответствии с нашими потребностями.

Изменение триггеров с помощью команды SQL

Мы можем использовать оператор ALTER TRIGGER для изменения триггеров в MS SQL. Следующий оператор позволяет нам модифицировать триггеры:

ИЗМЕНИТЬ ТРИГГЕР [dbo].[triggers_in_sql]
ON [dbo].[Таблица Сотрудников]
ПОСЛЕ ВСТАВКИ
КАК
НАЧИНАТЬ
— Изменить в соответствии с вашими потребностями
КОНЕЦ

Как УДАЛИТЬ триггеры в SQL Server?

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

Следующий синтаксис удаляет триггеры DML:

УДАЛИТЬ ТРИГГЕР [ЕСЛИ СУЩЕСТВУЕТ] имя_схемы.имя_триггера;

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

DROP TRIGGER имя_схемы.имя_триггера1, имя_триггера2…..n;

Мы можем использовать оператор DROP TRIGGER в следующем формате, чтобы удалить один или несколько триггеров LOGON:

УДАЛИТЬ ТРИГГЕР [ ЕСЛИ СУЩЕСТВУЕТ ] имя_триггера1, имя_триггера2. ….n
НА {БАЗА ДАННЫХ | ВСЕ СЕРВЕРЫ };

Мы можем использовать оператор DROP TRIGGER в следующем формате, чтобы удалить один или несколько триггеров DDL:

УДАЛИТЬ ТРИГГЕР [ ЕСЛИ СУЩЕСТВУЕТ ] имя_триггера1, имя_триггера2…..n
НА ВСЕХ СЕРВЕРАХ;

Если мы используем SQL Server Management Studio, очень легко удалить триггеры из таблицы. Мы можем сделать это, используя следующие шаги:

  • Перейдите в меню Базы данных -> Таблицы и разверните его.
  • Выберите любую конкретную таблицу, разверните ее и выберите параметр Triggers

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

Преимущества триггеров

Ниже приведены преимущества использования триггеров в SQL Server:

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

    • Триггеры

  • просты в обслуживании.

Недостатки триггеров

Ниже приведены недостатки использования триггеров в SQL Server:

  • Триггеры позволяют использовать только расширенные проверки.
  • Триггеры вызываются автоматически, и их выполнение невидимо для пользователя. Следовательно, устранить неполадки, происходящие на уровне базы данных, непросто.
  • Триггеры могут увеличить нагрузку на сервер базы данных.
  • Мы можем определить одно и то же действие триггера для нескольких действий пользователя, таких как INSERT и UPDATE, в одном операторе CREATE TRIGGER.
  • Мы можем создать триггер только в текущей базе данных, но он может ссылаться на объекты за пределами текущей базы данных.

Следующая темаSQL Server CAST

← предыдущая
следующий →

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

Автор: Kris Wenzel   | Обновлено: 4 марта 2023 г.  

Работает с: 

Что такое триггер базы данных?

Триггер SQL — это специальная хранимая процедура, которая запускается, когда в базе данных происходят определенные действия. Большинство триггеров базы данных запускаются при внесении изменений в данные таблицы. Можно определить триггеры для запуска вместо или после действий DML (язык манипулирования данными), таких как INSERT, UPDATE и DELETE.

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

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

Триггеры и их реализации зависят от поставщиков баз данных. В этой статье мы сосредоточимся на сервере Microsoft SQL; однако концепции одинаковы или похожи в Oracle и MySQL.

Примечание. Все примеры для этого урока основаны на Microsoft SQL Server Management Studio и базе данных AdventureWorks2012. Вы можете начать использовать эти бесплатные инструменты, воспользовавшись моими Руководство  Начало работы с SQL Server .

Содержание

  • Что такое триггер базы данных?
  • Trigger Events
    • после триггеров
    • вместо триггеров
    • Специальные объекты базы данных
  • Определение

    • . Сложный!

Триггерные события SQL

Триггеры могут возникать ПОСЛЕ или ВМЕСТО действия DML. Триггеры связаны с действиями DML базы данных INSERT, UPDATE и DELETE. Триггеры определены для запуска, когда эти действия выполняются на определенной таблице.

Триггеры ПОСЛЕ

После завершения действий DML, таких как INSERT, выполняется триггер AFTER. Вот некоторые ключевые характеристики триггеров AFTER:

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

ВМЕСТО триггеров

Триггеры INSTEAD OF, как следует из их названия, запускаются вместо действия DML, вызвавшего их срабатывание. При использовании триггеров INSTEAD OF следует учитывать следующее:

  • Триггер INSTEAD OF переопределяет действие триггера. Если триггер INSTEAD OF определен для выполнения инструкции INSERT, то при попытке выполнения инструкции INSERT управление немедленно передается триггеру INSTEAD OF.
  • Максимум один триггер INSTEAD OF может быть определен для каждого действия для таблицы. Это имеет смысл, как если бы у вас было два триггера «INSTEAD OF» для вставки, какой из них должен выполняться?

Специальные объекты базы данных

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

Таблица INSERTED содержит все новые значения; тогда как таблица DELETED содержит старые значения. Вот как используются таблицы:

  • INSERT — используйте таблицу INSERTED, чтобы определить, какие строки были добавлены в таблицу.
  • DELETE — используйте таблицу DELETED, чтобы увидеть, какие строки были удалены из таблицы.
  • ОБНОВЛЕНИЕ. Используйте таблицу INSERTED для проверки новых или обновленных значений и таблицу DELETED для просмотра значений до обновления.

Определение

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

Ниже приведен пример триггера из базы данных AdventureWorks2012.

Вы заметите, что синтаксис триггера SQL очень похож на синтаксис хранимой процедуры. Фактически триггер использует тот же язык для реализации своей логики, что и хранимые процедуры. В MS SQL это T-SQL; тогда как в Oracle это PL/SQL.

Вот некоторые важные части триггера:

  1. Оператор CREATE — определяет, какая таблица связана с триггером. Кроме того, этот оператор используется для указания времени выполнения триггера (например, после вставки).
  2. Актуальная программа. В примере эта программа запускается всякий раз, когда одна или несколько строк вставляются в таблицу WorkOrder.
  3. Специальные объекты базы данных — триггеры используют специально определенные объекты базы данных, такие как INSERTED или DELETED, для доступа к записям, затронутым действием базы данных.
  4. В этом примере триггер использует объект INSERTED для получения доступа к вновь созданным строкам. Оператор INSERT используется для таблицы этих строк и добавления их в таблицу истории.

Использование для триггеров

Вот несколько распространенных вариантов использования триггеров:

Комплексный аудит

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

Обычно при создании журналов аудита используются триггеры AFTER.

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

Обеспечение соблюдения бизнес-правил

Триггеры SQL можно использовать для проверки всех данных перед выполнением действия DML. Вы можете использовать триггеры INSTEAD OF, чтобы «перехватить» ожидающую операцию DML, применить любые бизнес-правила и в конечном итоге завершить транзакцию.

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

  • Золото — покупки на сумму более 1 000 000 долларов США за последние 12 месяцев.
  • Silver — Покупка на сумму от 500 000 до 1 000 000 долларов США за последние 12 месяцев.
  • Бронза — Все остальные уровни покупки.

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

Получение значений столбца

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