Sql триггеры примеры: MS SQL Server и T-SQL

Триггеры в SQL — CodeTown.ru

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

Общие сведения

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

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

Создание триггеров в SQL

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

CREATE TRIGGER name_trigger

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

  • 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 были продемонстрированы. Если у вас остались вопросы, то оставляйте их в комментариях.

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

< Лекция 18 || Лекция 14: 123

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

Ключевые слова: триггер, исполнение, 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 (после их выполнения).

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

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

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

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

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

Дальше >>

< Лекция 18 || Лекция 14: 123

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

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

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

В SQL Server

есть три типа триггеров.

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

DML-триггеры

Триггер DML (язык манипулирования данными) автоматически вызывается, когда в таблице выполняется оператор INSERT, UPDATE или DELETE.

Используйте оператор CREATE TRIGGER для создания триггера в SQL Server.

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

В приведенном выше синтаксисе:

  • имя_схемы (необязательно) — имя схемы, в которой будет создан новый триггер.
  • trigger_name — это имя нового триггера.
  • ВКЛ { имя_таблицы | имя_представления } Ключевое слово указывает имя таблицы или представления, для которого будет создан триггер.
  • Предложение AFTER определяет событие INSERT, UPDATE или DELETE, которое запускает триггер. Предложение AFTER указывает, что триггер срабатывает только после того, как SQL Server успешно завершит выполнение действия, вызвавшего его срабатывание.
    Все остальные действия и ограничения должны быть успешно выполнены до срабатывания триггера.

  • Предложение INSTEAD OF используется для пропуска инструкции INSERT, UPDATE или DELETE к таблице и вместо этого выполняет другие инструкции, определенные в триггере.
    Таким образом, фактическая инструкция INSERT, UPDATE или DELETE вообще не выполняется. Предложение INSTEAD OF нельзя использовать в триггерах DDL.

  • Предложение [NOT FOR REPLICATION] указывает, что SQL Server не должен вызывать триггер, когда агент репликации изменяет таблицу.
  • sql_statements указывает действие, которое должно выполняться при возникновении события.

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

ВСТАВЛЕН Таблица

 УДАЛЕНО Таблица

Содержит новые строки для вставки во время события INSERT или UPDATE.

 Содержит копии затронутых строк во время события DELETE или UPDATE.

Нет записей для операторов DELETE.

 Нет записей для операторов INSERT.

Давайте создадим триггер, который срабатывает при операциях INSERT, UPDATE и DELETE в таблице Employee .
Для этого создайте новую таблицу EmployeeLog для регистрации всех операций, выполненных в таблице Employee .

 СОЗДАТЬ ТАБЛИЦУ EmpLog (
LogID int IDENTITY(1,1) NOT NULL,
EmpID int NOT NULL,
Операция nvarchar(10) НЕ NULL,
UpdatedDate Дата и время НЕ NULL
)

В приведенной выше таблице LogID — это серийный номер с автоматическим увеличением, UpdatedDate — это дата обновления таблицы Employee .
9Столбец 0005 Operation хранит тип операции, выполненной в таблице; либо «ВСТАВИТЬ», «ОБНОВИТЬ», либо «УДАЛИТЬ».

ДЛЯ триггеров

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

Следующий триггер FOR срабатывает при операции INSERT на Сотрудник таблица.

 СОЗДАТЬ ТРИГГЕР dbo. trgEmployeeInsert
ON dbo.Employee
ДЛЯ ВСТАВКИ
КАК
    ВСТАВИТЬ В dbo.EmpLog(EmpID, Operation, UpdatedDate)
    SELECT EmployeeID, 'INSERT', GETDATE() FROM INSERTED; --виртуальная таблица ВСТАВЛЕНА

Приведенное выше действие создаст триггер tgEmployeeInsert в папке -> Triggers, как показано ниже.

Триггеры в SQL Server

Выполните инструкции select в таблицах Employee и EmpLog , чтобы просмотреть существующие записи.

Ниже приведена таблица EmpLog .

Теперь выполните следующую инструкцию INSERT, которая активирует триггер tgEmployeeInsert .

 INSERT INTO Сотрудник (Имя
           ,Фамилия
           ,Электронная почта
           ,Телефон
           ,Дата приема на работу
           ,Идентификатор менеджера
           ,Зарплата
           ,идентификатор отдела)
     ЗНАЧЕНИЯ('Маниша'
           , "Датт"
           ,'[электронная почта защищена]'
           ,6799878453
           ,'07. 11.2015'
           ,5
           ,50000
           ,20)

Приведенное выше действие вставит новую строку в таблицу Сотрудник , как показано ниже.

tgEmployeeInsert будет запущен и вставит строку в таблицу EmpLog , как показано ниже.

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

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

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

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

Например, следующий триггер будет запускаться после каждого оператора UPDATE в таблице Employee .

 СОЗДАТЬ ТРИГГЕР dbo. trgEmployeeUpdate
ON dbo.Employee
ПОСЛЕ ОБНОВЛЕНИЯ
КАК
    ВСТАВИТЬ В dbo.EmpLog(EmpID, Operation, UpdatedDate)
    SELECT EmployeeID, 'ОБНОВЛЕНИЕ', ПОЛУЧИТЬ () ИЗ УДАЛЕНЫХ;

Чтобы проверить этот триггер, выполните следующую инструкцию UPDATE.

 ОБНОВЛЕНИЕ Сотрудник
ЗП = 55000
ГДЕ СотрудникID = 2;

