Работа в 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. Приведем несколько иллюстраций для наиболее типичных административных функций.

⇐ Предыдущая45678910111213Следующая ⇒

Поделиться: