Ане. МЕТОДИЧКА Full. Методические указания по выполнению лабораторных работ по дисциплине Базы и банки данных
 Скачать 1.71 Mb.
|
«Триггеры»Цель: изучить триггеры и научиться самостоятельно создавать их. Триггер – это специальный тип хранимых процедур, который запускается автоматически при выполнении тех или иных действий с данными таблицы. Каждый триггер привязывается к конкретной таблице. Существует три типа триггеров в зависимости от команд, на которые они реагируют: Триггеры на вставку. Триггеры на обновление. Триггеры на удаление. Для одной таблицы допускается создание нескольких однотипных триггеров. Для создания триггера используется следующая команда Transact SQL: CREATE TRIGGER trigger_name ON TABLE [WITH ENCRYPTION] {FOR {[DELETE] [,] [INSERT] [,] [UPDATE]} [WITH APPEND] [NOT FOR REPLICATION] AS sql_statement [...n] } Trigger_name – задает имя триггера, с помощью которого он будет распознаваться хранимыми процедурами и командами Transact SQL. Имя триггера должно быть уникальным в пределах БД. TABLE – имя таблицы БД, к которой будет привязан триггер. WITH ENCRYPTION – при указании этой опции сервер выполняет шифрование кода триггера. [DELETE] [,] [INSERT] [,] [UPDATE] – эта конструкция определяет, на какие автоматы будет реагировать триггер. При создании триггера должно быть указано хотя бы одно из этих ключевых слов, допускается создание триггера, реагирующего на две или три команды. WITH APPEND – указание этого ключевого слова требуется для обеспечения совместимости с более ранними версиями SQL-сервер. NOT FOR REPLICATION – запрещает запуск триггера при модификации таблиц с помощью репликации. sql_statement – определяет набор команд, которые будут выполняться при запуске триггера. Второй вариант данной команды: CREATE TRIGGER trigger_name ON TABLE [WITH ENCRYPTION] {FOR { [[INSERT] [,] [UPDATE]} [WITH APPEND] [NOT FOR REPLICATION] AS { IF UPDATE (column) [ { AND/OR } UPDATE(column) ] [..n]} sql_statement [...n]}} FOR { [[INSERT] [,] [UPDATE] – эта инструкция определяет, при выполнении какой команды будет запускаться триггер. IF UPDATE (column) – использование этого параметра позволяет выполнить триггер при модификации конкретной колонки таблицы. AND/OR UPDATE(column) – применение совпадает с предыдущим параметром, если необходимо выполнить запуск триггера при модификации нескольких колонок. Аргумент column задает имя колонки, при модификации которой будет производиться запуск триггера. Ключевое слово AND предписывает запускать триггер только в том случае, если были модифицированы обе колонки, указанные в этой и в предыдущей конструкции. При использовании ключевого слова OR триггер будет выполнен при выполнении изменений в любой из колонок. Допускается использование нескольких конструкций AND/OR UPDATE(column). Для изменения триггера используется команда ALTER TRIGGER. Примеры 1. Триггер, который будет запрещать удаление записей таблицы «Пользование библиотекой», если текущий пользователь не владелец базы данных и если поле «дата выдачи» содержит какое-либо значение. CREATE TRIGGER udalenie /*Обьявляемимятриггера*/ ON Пользование_библиотекой2 /*Указываем имя таблицы, с которой будет связан триггер*/ FOR DELETE /*Указываем операцию, на кот. будет срабатывать триггер (здесь на удаление)*/ AS IF ( SELECT count(*) /*проверяет*/ from Пользование_библиотекой2 /*записи из таблицы «Пользование библиотекой»*/ where Пользование_библиотекой2.дата_выдачи is not null)>0 /*условие проверяет наличие записи в поле «дата выдачи». Если count возвращает значение отличное от нуля (означает, что запись есть) то первое условие IF не выполнено*/ AND (CURRENT_USER <> 'dbo') /*вызывается функция определения имени текущего пользователя и проверяется, владелец ли он*/ BEGIN PRINT 'у вас нет прав на удаление этой записи' /*выдача сообщения о неудаче операции*/ ROLLBACK TRANSACTION /*откат (отмена) транзакции*/ END 2. Проверяет правильность номера отдела. Существует лишь 5 отделов, которым присваиваются номера от 10 до 14 включительно. CREATE TRIGGER dobavlenie ON Отделы FOR INSERT AS DECLARE @@f int /*Объявляем переменную*/ Set @@f=10 /*Присваиваем ей значение*/ IF NOT EXISTS (SELECT * FROM Отделы, inserted WHERE Отделы.Номер_отдела = inserted.Номер_отдела) Set @@f=0 IF EXISTS (SELECT * FROM Отделы, inserted WHERE inserted.Номер_отдела>14 OR inserted.Номер_отдела<10) /*Если номер отдела не >14 или <10, то такой отдел не существует*/ Set @@f=0 /*Меняем значение переменной*/ If @@f=0 /*если f=0, значит были ошибки*/ BEGIN PRINT 'Неверно введены данные' /*выдача сообщения о неудаче операции*/ ROLLBACK TRANSACTION /*откат (отмена) транзакции*/ END /*inserted – временная таблица, куда заносятся добавляемые данные*/ 3. Триггер, который записывает при удалении записей из таблицы «Преподаватели» в отдельную таблицу информацию о дате удаления, пользователе, Перед созданием такого триггера необходимо создать таблицу DeletedItems, куда будет производится запись: CREATE TABLE DeletedItem ( [Читательский_номер] [int] NOT NULL , /*объявляем поля таблицы*/ [имя] [varchar] (25) NULL , [отчество] [varchar] (75) NULL , [фамилия] [varchar] (25) NULL , [должность] [varchar] (35) NULL , [Имя_пользователя] [varchar] (50) NULL , [Дата_удаления] [datetime] NULL ) ON [PRIMARY] Сам триггер выглядит следующим образом; CREATE TRIGGER deletedby ON Преподаватели /*Связываем триггер с таблицей Преподаватели*/ FOR DELETE AS INSERT INTO DeletedItem (Читательский_номер ,имя ,отчество, /*указываем, какие поля нужно вставить*/ фамилия ,должность,Имя_пользователя,Дата_удаления) SELECT Читательский_номер ,имя ,отчество,фамилия, должность, SYSTEM_USER, /*функция определяет текущего пользователя*/ getdate() /*функция возвращает текущую дату*/ FROM deleted /*deleted – временная таблица, куда заносятся удаляемые данные*/ 4. Триггер, который записывает при добавлении записей в таблицы «Сотрудники_Библиотеки» в отдельную таблицу информацию о дате удаления, пользователе. Перед созданием такого триггера необходимо создать таблицу InsertedItemSotrydniki, куда будет производится запись: CREATE TABLE InsertedItemSotrydniki ( [табельный_номер] [int] NOT NULL , /*объявляем поля таблицы*/ [фамилия] [varchar] (25) NULL , [имя] [varchar] (25) NULL , [отчество] [varchar] (75) NULL , [Дата_рождения] [datetime] NULL , [должность] [varchar] (35) NULL , [Имя_пользователя] [varchar] (50) NULL , [Дата_добавления] [datetime] NULL ) ON [PRIMARY] Сам триггер выглядит следующим образом; CREATE TRIGGER infoInsertSotrud ON Сотрудники_библиотеки /*Связываем триггер с таблицей Сотрудники_библиотеки */ FOR INSERT AS INSERT INTO InsertedItemSotrydniki (Табельный_номер , /*указываем, какие поля нужно вставить*/ фамилия ,имя ,отчество,Дата_рождения, должность,Имя_пользователя,Дата_добавления) SELECT Табельный_номер ,фамилия ,имя ,отчество,Дата_рождения,должность, SYSTEM_USER, /*функция определяет текущего пользователя*/ getdate() /*функция возвращает текущую дату*/ FROM inserted /*inserted – временная таблица, куда заносятся добавляемые данные*/ 5. Триггер, который записывает при изменении записей в таблице «Студенты» в отдельную таблицу информацию о дате удаления, пользователе. Перед созданием такого триггера необходимо создать таблицу UpdatedStudents, куда будет производится запись: CREATE TABLE UpdatedStudents ( [читательский_номер] [int] NOT NULL , [фамилия] [varchar] (25) NULL , [Имя_пользователя] [varchar] (50) NULL , [Дата_добавления] [datetime] NULL ) ON [PRIMARY] Триггер выглядит следующем образом: CREATE TRIGGER infoUpdateStudent ON Студенты /*Связываем с таблицей*/ FOR UPDATE AS INSERT INTO UpdatedStudents (Читательский_номер , /*указываем, какие поля нужно вставить*/ фамилия,Имя_пользователя,Дата_добавления) SELECT Читательский_номер ,фамилия, /*указываем откуда*/ SYSTEM_USER, /*функция определяет текущего пользователя*/ getdate() /*функция возвращает текущую дату*/ FROM updated /*updated – временная таблица, куда заносятся данные*/ 6. Триггер запрещает изменять в таблице «Пользование_библиотекой2» запись полей «дата_приема» и «дата выдачи» в воскресенье. ALTER TRIGGER proverka ON Пользование_библиотекой2 FOR UPDATE AS IF UPDATE(дата_приема) /*Проверяет, какое поле обновляется*/ AND datename(dw,GETDATE()) in ('Sunday') /*Проверяет, является ли день воскресеньем*/ BEGIN PRINT 'В воскресение нельзя принимать книги' ROLLBACK TRANSACTION END IF UPDATE(дата_выдачи) /*Проверяет, какое поле обновляется*/ AND datename(dw,GETDATE()) in ('Sunday') /*Проверяет, является ли день воскресеньем*/ BEGIN PRINT 'В воскресение нельзя принимать книги' ROLLBACK TRANSACTION END /* datename(dw,GETDATE()) – GETDATE()получает текущую дату, datename(dw,<дата>) получает название дня недели*/ 7. Триггер, который записывает при удалении записей из таблицы «Сотрудники_библиотеки» в отдельную таблицу информацию о дате удаления, пользователе, Перед созданием такого триггера необходимо создать таблицу DeletedSotrudLibrary, куда будет производится запись: CREATE TABLE DeletedSotrudLibrary ( [Табельный_номер] [int] NOT NULL , /*объявляем поля таблицы*/ [имя] [varchar] (25) NULL , [фамилия] [varchar] (25) NULL , [отчество] [varchar] (75) NULL , [дата_рождения] [datetime] NULL , [должность] [varchar] (35) NULL , [номер_отдела] [int] NOT NULL , [Имя_пользователя] [varchar] (50) NULL , [Дата_удаления] [datetime] NULL ) ON [PRIMARY] Триггер: CREATE TRIGGER deletesotrud ON Сотрудники_библиотеки /*Связываемстаблицей*/ FOR DELETE AS INSERT INTO DeletedSotrudLibrary /*Указываемтаблицу, кудабудетпроизводитьсязапись*/ (Табельный_номер ,имя ,фамилия ,отчество,дата_рождения, /*Пречисляем поля таблицы*/ должность,номер_отдела, Имя_пользователя,Дата_удаления) SELECT /*Причисляем вносимые значения*/ Табельный_номер, имя, фамилия, отчество, дата_рождения, должность, номер_отдела, SYSTEM_USER, getdate() FROM deleted /*deleted – временная таблица, куда заносятся удаляемые данные*/ 8. Триггер, который записывает при добавлении записей в таблицы «Преподаватели» в отдельную таблицу информацию о дате удаления, пользователе. Перед созданием такого триггера необходимо создать таблицу InsertedItemPrepdavateli, куда будет производится запись CREATE TABLE InsertedItemPrepdavateli ( [Читательский_номер] [int] NOT NULL , /*объявляем поля таблицы*/ [имя] [varchar] (25) NULL , [отчество] [varchar] (75) NULL , [фамилия] [varchar] (25) NULL , [должность] [varchar] (35) NULL , [Имя_пользователя] [varchar] (50) NULL , [Дата_удаления] [datetime] NULL ) ON [PRIMARY] Триггер: CREATE TRIGGER infoInsertPred ON Преподаватели FOR INSERT AS INSERT INTO InsertedItemPrepdavateli (Читательский_номер, имя, отчество, фамилия ,должность, Имя_пользователя, Дата_удаления) SELECT Читательский_номер, имя, отчество, фамилия, должность, SYSTEM_USER, getdate() FROM inserted 9. Триггер, запрещающий ввод значения в поле «год_поступления», если оно превышает номер текущего года. CREATE TRIGGER proverka_Studentov ON Студенты FOR INSERT AS DECLARE @@t int /*Объявляем переменную*/ Set @@t=5 /*Присваиваем ей значение*/ IF NOT EXISTS (SELECT * FROM Студенты, inserted WHERE Студенты.год_поступления = inserted.год_поступления) Set @@t=0 IF EXISTS (SELECT * FROM Студенты, inserted WHERE inserted.год_поступления>YEAR(GETDATE())) /*Проверяем, больше ли значения вводимого года, чем у текущего*/ Set @@t=0 /*Если да, то меняем значение переменной*/ If @@t=0 /*если f=0, значит были ошибки*/ BEGIN PRINT 'Неверно введен год поступления' /*выдача сообщения о неудаче операции*/ ROLLBACK TRANSACTION /*откат (отмена) транзакции*/ END 10. Триггер, запрещающий обновление значения в поле «год_отчисления», если оно превышает номер текущего года. CREATE TRIGGER proverka_otchislenia_Studentov ON Студенты FOR UPDATE AS DECLARE @@t int /*Объявляем переменную*/ Set @@t=5 /*Присваиваем ей значение*/ IF NOT EXISTS (SELECT * FROM Студенты, inserted WHERE Студенты.год_окончания = inserted.год_окончания) Set @@t=0 IF EXISTS (SELECT * FROM Студенты, inserted WHERE inserted.год_окончания>YEAR(GETDATE())) /*Проверяем, больше ли значения вводимого года, чем у текущего*/ Set @@t=0 /*Меняем значение переменной*/ If @@t=0 /*если f=0, значит были ошибки*/ BEGIN PRINT 'Неверно введен год отчисления' /*выдача сообщения о неудаче операции*/ ROLLBACK TRANSACTION /*откат (отмена) транзакции*/ END Вопросы: Что такое триггер? Чем хранимая процедура отличается от триггера? С помощью какой команды можно создать триггер? С помощью какой команды можно изменять триггер? Какие стандартные виды триггеров существуют? Опишите структуру триггера. Для чего используется функция ROLLBACK TRANSACTION? Как описываются переменные, используемые в триггером? Как вызвать триггер? Для чего предназначен триггер с оператором FOR INSERT? |