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

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

Для базы данных Library создадим три триггера:

1. триггер типа UPDATE печатает определенный текст при каждой модификации таблицы Books:

--создание триггера TR_Print_Update

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'Print_Update' AND type = 'TR')

DROP TRIGGER Print_Update

GO

CREATE TRIGGER TR_Print_Update

ON Books

FOR UPDATE

AS

PRINT 'The Books table was updated'

GO

Чтобы проверить работу триггера, выполним модификацию строки:

UPDATE Books

SET Mockery=145

WHERE Year_Edition='2005'

Будет возвращено сообщение The Books table was updated (1 row(s) affected), так как в результате выполнения оператора UPDATE был запущен триггер. В данном триггере мы задали вывод сообщения, чтобы можно было увидеть работу триггера;

2. триггер типа DELETE будет сохранять все строки, удаленные из таблицы Books_Delivery, в таблицу Books_Delivery_Backup для последующего анализа данных. Для реализации триггера используем программу приведенную ниже:

-- создание таблицы Books_Delivery_Backup

CREATE TABLE Books_Delivery_Backup

( Subscriber int

FOREIGN KEY REFERENCES Subscriber(Subscriber_ID)ON DELETE NO ACTION,

Book_ID nchar(6) not null

FOREIGN KEY REFERENCES Books(Book_ID) ON DELETE NO ACTION,

Date_delivery date not null,

Date_return date not null,

Librarian int not null

FOREIGN KEY REFERENCES Librarian(Librarian_ID) ON DELETE NO ACTION

)

ON USERS

--создание триггера

IF EXISTS (SELECT name

FROM sysobjects

WHERE name = 'TR_Books_Delivery_Backup' AND type = 'TR')

DROP TRIGGER TR_Books_Delivery_Backup

GO

CREATE TRIGGER TR_Books_Delivery_Backup

ON Books_Delivery

FOR DELETE

AS

INSERT INTO Books_Delivery_Backup

SELECT * FROM deleted

GO

Отметим, что резервной таблице присвоены те же имена колонок и те же типы данных, что и в исходной таблице. Изначально таблица Books_Delivery_Backup не содержит записей, она будет заполняться по мере удаления записей из таблицы Books_Delivery;

3. триггер DELETE для таблицы Subscriber будет выводить информацию о попытках удаления и количестве удаляемых строк:

CREATE TRIGGER TR_Subscriber_Del

ON Subscriber

FOR DELETE AS

PRINT 'Popitka udalenia '+STR(@@ROWCOUNT)+' strok in table Subscriber'

PRINT 'User '+CURRENT_USER

IF CURRENT_USER<>'dbo'

BEGIN

PRINT 'Udalenie zapresheno'

ROLLBACK TRANSACTION

END

ELSE

PRINT 'Udalene razresheno'

Данный триггер будет выводить информацию о количестве строк, которое пытается удалить пользователь, и имя пользователя, выполнившего команду DELETE. Если пользователь не 'dbo', то удаление запрещается и выдается соответствующее предупреждение.

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

--добавление строки

INSERT INTO Subscriber

VALUES ('13000','Aglullina','Liliya','Rafikonvna','02-02-1990','Gremychinsk, Vostochnaya 6-3','570402','Perm Agricultural Academy');

--удаление строки

DELETE FROM subscriber WHERE Subscriber_ID='13000'

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

Popitka udalenia 1 strok in table Subscriber

User dbo

Udalene razresheno

(1 row(s) affected)