бд / Лекция 8 Триггеры

Лекция 7. Триггеры

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

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

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

SQL Server поддерживает два различных типа триггеров:

- триггеры AFTER 

- триггеры INSTEAD OF. 

Триггеры AFTER

Триггеры AFTER вызываются после выполнения команды, которой они назначены, а триггеры INSTEAD OF вызываются вместо команды.

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

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

Триггеры INSTEAD OF

Триггеры INSTEAD OF заменяют команду, для которой они объявлены. Подобно триггерам AFTER, можно определять триггеры INSTEAD OF для команд INSERT, UPDATE или DELETE. Один триггер может быть применен к нескольким командам.

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

Триггеры INSTEAD OF несовместимы с каскадными изменениями связанных данных. Вы не можете объявить триггер INSTEAD OF DELETE или INSTEAD OF UPDATE для таблицы, внешний ключ которой затрагивается действиями удаления (DELETE) или модификации (UPDATE).

Когда триггер срабатывает и начинает выполняться, во время его выполнения существуют две специальные таблицы - INSERTED и DELETED. В них находятся записи, соответственно добавляемые или удаляемые. Если триггер вызывается из команды DELETE, таблица удаления будет содержать строки, которые были удалены из таблицы. При вызове из команды INSERT таблица вставки будет содержать копию новых (вставляемых) строк. Физически оператор UPDATE представляет собой последовательное выполнение команды удаления DELETE и вставки INSERT, так что таблица удаления будет содержать старые значения, а таблица вставки – новые значения. Вы можете обращаться к содержимому этих таблиц из триггера, но вы не можете изменять их.

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

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

Даже, если операция реально не затрагивает ни одной записи, как, например, в случае DELETE FROM PAYS WHERE 1=2 триггер все равно сработает, @@ROWCOUNT будет равен нулю, а таблица DELETED пуста.

Формирование триггера выполняется с помощью команды CREATE TRIGGER. Синтаксис команды приведен ниже:

CREATE TRIGGER [owner.]trigger_name

ON [owner.]table_name

FOR | AFTER | INSTEAD OF {INSERT, UPDATE, DELETE}

[WITH ENCRYPTION]

AS sql_statements

Использование функции UPDATE

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

UPDATE (имя_столбца)

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

Пример: Создадим триггер на обновление, добавление записей в таблицу Student, который помещает в поле lastEdit дату последнего редактирования (добавления) записи.

CREATE TRIGGER StudentLastEdit

ON Student

FOR INSERT, UPDATE

AS

UPDATE Student

SET lastEdit=getDate()

FROM Student, inserted

WHERE Student.idSt= inserted.idSt

print 'Дата последнего редактирования изменена'

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

insert into Student (nameSt, sNameSt) values ('Анна', 'Иванова')

Результат выполнения запроса на вставку:

(строк обработано: 1)

Дата последнего редактирования изменена

(строк обработано: 1)

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

CREATE TRIGGER [dbo].[StudentDel]

ON [dbo].[Student]

INSTEAD OF DELETE

AS

DELETE FROM Student

WHERE idSt in (SELECT idSt FROM deleted WHERE numZach IS NULL)

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

CREATE TRIGGER StudUpdate

ON Student

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

IF UPDATE(dateZach)

BEGIN

RAISERROR('Нельзя изменять дату зачисления',10,1)

ROLLBACK

END

END

GO

SET NOCOUNT { ON | OFF } - Запрещает вывод количества строк, на которые влияет инструкция Transact-SQL или хранимая процедура, в составе результирующего набора

 

RAISERROR(‘Сообщение’, степень_серьезности, состояние) – формирование сообщений об ошибках.

 

Параметр “Сообщение” – это текст, содержащий описание ошибки

 

Параметр “Уровень серьезности” – уровень серъезности ошибки. Значение от 0 до 18 может указать любой пользователь. Уровни серьезности от 19 до 25 могут быть указаны только членами предопределенной роли сервера sysadmin и пользователями с разрешениями ALTER TRACE. Для уровней серьезности от 19 до 25 требуется параметр WITH LOG..

 

Параметр «Состояние» - целое число от 0 до 255.