Файл: Практикум направление подготовки 09. 03. 03 Прикладная информатика Уровень высшего образования Бакалавриат.docx

Модифицируйте процедуру «Распродажа» с учетом того, что у нас может быть несколько товаров с минимальным количеством продаж.

3. 
   Создайте и запустите следующие хранимые процедуры:
   

1. 
   Создайте и запустите процедуру, которая в качестве параметра получает должность и печатает фамилии сотрудников, занимающих эту должность.
   
2. 
   Создайте и запустите процедуру, которая в качестве параметра получает номер счета и выводит сведения о платежах (номер, дата, сумма) вносимых по данному счету.
   
3. 
   Создайте и запустите процедуру, которая выполняет поиск самого дорогого товара и уценяет его. Величину уценки в процентах задавайте в качестве параметра.
   
4. 
   Создайте и запустите процедуру для назначения нового начальника отдела. Отдел и начальника задавайте параметрами.
   
5. 
   Создайте и запустите процедуру для перевода сотрудника на другую должность. Процедура в качестве параметров получает фамилию и новую должность сотрудника.
   
6. 
   Создайте процедуру для изменения названия отдела. Старое и новое названия задаются в качестве параметров.
   
7. 
   Создайте процедуру для начисления пени в размере 10 % от суммы выставленного счета для всех счетов, у которых платежи (payment_date) были внесены позже установленного контрактом срока (bill_term).
   

---

Практическое занятие 11. Использование триггеров. Агрегация данных с использованием триггеров

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

Задачи:

1. 
   Разобрать учебные примеры разработки триггеров к разработанной ранее базе данных «Продажи».
   
2. 
   В соответствии с заданием написать и проверить триггеры к БД «Продажи».
   

1. Общие сведения о триггерах

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

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

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

Все производимые триггером модификации данных рассматриваются как одна транзакция. В случае обнаружения ошибки или нарушения целостности данных происходит откат этой транзакции.

Момент запуска триггера определяется с помощью ключевых слов BEFORE (триггер запускается до выполнения связанного с ним события; например, до добавления записи) или AFTER (после события).

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

Оператор CREATE TRIGGER позволяет создать новый триггер и имеет следующий синтаксис:

CREATE

[DEFINER = { user | CURRENT_USER]

TRIGGER имя_триггера время_триггера событие_триггера

ON имя_таблицы FOR ЕАСН ROW

тело_триггера

Конструкция время_триггера указывает момент выполнения триггера и может принимать два значения:

BEFORE – действия триrrера производятся дo выполнения операции изменения таблицы;

AFTER – действия триrrера производятся после выполнения операции изменения таблицы.

---

Конструкция событие_триггера может принимать значения INSERT, UPDATE и DELETE.

Идентификаторы OLD и NEW означают старое и новое значение изменяемых данных.

Перед командой CREATE в MySQL следует определить пользовательский разделитель. Разделитель определяется при помощи команды DELIMITER, за которой следует символ разделителя. Разделитель необходим для того, чтобы MySQL рассматривал последующие строки, как одну команду, пока не достигнет пользовательского разделителя.

Команда DELIMITER используется для изменения стандартного разделителя команд MySQL – точки с запятой «;». Поскольку операторы внутри подпрограмм (функций, хранимых процедур или триггеров) заканчиваются точкой с запятой (;), для обработки их как составного оператора используется DELIMITER. Если он не определен при использовании разных подпрограмм в одном файле или командной строке, это приведет к синтаксической ошибке.

3. Примеры использования триггеров

Пример 1.

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

Создайте триггер для решения поставленной задачи:


Для просмотра и редактирования созданного триггера откройте таблицу k_protokol в режиме работы со структурой:


Далее откройте вкладку Triggers и выделите нужный триггер:


Протестируйтетриггер.

Предварительно посмотрите информацию о счете №9:


Далее добавьте новую строку в протокол этого счета: добавьте 1 единицу товара с номером 1 и ценой 1000 р. (цена хранится в таблице k_price).

В результате срабатывания триггера информация о счете № 9 должна измениться:

---

Общая сумма счета увеличилась на 1000 р.

Отобразите таблицу протокола счетов:


В добавленной строке с номером товара 1 цена заполнилась автоматически, из прайс-листа.
Пример 2.

Создайте триггер для операции удаления из той же таблицы. При удалении строки из протокола счета должна уменьшаться общая сумма счета.


Протестируйте триггер. Предварительно посмотрите содержание таблиц до выполнения операции удаления:





Теперь удалите из протокола счетов информацию о товаре с номером 5:


Снова посмотрите содержимое таблицы k_bill.



Общая сумма счета уменьшилась до 1000 р. (11000 р.-2*5000 р.)




При необходимости удалить триггер используйте команду DROP TRIGGER:

Drop trigger NAME;

При необходимости удалить таблицу используйте команду DROP TABLE:

Drop table k_bill_archive;
4. Напишите и проверьте триггеры к БД «Продажи»:
1. Создайте таблицу k_staff_archive, в которой будет храниться информация об уволенных сотрудниках.

---

CREATE TABLE k_staff_archive (

staff_num INT NOT NULL AUTO_INCREMENT,

staff_name VARCHAR(45) NOT NULL,

staff_post VARCHAR(45) NULL DEFAULT NULL,

staff_hiredate DATE NULL DEFAULT NULL,

staff_termdate DATE NULL DEFAULT NULL,

k_dept_dept_num INT NULL DEFAULT NULL,

PRIMARY KEY (staff_num));

Создайте и протестируйте триггер на удаление (DELETE), который добавит данные об уволенном сотруднике в таблицу k_staff_archive перед его удалением из таблицы k_staff. Датой увольнения автоматически становится текущая дата.
2. Создайте и протестируйте триггер на удаление (DELETE), который перед удалением фирмы «Дельта» из таблицы k_firm удалит все связанные с нею контракты из таблицы k_contract.

DELETE FROM k_firm WHERE firm_name = 'Дельта';
3. Создайте и протестируйте триггер на удаление (DELETE), который перед удалением отдела гарантийного обслуживания из таблицы k_dept переведет сотрудников в таблице k_staff из этого отдела в отдел маркетинга.

DELETE FROM k_dept WHERE dept_name = 'Отдел гарантийного обслуживания';
4. Создайте и протестируйте триггер на добавление (INSERT), который после добавления новой фирмы «Эпсилон» в таблицу k_firm переведет на нее контракты в таблице k_contract, заключенные раннее с фирмой «Бета».

INSERT INTO k_firm (firm_name, firm_addr, firm_phone) VALUES ('Эпсилон', 'Глазго', NULL);
5. Создайте и протестируйте триггер на добавление (INSERT), который после добавления нового сотрудника в таблицу k_staff автоматически заполняет дату приёма на текущую дату, где её не заполнили (NULL). Делается это для автоматизации.

INSERT INTO k_staff (Staff_Name, Staff_Post) VALUES ('Орлов', 'Бухгалтер');
6. Создайте и протестируйте триггер на добавление (INSERT), который после добавления нового счёта в таблицу

---