Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
Работа в mySQL Workbench - Триггеры
Триггеры – это хранимые процедуры специального вида, которые автоматически выполняются при изменении данных с помощью операторов INSERT, UPDATE и DELETE. Триггер создается для определенной таблицы, но может использовать данные других таблиц и объекты других баз данных. Оператор CREATE TRIGGER позволяет создать новый триггер и имеет следующий синтаксис:
CREATE TRIGGER имя_триггера время_триггера событие_триггера ON имя_таблицы FOR ЕАСН ROW тело_триггера
Конструкция время_триггера указывает момент выполнения триггера и может принимать два значения: BEFORE - действия триrrера производятся дo выполнения операции изменения таблицы; AFTER - действия триrrера производятся после выполнения операции изменения таблицы. Конструкция событие_триггера может принимать значения INSERT, UPDATE и DELETE. Идентификаторы OLD и NEW означают старое и новое значение изменяемых данных. Рассмотрим пример триггера вставки, который вызывается при выполнении команды INSERT в таблице протоколов счетов. При добавлении новой позиции в счете нам нужно заново пересчитать его общую сумму:
DELIMITER // -- триггер запускается перед добавлением строки в протокол --счетов CREATE TRIGGER ins_prot BEFORE INSERT ON k_protokol FOR EACH ROW BEGIN DECLARE v_kolvo NUMERIC(6); --количество DECLARE v_bill_num NUMERIC(6); --номер счета DECLARE v_price_num NUMERIC(6); --номер товара DECLARE v_price_sum NUMERIC(9, 2); --цена товара SET v_kolvo=New.kolvo; SET v_bill_num=New.k_bill_bill_num; SET v_price_num=New.k_price_price_num;
IF v_kolvo> 0 THEN -- только если количество > 0 --из прайс-листа получаем цену товара SELECT p.price_sum INTO v_price_sum FROM k_price p WHERE p.price_num=v_price_num; -- обновляем общую сумму счета UPDATE k_bill SET bill_sum=bill_sum+v_kolvo*v_price_sum WHERE k_bill.bill_num=v_bill_num; -- цену товара продублируем в протоколе счета SET New.price_sum=v_price_sum; END IF; END//
Протестируем триггер. Предварительно посмотрим информацию о счете №9:
SELECT * FROM k_bill WHERE bill_num=9;
Теперь добавим новую строку в протокол этого счета: добавляем 1 штуку товара с номером 1 и ценой 1000 р. (цену берем из таблицы k_price).
INSERT INTO k_protokol (kolvo, price_sum, k_price_price_num, k_bill_bill_num) VALUES(1, 0, 1, 9);
Посмотрим, как изменился счет №9:
SELECT * FROM k_bill WHERE bill_num=9;
Общая сумма счета увеличилась на 1000 р. Посмотрим таблицу протокола счетов:
SELECT * FROM k_protokol WHERE k_bill_bill_num=9;
В добавленной строке с номером товара 1 цена заполнилась автоматически, из прайс-листа.
Теперь создадим триггер для операции удаления из той же таблицы. При удалении строки из протокола счета должна уменьшаться общая сумма счета. -- триггер запускается перед удалением строки из протокола --счетов DELIMITER // CREATE TRIGGER del_prot BEFORE DELETE ON k_protokol FOR EACH ROW BEGIN DECLARE v_kolvo NUMERIC(6); -- количество DECLARE v_bill_num NUMERIC(6); -- номер счета DECLARE v_price_sum NUMERIC(9, 2); -- цена товара SET v_kolvo=Old.kolvo; SET v_bill_num=Old.k_bill_bill_num; SET v_price_sum=Old.price_sum;
IF v_kolvo> 0 THEN -- только если количество > 0 -- обновляем общую сумму счета UPDATE k_bill SET bill_sum=bill_sum-v_kolvo*v_price_sum WHERE k_bill.bill_num=v_bill_num; END IF; END//
Протестируем триггер. Сначала посмотрим содержание таблиц до выполнения операции удаления:
SELECT * FROM k_bill WHERE bill_num=9;
SELECT * FROM k_protokol WHERE k_bill_bill_num=9;
Теперь удалим из протокола счетов информацию о товаре с номером 5:
DELETE FROM k_protokol WHERE k_bill_bill_num=9 AND k_price_price_num=5;
Снова посмотрим содержимое таблицы k_bill:
SELECT * FROM k_bill WHERE bill_num=9;
Общая сумма счета уменьшилась до 1000 р.
SELECT * FROM k_protokol WHERE k_bill_bill_num=9;
Задание. Создайте и протестируйте по крайней мере 1 триггер для вашей базы данных. Более подробно о триггерах MySQL можно прочитать в [2, Глава 34 ].
Приложение 1. Сценарий создания базы данных В данном приложении приведен сценарий, автоматически сформированный при создании базы данных из EER-модели.
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `kontora` DEFAULT CHARACTER SET cp1251 COLLATE cp1251_general_ci;
-- ----------------------------------------------------- -- Table `kontora`.`k_staff` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `kontora`.`k_staff` ( `staff_num` INT NULL AUTO_INCREMENT, `staff_name` VARCHAR(45) NOT NULL, `staff_post` VARCHAR(45) NULL, `staff_hiredate` DATE NULL, `staff_termdate` DATE NULL, `K_dept_dept_num` INT NOT NULL, PRIMARY KEY (`staff_num`), INDEX `fk_k_staff_K_dept1` (`K_dept_dept_num` ASC), CONSTRAINT `fk_k_staff_K_dept1` FOREIGN KEY (`K_dept_dept_num` ) REFERENCES `kontora`.`K_dept` (`dept_num` )
ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `kontora`.`K_dept` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `kontora`.`K_dept` ( `dept_num` INT NULL AUTO_INCREMENT, `dept_full_name` VARCHAR(45) NULL, `dept_short_name` VARCHAR(10) NOT NULL, `k_staff_staff_num` INT NOT NULL, PRIMARY KEY (`dept_num`), INDEX `fk_K_dept_k_staff1` (`k_staff_staff_num` ASC), CONSTRAINT `fk_K_dept_k_staff1` FOREIGN KEY (`k_staff_staff_num` ) REFERENCES `kontora`.`k_staff` (`staff_num` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `kontora`.`k_firm` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `kontora`.`k_firm` ( `firm_num` INT NULL AUTO_INCREMENT, `firm_name` VARCHAR(45) NOT NULL, `firm_addr` VARCHAR(45) NULL, `firm_phone` VARCHAR(20) NULL, PRIMARY KEY (`firm_num`) ) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `kontora`.`k_contract` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `kontora`.`k_contract` ( `contract_num` INT NULL AUTO_INCREMENT, `contract_date` DATE NOT NULL, `contract_type` ENUM('A', 'B', 'C') NOT NULL, `k_firm_firm_num` INT NOT NULL, `k_staff_staff_num` INT NOT NULL, PRIMARY KEY (`contract_num`), INDEX `fk_k_contract_k_firm1` (`k_firm_firm_num` ASC), INDEX `fk_k_contract_k_staff1` (`k_staff_staff_num` ASC), CONSTRAINT `fk_k_contract_k_firm1` FOREIGN KEY (`k_firm_firm_num` ) REFERENCES `kontora`.`k_firm` (`firm_num` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_k_contract_k_staff1` FOREIGN KEY (`k_staff_staff_num` ) REFERENCES `kontora`.`k_staff` (`staff_num` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `kontora`.`k_bill` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `kontora`.`k_bill` ( `bill_num` INT NULL AUTO_INCREMENT, `bill_date` DATE NULL, `bill_sum` DECIMAL(9, 2) NULL, `bill_term` DATE NULL, `bill_peni` DECIMAL(6, 2) NULL, `k_contract_contract_num` INT NOT NULL, PRIMARY KEY (`bill_num`), INDEX `fk_k_bill_k_contract1` (`k_contract_contract_num` ASC), CONSTRAINT `fk_k_bill_k_contract1` FOREIGN KEY (`k_contract_contract_num` ) REFERENCES `kontora`.`k_contract` (`contract_num` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB AUTO_INCREMENT = 1;
-- ----------------------------------------------------- -- Table `kontora`.`k_payment` -- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `kontora`.`k_payment` ( `payment_num` INT NULL, `payment_date` DATE NULL, `payment_sum` DECIMAL(9, 2) NULL, `k_bill_bill_num` INT NOT NULL, PRIMARY KEY (`payment_num`), INDEX `fk_k_payment_k_bill1` (`k_bill_bill_num` ASC), CONSTRAINT `fk_k_payment_k_bill1` FOREIGN KEY (`k_bill_bill_num` ) REFERENCES `kontora`.`k_bill` (`bill_num` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `kontora`.`k_price` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `kontora`.`k_price` ( `price_num` INT NULL AUTO_INCREMENT, `price_name` VARCHAR(45) NOT NULL, `price_sum` DECIMAL(9, 2) NULL, `price_type` VARCHAR(1) NULL, PRIMARY KEY (`price_num`) ) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `kontora`.`k_protokol` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `kontora`.`k_protokol` ( `k_bill_bill_num` INT NOT NULL, `k_price_price_num` INT NOT NULL, `kolvo` INT NOT NULL, `price_sum` DECIMAL(9, 2) NOT NULL, PRIMARY KEY (`k_bill_bill_num`, `k_price_price_num`), INDEX `fk_k_bill_has_k_price_k_bill1` (`k_bill_bill_num` ASC), INDEX `fk_k_bill_has_k_price_k_price1` (`k_price_price_num` ASC), CONSTRAINT `fk_k_bill_has_k_price_k_bill1` FOREIGN KEY (`k_bill_bill_num` ) REFERENCES `kontora`.`k_bill` (`bill_num` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_k_bill_has_k_price_k_price1` FOREIGN KEY (`k_price_price_num` ) REFERENCES `kontora`.`k_price` (`price_num` ) ON DELETE NO ACTION
ON UPDATE NO ACTION) ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Приложение 2. Dbforge Studio от Devart Для работы с сервером MySQL существуют альтернативные интерфейсы, позволяющие осуществлять разработку баз данных и администрирование сервера. Один из наиболее интересных вариантов – dbForge Studio от компании Devart. Приведем несколько иллюстраций для наиболее типичных административных функций. |
Последнее изменение этой страницы: 2019-06-19; Просмотров: 1290; Нарушение авторского права страницы