.

Работа с триггерами Mysql

Немного теории…

«Клиент-сервер (англ. Client-server) — вычислительная или сетевая архитектура, в которой задания или сетевая нагрузка распределены между поставщиками услуг, называемыми серверами, и заказчиками услуг, называемыми клиентами. Физически клиент и сервер — это программное обеспечение.» Википедия.

Работа клиенто-серверных приложений подразумевает распределение обработки информации между клиентом и сервером.  И тут начинается самое интересное… В связи с тем, что очень часто соединение клиента с сервером  медленное, то лучше на мой взгляд, обрабатывать информацию на сервере.
Есть еще одна причина в пользу обработки информации на сервере. Представим ситуацию у нас несколько офисов  (отделений одной фирмы)  и централизованная база данных.  Каждый день большим потоком каждый офис заносит записи в базу. Задача состоит в том, чтобы каждой записи присваивался id  который состоит  из двух частей –(XXXYYYYYY),
где XXX -  трехзначный код офиса, с которого прилетает запись в базу данных;
YYYYYY – шестизначный порядковый номер, записи причем именно для этого офиса.
Первая реализация (не правильная).  Формирование id происходит на стороне клиента, а значения для каждого кода будем хранить в справочнике (табличка в которой хранится связь кода и текущий порядковый номер) в базе.
У данной реализации есть ощутимый минус… Например, прежде чем передать id для записи строки, вы проверяете если такое уже в базе (чтоб не дублировать id). Но вот проблема, если клиентов много, высока вероятность, что запросы на проверку будут выполнены почти одновременно, даже с разницей в несколько секунд. Например, в СУБД MySql запросы выполняются в порядке очереди, которой они проходят на сервер. Возникнет ситуация когда один id прошел проверку, но еще не добавлен в таблицу с id, а другой клиент проверяет точно такой же id. Как следствие - у двух записей будет один и тот же id.
Однако связь между клиентом и сервером не всегда будет устойчивая,  такое положение тоже может внести свои коррективы в нашу ситуацию.
И так мы приходим к тому, что неплохо бы если этот id генерировался на сервере.
И тут нам на помощь проходят триггеры.

Триггеры в Mysql. Что это такое и с чем едят?

Триггер — это хранимая процедура, которая не вызывается непосредственно, а исполняется при наступлении определенного события (вставка, удаление, обновление строки).
Поддержка триггеров в MySQL началась с версии 5.0.2 (Хабар)
Данное определение понятно описывает, что такое  - ТРИГГЕР В MySql. Думаю примеров написания  триггеров можно много найти в интернете. Но на самом деле они не так нужны, современные  менеджеры  позволяют создавать триггеры на «лету», то есть вам нужно только уметь писать наполнение триггера.
Ну и самое интересное, область применения триггеров безгранична, но чаще всего его предлагают применяться в качестве системы ведения логов. Здесь мы рассмотрим  разные области применения этого мощного инструмента – ТРИГГЕРА.

Генерирование id. Практика написания первого триггера.

Вначале определимся с алгоритмом генерирования id. Процесс генерирования id можно разложить на следующие операции.

  1. Получаем текущий номер для определенного кода офиса из справочника.
  2. Увеличиваем текущий номер на единицу и проверяем этот id в базе.
  3. Если проверка пройдена, то заносим это id в добавляемую строку.
  4. После обновляем запись в справочнике.

Представим данный алгоритм в виде схемы:

Схема работы тригерра
Схема работы тригерра

Вот изящность данного решения - вся нагрузка ложится на сервер.
Вот пример кода триггера:

CREATE
	DEFINER = 'root'@'%'
TRIGGER Rosno_test_Slava.data_base_zas_insert
	BEFORE INSERT
	ON Rosno_test_Slava.data_base_zas
	FOR EACH ROW
Begin  ##Объявление переменных var
DECLARE id_un char(9) DEFAULT '0';  ## Уникальный id
DECLARE prz_un char(3) DEFAULT '0';  ##Код  офиса
DECLARE numer_un char(6) DEFAULT '0';  ## Номер
DECLARE dl_numera int DEFAULT 0;  ##Длина строки номера
DECLARE col_row int DEFAULT 1; ##Количество строк 

set prz_un = new.prz;  ##Присвоение переменой значение из конструкции INSERT

select numer into numer_un from sp_id where prz=prz_un;  ##Запросом записываем результат в переменую
set numer_un=numer_un+1;  ##Увеличиваем номер на 1
set dl_numera:=Length(numer_un); ##Длина строки с номером
set dl_numera:=6-dl_numera; ##Сколько лидирующих нулей
set numer_un:=CONCAT (mid('000000',1,dl_numera),numer_un);   ##Получаем строку номера 

set id_un=CONCAT (prz_un,numer_un);   ##Склеиваем код офиса и  номер с лидирующими нулями

