Программирование под MySql(триггеры, хранимые процедуры/функции)

[CoDy]

Всем привет.

Есть следующая задача:

 - отследить изменение таблици(insert/update), причем нужно указать какие поля изменились;

 - в спец поле в таблице вставлять hash всей записи.

Вот мое решение

-- таблица для отслеживания изменений

CREATE TABLE `change`(

  idChange INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

  `table` VARCHAR(50) NOT NULL,

  rowId INT(10) UNSIGNED NOT NULL,

  columnLst VARCHAR(255) NOT NULL,

  PRIMARY KEY (idChange)

);



-- собственно объект наблюдений

CREATE TABLE item(

  idItem INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

  title VARCHAR(45) NOT NULL,

  count TINYINT(4) UNSIGNED NOT NULL,

  price DECIMAL(10, 2) NOT NULL,

  `hash` CHAR(32) DEFAULT NULL,

  PRIMARY KEY (idItem)

);



-- набор тригеров для таблици item

CREATE

TRIGGER after_insert_item

AFTER INSERT

ON item

FOR EACH ROW

BEGIN

  INSERT INTO `Change`

  SET

    `table` = 'Item', `rowId` = new.idItem, `columnLst` = 'title,count,price';

END

$



CREATE

TRIGGER after_update_item

AFTER UPDATE

ON item

FOR EACH ROW

BEGIN

  DECLARE modColumnLst VARCHAR(255) DEFAULT '';



  IF OLD.title <> NEW.title THEN

    SET modColumnLst = 'title,';

  END IF;

  IF OLD.count <> NEW.count THEN

    SET modColumnLst = 'count,';

  END IF;

  IF OLD.price <> NEW.price THEN

    SET modColumnLst = 'price';

  END IF;



  IF modColumnLst <> '' THEN

    INSERT INTO `Change`

    SET

      `table` = 'Item', `rowId` = NEW.idItem, `columnLst` = modColumnLst;

  END IF;

END

$



CREATE

TRIGGER before_insert_item

BEFORE INSERT

ON item

FOR EACH ROW

BEGIN

  SET NEW.hash = MD5(CONCAT(new.idItem, new.title, new.count, new.price));

END

$



CREATE

TRIGGER before_update_item

BEFORE UPDATE

ON item

FOR EACH ROW

BEGIN

  SET new.hash = MD5(CONCAT(new.idItem, new.title, new.count, new.price));

END

$



DELIMITER ;

Вот пример вставки

INSERT INTO `Item`

  (`idItem`,`title`,`count`,`price`)

VALUES

  (null,'fsdfsd',2,23.45),

  (null,'sdfsdf',1,26.45),

  (1,'12312312',2,23.45),

  (2,'—--',1,26.45)

ON DUPLICATE KEY UPDATE

  title = VALUES(title),

  count = VALUES(count),

  price = VALUES(price);



SELECT * FROM `Item`;

SELECT * FROM `Change`;

Есть ли возможность сделать все это более универсально, а не дублировать для каждой таблици?

Например, можно ли как-то пройтись по NEW и OLD, как по массиву или передать его в процедуру? И можно ли узнать имя таблици для которой вызывается триггер?

Edited October 12, 2010 by CoDy

[Veseloff]

А какая цель этого всего? Может, если будет известна конечная цель, то придёт в голову какое-нибудь решение.

[CoDy]

Просто нужно отслеживать изменение таблицы(с указанием полей, которые были изменены, если выполняется update) - все это делается исключительно в целях получения истории изменений - вот собственно и вся цель.

[rus]

оно?

[CoDy]

оно?

Очень интересный материал, не сталкивался с подобными задачами ранее.

Но нет, это не совсем то, мне не нужна история изменений. Мне нужна информация о самом факте изменения, с указанием следующих данных:

- имя таблицы, в которой имело место изменение;

- идентификатор записи, которая пдверглась изменению;

- какие именно поля в записи были изменены.

Для чго это нужно?

Например есть локальная база с таблицей, в которой имеется 20 полей и порядка 50 000 записей. И есть удаленная база с которой нет возможности настроить стандартный механизм репликации.

Я хочу чтобы они обменивались пакетами в XML-формате, уведомляя удаленную базу о произошедших изменениях.Это все будет гоняться

по сети через POST http, например.

Допустим имел факт изменения 100 записей, причем в каждой записи было изменено по 1 полю. При очередном сеансе синхронизации, я лезу в таблицу, которая фиксирует

изменения, и формирую XML-пакет, который содержит данные, которые действительно изменились.

Вот такой доморощенный механизм репликации баз получается.

Что мне не нравится в коде,который указан в первом посте: он не универсальный. Там вся логика заложена в теле триггера. Если таблиц будет больше 10, и в какой-то момент нада

будет поправить логику сбора данных о изменениях, это будет запара.

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

В частности возникли такие проблемы:

- определить имя таблицы, для которой вызван триггер;

- не понятно что такое "ключевые слова" OLD и NEW, которые предоставляют доступ к вставляемым и обновляемым данным и можно ли из передать в процедуру.

Да и литературы оч. мало по этой теме ((.

[Veseloff]

Вот тут и тут достаточно понятно всё описано. А насчёт «определить имя таблицы, для которой вызван триггер», так вообще непоянтно, что тут может вызывать затруднения — вы же создаёте триггер на дествие в конкретной табличке (ON item), так что её имя вы по-умолчанию знаете. А так, конечно, извращение какое-то. Может, чтоит поискать какие-то другие решения задачи? Что не даёт «настроить стандартный механизм репликации»?

[CoDy]

 

А так, конечно, извращение какое-то. Может, чтоит поискать какие-то другие решения задачи? Что не даёт «настроить стандартный механизм репликации»?

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