Mathmech wiki

30 Триггеры

4 min read

Триггер на таблицу

определение

Синтаксис

Триггер на вход

Синтаксис

Как написать триггер

Конспект

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

События

Существуют три события, на которые могут реагировать триггеры:

  • Вставка INSERT
  • Обновление UPDATE
  • Удаление DELETE Идеален для обеспечения целостности и согласованности данных, но работает медленнее, чем стандартные огранчения

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

синтаксис

CREATE TRIGGER trigger_name 
ON { table | view } 
[ WITH ENCRYPTION ] 
{ 
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } 
        [ WITH APPEND ] 
        [ NOT FOR REPLICATION ] 
        AS 
        [ { IF UPDATE ( column ) 
            [ { AND | OR } UPDATE ( column ) ] 
                [ ...n ] 
        | IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask) 
                { comparison_operator } column_bitmask [ ...n ] 
        } ] 
        sql_statement [ ...n ] 
    } 
}

Нельзя создать триггер на системные таблицы

Ограничения

Сервер SQL не позволяет использовать следующие операторы в теле триггера:

  • ALTER DATABASE
  • CREATE DATABASE
  • DISK INIT
  • DISK RESIZE;
  • DROP DATABASE
  • LOAD DATABASE
  • LOAD LOG
  • RECONFIGURE
  • RESTORE DATABASE
  • RESTORE LOG т.е. в них нельзя изменять структуру базы данных

Откат изменений в триггере

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

пример создания триггера

CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples
FOR UPDATE
AS
 ROLLBACK TRANSACTION

Как работают триггеры?

INSERT

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

пример

CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples
FOR INSERT 
AS
 DECLARE @Name varchar(50)
 SELECT @Name=vcName
 FROM inserted
 IF @Name='ВАСЯ'
  BEGIN 
   PRINT 'ОШИБКА'
   ROLLBACK TRANSACTION
  END

DELETE

  • Пользователем выполняется оператор DELETE для добавления записей
  • Сервер сохраняет информацию о запросе в журнале транзакций
  • Вызывается триггер с таблицей deleted
  • Подтверждение изменений и физическое изменение данных Особенности:
  • когда строки добавляются в таблицу deleted, они еще существуют в таблице базы данных
  • для таблицы deleted выделяется память, поэтому она всегда в кэше
  • триггер удаления не выполняется на операцию TRUNCATE TABLE

Пример

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples
FOR DELETE
AS
 IF EXISTS (SELECT *
            FROM deleted
            WHERE vcName='рлр')
  BEGIN 
   PRINT 'ОШИБКА, нельзя удалить этого пользователя'
   ROLLBACK TRANSACTION
  END

Триггеры срабатывают после проверки всех ограничений CHECK и внешних ключей

UPDATE

Обновление - по сути два этапа – удаление и вставка. Поэтому в триггера на обновление 2 таблицы: deleted и inserted

Можно мониторить только некоторые колонки с помощью IF UPDATE:

CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples
FOR UPDATE
AS
 IF UPDATE (vcName)
   PRINT 'Я надеюсь, что вы правильно указали имя'

Если нужно выполнить несколько операторов в триггере, то объедините их с помощью BEGIN и END:

CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples
FOR UPDATE
AS
 IF UPDATE (vcName) 
    OR UPDATE (vcFamil)
    OR UPDATE (vcSurname)
  BEGIN
   PRINT 'Нельзя изменять фамилию, имя и отчество'
   ROLLBACK TRANSACTION
  END

INSTEAD OF

Действия такого триггера выполняются вместо операторов, сгенерировавших триггер

Каждая таблица или просмотрщик ограничены одним триггером INSTEAD OF на каждое событие

Как можно использовать INSTEAD OF?

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

CREATE TRIGGER i_People ON dbo.People
INSTEAD OF INSERT
AS
 BEGIN
  -- Добавление должности
  INSERT INTO tbPosition (vcPositionName)
  SELECT vcPositionName
  FROM inserted i
 
  -- Добавление работника
  INSERT INTO tbPeoples (vcFamil, idPosition)
  SELECT vcFamil, idPosition
  FROM inserted i,tbPosition pn
  WHERE i.vcPositionName=pn.vcPositionName
 END

Обновление представления с помощью триггера

CREATE TRIGGER u_People ON dbo.People
INSTEAD OF UPDATE
AS 
BEGIN
 UPDATE tbPosition
 SET vcPositionName=i.vcPositionName
 FROM tbPosition pn, inserted i
 WHERE i.idPosition=pn.idPosition
 
 UPDATE tbPeoples
 SET vcFamil=i.vcFamil
 FROM tbPeoples pl, inserted i
 WHERE i.idPeoples=pl.idPeoples
END

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

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

Триггеры не должны возвращать результирующих наборов

Хранение истории

Очень часто в базах данных необходимо сохранять историю. Можно делать с помощью триггера

Особенности триггеров

Триггеры могут иметь вложения до 32 уровней

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

Включение рекурсивных вызовов триггеров

ALTER DATABASE FlenovSQLBook SET RECURSIVE_TRIGGERS ON

Recent Notes

Graph View

Backlinks