while not col_row=0 do  ##Выполняем цикл пока переменная не станет равной 0 

 select count(id) into col_row  from data_base_zas where id=id_un;  ##Проверяем кол-во таких id уже в базе
 if col_row<>0 then
  set numer_un:=numer_un+1;
  set dl_numera:=Length(numer_un);
  set dl_numera:=6-dl_numera;
  set numer_un:=CONCAT (mid('000000',1,dl_numera),numer_un);
  set id_un=CONCAT (prz_un,numer_un);
 end if;

end while;

update sp_id set numer=numer_un where prz=prz_un; ##Обновляем справочник
set new.id=id_un; ## Заполняем поле полученным уникальным id

end

Разберем описанный код подробнее. Объявление переменных «DECLARE col_row int DEFAULT 1;»,
где DECLARE – ключевое слово после, которого начинается объявление новой переменной; col_row – имя переменной; int – тип данных (int -Integer); DEFAULT 1 – не обязательная часть конструкции, здесь мы задаем значение переменой по умолчанию (1).
Set – это когда «передать,записать» значение в переменную.
Select «Поле» into «Переменная» From «Таблица» Where «Условие» - конструкция описывает метод передачи значения из любого поля в определенную переменную.
New.«Поле» - Это как глобальные переменные, в общем, в данном случае, это поля которые можно заполнять или изменять.
Примечание*: Для операции update еще добавляется такое понятие как старое значение old. Но об этом расскажу чуть ниже.
В завершение этого пункта, приведу пример таблицы справочника:

CREATE TABLE sp_id (
  id int(6) NOT NULL AUTO_INCREMENT,
  prz int(3) DEFAULT NULL,
  numer char(6) DEFAULT NULL,
  PRIMARY KEY (id),
  INDEX prz_numer (prz, numer)
)
ENGINE = INNODB
AUTO_INCREMENT = 17
AVG_ROW_LENGTH = 1024
CHARACTER SET cp1251
COLLATE cp1251_general_ci
COMMENT = 'Справочник уникальных id';
Таблица справочник sp_id
Таблица справочник sp_id

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

История изменений…Проще простого!

Мы разобрались, как с помощь триггера генерировать уникальный id – это было описание конструкции INSERT. А что можно сделать с UPDATE?
Однажды была поставлена задача: остро проявилась необходимость вести журнал изменений записей в базе. Да именно журнал, а не лог. Лог у меня был и он велся средствам программы: после выполнения запроса, в лог писалась строка формата «КТО, КОГДА, ЧТО СДЕЛАЛ». Простенький такой лог, который не раз меня спасал при разборе полетов: куда дели документы или кто это натворил.
Так почему журнал? Журнал – это таблица в которую сохраняется вся запись до ее обновления в основной таблице. Хочу сразу оговориться: данная реализация не тянет на «гениальную мысль», но зато она помогает понять механизм работы. Можете доработать по собственному желанию.
Сначала разберем алгоритм работы:
1. Начинаем выполнять конструкцию UPDATE.
2. До выполнения конструкции старые данные переносим в табличку истории изменений.
3. Когда выполнили все действия, выполняем обновление записи.
Собственно код триггера:

CREATE
	DEFINER = 'root'@'%'
TRIGGER data_base _up
	BEFORE UPDATE
	ON data_base
	FOR EACH ROW
begin
DECLARE date_log datetime default '1900-01-01 00:00:00';

set date_log = now(); 

insert into history (Data_Up,id,FAM,IM,OT,DR,W,prz) values (date_log,old.id, old.FAM, old.IM, old.OT, old.DR, old.W, old.prz);
  

Ну думаю, тут все понятно. «set date_log = now(); »- задаем значение переменой функцией now()* (возвращает текущую дату и время на сервере).
«old.FAM» - «старое» значение, то есть значение поля еще до обновления.
Вот так просто парой команд мы получаем простенький лог изменений в таблице «data_base». Все пишется в табличку history. Лог можно по желанию дорабатывать. Соль данного подхода в том, что не важно от куда приходит запрос обновления из вашей программы или вы менеджером подключились и что-то исправляли руками – все будет сохранено.
На заметку, триггер можно назначить на любое из действий (INSERT, UPDATE, DELETE). Никто вам не запрещает написать триггер на удаление записи с определенной таблицы. Таким образом, важные данные никогда не пропадут.

Читайте, применяйте и комментируйте. С уважением, ваш Shinobi.
P.S. Нравится материал. Делитесь в твитере, лайкайте в контакте и так далее. Хочется выразить благодарность - кликай по рекламе. Все спасибо))

1 комментарий

  1. Бабайка

    Слав, триггеры не всегда спасут. Посмотри в модуль смс. Там используется функция генерации ид. Для того что бы отдать этот ид клиенту. Бывает такое что этот ид нужен клиенту. Но статья норм так;-) На мой взгляд пример для статьи не очень удачен в виде поля ид. 😉

    Ответить

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *