Лаба 3 Создание триггеров
.docЛабораторная работа №3: «Создание триггеров»
Теоретические сведения
Триггер определяет последовательность действий, которые вызываются операциями UPDATE, DELETE и INSERT. Триггер может использоваться совместно с ограничениями ссылочной целостности или с check-ограничениями.
Что позволяют триггеры, в основном:
-
проверять значения ячеек таблицы при UPDATE и DELETE;
-
вызывать изменения в связанных таблицах;
-
информировать об ошибках.
Удобно часть логики приложений перенести в триггеры, что облегчает поддержку приложений.
При создании триггера определяется:
-
таблица, для которой триггер создается;
-
событие, в результате которого триггер6 активизируется:UPDATE, DELETE, INSERT;
-
время активизации триггера: BEFOR, AFTER;
-
глубина действия триггера: однажды для всей таблицы, при модификации каждой строки таблицы.
Триггер может использоваться для изменения других таблиц. Может быть встроен в приложение или выдан в интерактивном режиме. Создание триггера: меню-> CREATE TRIGGER.
<trigger definition> ::=
CREATE TRIGGER <trigger name>
{ NO CASCADE BEFORE | AFTER }
{ INSERT | DELETE | UPDATE [ <update clause> ]}
{ ON <table name> }
{[ REFERENSING <referencing clause> [<,><referencing clause>...] }
{ FOR EACH ROW | FOR EACH STATEMENT }
MODE DB2SQL <triggered action>
<update clause> ::=
OF <column name> [<,><column name>...]
<referencing clause> ::=
{ OLD [AS] <correlation name> }
|{ NEW [AS] <correlation name> }
|{ OLD_TABLE [AS] <table name> }
|{ NEW_TABLE [AS] <table name> }
<triggered action> ::=
{ [WHEN <search condition>] }
{ <query specification>
| BEGIN ATOMIC <query specification><;>[<query specification>,] END}
OLD [AS] <correlation name> – имя старой строки (до оператора модификации);
NEW [AS] <correlation name> – имя новой строки (после оператора модификации);
OLD_TABLE, NEW_TABLE – временная таблица с уникальным именем.
MODE DB2SQL – включает расширение SQL стандарта, в простом SQL команды CREATE TRIGGER нет.
WHEN <search condition> может принимать значение верно (действие триггера выполняется); неверно и неизвестно (действие триггера не выполняется). Без WHEN действие триггера выполняется всегда.
Триггируемый SQL-оператор выполняется при следующих событиях:
а) если указано BEFOR:
-
FULL_SELECT;
-
SET <transition_variable>;
-
SIGNAL SQLSTATE.
б) если указано AFTER:
-
INSERT;
-
UPDATE;
-
DELETE;
-
SIGNAL SQLSTATE;
-
FULL_SELECT.
Если на таблицу определено несколько триггеров и происходит действие, активизирующее эти триггеры, то триггеры выполняются в порядке их создания.
<transition_variable> := { expression | NULL | DEFAULT}
Рассмотрим примеры создания триггера.
Пример 1: создать триггер, который выдаст столбец, при попытке увеличить зарплату служащему >10%. При попытке увеличить зарплату единовременно >10% выдавать сообщение.
CREATE TREGGER TR1
AFTER UPDATE SALARY ON EMPLOYEE
REFERENSING NEW AS N
OLD AS O
FOR EACH ROW MODE DB2SQL
WHEN (N.SALARY-O.SALARY > 0.1*O.SALARY) SIGNAL SQLSTATE ‘75000’ (‘Нельзя увеличить зарплату больше чем на 10%’)
Пример 2: создать два триггера, которые будут отслеживать количество служащих в таблице OFFICE (надо добавить в таблицу OFFICE столбец с количеством служащих в каждом отделе):
Office(deptno(char(3)), count(integer))
-
увеличивать количество служащих отдела в таблице OFFICE, если поступил новый служащий, т.е. при добавлении записи в таблицу EMPLOYEE;
-
уменьшать количество служащих отдела в таблице OFFICE, при увольнении служащего, т.е. при удалении записи из таблицы EMPLOYEE.
--!
CREATE TRIGGER NEW_HIRED
AFTER INSERT ON EMPLOYEE
REFERENCING NEW AS NEWEMP
FOR EACH ROW MODE DB2SQL
UPDATE office SET count = count+1 where deptno=newemp.workdept;
CREATE TRIGGER DELETE_HIRED
AFTER DELETE ON EMPLOYEE
REFERENCING OLD AS OLDEMP
FOR EACH ROW MODE DB2SQL
UPDATE office SET count = count-1 where deptno=oldemp.workdept;
!--
По дефолту в таблице OFFICE выставить все значения count=количеству сотрудников в соответствующем отделе. Например,
update office set count=8 where deptno='D21'
Выполнить команду вставки нового сотрудника например в отдел ‘D21’:
insert into employee (empno,firstnme,midinit,lastname,workdept,edlevel) values('080505','Tom','L','LL','D21',19)
insert into employee (empno,firstnme,midinit,lastname,workdept,edlevel) values('080505','Tom','L','LL','D21',20)
Проверить работу триггера:
select * from office
DEPTNO | COUNT
--------------------------
D21 | 10
Два сотрудника добавились.
Выполнить команду удаления сотрудников из таблицы EMPLOYEE:
delete from employee where firstnme='Tom' and workdept='D21'
Проверить работу триггера:
select * from office
DEPTNO | COUNT
--------------------------
D21 | 8
Два сотрудника удалились.
Контрольные вопросы:
-
Может ли быть использован механизм триггеров для проверки корректности ввода, например, строка, содержащая e-mail, должна обязательно содержать символ ‘@’?
-
Что означает понятие время активации триггера?
-
Что означает понятие триггируемое событие?
-
При помощи какой синтаксической конструкции задается однократное выполнение триггера?
-
При помощи какой синтаксической конструкции можно обратиться к удаляемым строкам?
-
При помощи какой синтаксической конструкции можно обратиться к добавляемым строкам?
-
При помощи какой синтаксической конструкции можно обратиться к модифицируемым строкам?
-
Что означает ключевое слово MODE в операторе CREATE TRIGGER?
-
Если в таблице employee созданы два триггера: а) пересчет премиальных в зависимости от размера зарплаты; б) пересчет премиальных в зависимости от размера премии, - в каком порядке выполняться вычисления?
Задание:
Для указанных таблиц базы “Sample” создать триггеры, определенные в варианте индивидуального задания.
Варианты индивидуальных заданий:
-
Создать триггер в таблице department. При появлении нового отдела в таблице department проверять, менеджеров (mgrno), чтобы каждый менеджер управлял не более чем одним отделом, иначе выдавать сигнал и/или сообщение. Атрибут deptno должен быть неуникальным.
-
Создать триггер в таблице emp_act. При выдаче служащему нового задания в проекте проверять, чтобы новое задание не совпадало по срокам с теми которые уже назначены данному служащему, иначе выдавать сигнал и/или сообщение.
-
Создать дополнительную таблицу bonus(empno, persent_bonus), где persent_bonus – процент премиальных. Создать триггер в таблице bonus, который при изменении процента премиальных для данного служащего будет изменять сумму премиальных в таблице employee.
-
Создать триггер в таблице project. При создании нового проекта проверять, чтобы этот проект принадлежал не более чем одному надпроекту (majproj), иначе выдавать сигнал и/или сообщение. Столбец projno должен быть неуникальным.
-
Создать дополнительную таблицу summa(sales_person, sum), где sum содержит общее количество продаж для данного продавца, вычисленное как сумма значений в колонке sales таблицы sales. Создать триггер в таблице sales. При появлении новой записи в таблице sales, триггер вычисляет общую сумму продаж для данного служащего и записывает значение в таблицу summa.
-
Создать дополнительную таблицу time_rest(projno, time), где time – время, оставшееся до окончания данного проекта. Создать триггер в таблице time_rest, который будет вычислять время до окончания проекта в днях и записывать его в соответствующую ячейку столбца time, при выполнении операции update в таблице emp_act.
-
Создать дополнительную таблицу comm(empno, persent_comm), где persent_comm – процент комиссионных. Создать триггер в таблице comm, который при изменении процента комиссионных для данного служащего будет изменять сумму комиссионных в таблице employee.
-
Создать дополнительную таблицу hire(empno, hire_year), где hire_year – количество отработанных лет. Создать триггер в таблице employee, который при удалении сотрудника из таблицы будет вычислять количество лет, которые он отработал до увольнения.
-
Создать дополнительную таблицу responsibility(respemp, count_resp), где count_resp – количество проектов, за которые данный служащий несет ответственность. Создать триггер в таблице project, который при создании нового проекта вычисляет количество проектов у данного ответственного служащего и изменяет соответствующее значение в таблице responsibility.
-
Создать дополнительную таблицу birthdate(empno, birthdate, month, day), где month – количество месяцев (дробное) до дня рождения, day – количество дней до дня рождения. Создать триггер в таблице employee, который вычисляет количество месяцев и количество дней до дня рождения при выполнении операции insert в таблицу employee.
-
Создать дополнительную таблицу sales_region(region, sum_sales), где sum_sales – общая сумма продаж для данного региона, вычисленная как сумма значение столбца sales таблицы sales. Создать в таблице sales триггер, который увеличивает количество продаж в данном регионе на соответствующее значение при добавлении новой продажи.
-
Создать дополнительную таблицу age(empno, count_age), где count_age – количество лет стажа для данного сотрудника. Создать триггер в таблице employee, который при добавлении нового сотрудника, будет вычислять количество лет стажа у данного сотрудника и заносить в таблицу age. Стаж вычисляется следующим образом: текущий возраст - (7+edlevel).
-
Создать дополнительную таблицу time_activity(empno, actno, time_rest), где time_rest – время, оставшееся до сдачи данного задания служащим. Создать триггер, который будет вычислять время до сдачи задания служащим в днях и месяцах (дробное) и записывать его в в таблицу time_activity, при выполнении операции update в таблице emp_act.
-
Создать триггер в таблице project, который будет контролировать количество подпроектов в главном проекте (majproj). Количество подпроектов не должно превышать 5. Триггер вызывается операцией insert.
-
Создать триггер в таблице employee. При создании нового служащего проверять, чтобы у этого служащего был не более чем один начальник иначе выдавать сигнал и/или сообщение. Столбец empno должен быть неуникальным.