Теперь выберите строки из таблицы EmpLog . 9Триггер 0005 tgEmployeeUpdate должен был вставить новую строку в таблицу EmpLog , как показано ниже.

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

Триггер INSTEAD OF позволяет переопределить операции INSERT, UPDATE или DELETE в таблице или представлении.
Фактические операции DML вообще не выполняются.

Триггер INSTEAD OF DELETE выполняется вместо фактического события удаления в таблице или представлении. В приведенном ниже примере триггера «Вместо удаления» при выполнении команды удаления для таблицы «Сотрудник» в таблице «9» создается новая строка. 0005 Таблица EmpLog сохраняет операцию как «Удалить», но строка не удаляется.

 СОЗДАТЬ ТРИГГЕР dbo.trgInsteadOfDelete
ON dbo.Employee
ВМЕСТО УДАЛЕНИЯ
КАК
    ВСТАВИТЬ В dbo.EmpLog(EmpID, Operation, UpdatedDate)
    ВЫБЕРИТЕ ID сотрудника, 'УДАЛИТЬ', ПОЛУЧИТЬ ДАТУ () ИЗ УДАЛЕННОГО;

Теперь выполните следующий оператор удаления, чтобы проверить приведенный выше триггер.

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

Приведенный выше оператор запускает триггер trgInsteadOfDelete , который вставляет новую строку в таблицу EmpLog вместо удаления строки в таблице Employee .

Триггер INSTEAD OF DELETE работает таким же образом и для массового удаления.
Когда вы запускаете оператор SQL, удаляющий несколько строк, строки не будут удалены, но равное количество строк будет вставлено в EmpLog 9.0006 таблица.

Несколько триггеров

В SQL Server для одного и того же события в таблице можно создать несколько триггеров. Для этих триггеров нет определенного порядка выполнения.

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

 sp_settriggerorder [ @triggername = ] 'имя_триггера',
[ @order = ] 'значение',
[ @stmttype = ] 'тип_оператора',
[ @namespace = { 'БАЗА ДАННЫХ' | 'СЕРВЕР' | НУЛЕВОЙ } ]

Аргументы:

  • Triggername — имя триггера, который нужно заказать.

  • @order = Порядок триггера. Первый, последний или нет

  • @stmttype = тип оператора. INSERT UPDATE, DELETE, LOGON или любое событие оператора TSQL, указанное в событиях DDL.

  • @namespace указывает, был ли триггер DDL создан в базе данных или на сервере.

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

 sp_settriggerorder @triggername= 'dbo.trgEmployeeUpdate',
    @order='Первый',
    @stmttype = 'ОБНОВЛЕНИЕ';

Создание триггера DML с помощью SSMS

Шаг 1. Откройте SSMS и войдите на сервер базы данных. В обозревателе объектов разверните экземпляр базы данных и выберите базу данных, в которой вы хотите создать триггер.

Шаг 2: Разверните таблицу, в которой вы хотите создать триггер. Щелкните правой кнопкой мыши папку «Триггеры» и выберите «Новый триггер». Синтаксис CREATE TRIGGER для нового триггера откроется в редакторе запросов.

Шаг 3: В меню «Запрос» нажмите «Указать значения для параметров шаблона».

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

Шаг 4. В редакторе запросов введите операторы SQL для триггера в разделе с комментариями — вставьте здесь операторы для триггера.

Шаг 5: Вы можете проверить синтаксис, щелкнув Parse в меню Query.

Шаг 6: Нажмите «Выполнить», чтобы создать триггер.

Шаг 7: Обновите таблицу. Новый триггер будет создан в папке Triggers таблицы.

Таким образом, в SSMS можно создавать триггеры.

Введение в триггеры в SQL Server

Введение

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

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

Триггер SQL — это объект базы данных, который срабатывает, когда в базе данных происходит событие. Например, мы можем выполнить SQL-запрос, который будет «делать что-то» в базе данных, когда в таблице базы данных происходит изменение, например, когда запись вставляется, обновляется или удаляется. Например, триггер может быть установлен на вставку записи в таблицу базы данных. Например, если вы хотите увеличить количество блогов в таблице «Отчеты» при вставке новой записи в таблицу «Блоги», мы можем создать триггер в таблице «Блоги» на INSERT и обновить таблицу «Отчеты», увеличив количество блогов до 1.

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

Существует два типа триггеров:

  1. Триггер DDL
  2. Триггер DML

Триггеры DDL в SQL Server

Триггеры DDL запускаются в ответ на командные события DDL (язык определения данных), которые начинаются с Create, Alter и Drop, например Create_table, Create_view, drop_table, Drop_view и Alter_table.

Код триггера DDL 

 создание триггера безопасности
в базе данных
для
создать_таблицу, изменить_таблицу, удалить_таблицу
как
print'вы не можете создавать, удалять и изменять таблицы в этой базе данных'
откат;

Когда мы создаем, изменяем или удаляем любую таблицу в базе данных, появляется следующее сообщение:

Триггеры DML в SQL Server

Триггеры DML запускаются в ответ на события команды DML (язык манипулирования данными), которые начните с «Вставить», «Обновить» и «Удалить». Например, insert_table, Update_view и Delete_table.

 создать триггер глубоко
на эмп
для
вставить, обновить, удалить
как
print'вы не можете вставлять, обновлять и удалять эту таблицу i'
откат;

Когда мы вставляем, обновляем или удаляем таблицу в базе данных, появляется следующее сообщение:

Типы триггеров DML

В SQL Server существует два типа триггеров DML.

Триггеры AFTER

Триггеры AFTER выполняются после действия инструкции INSERT, UPDATE или DELETE.