Jump to content
  • 0

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


CoDy
 Share

Question

Всем привет.

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

 - отследить изменение таблици(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 by CoDy
Link to comment
Share on other sites

6 answers to this question

Recommended Posts

  • 0

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

Link to comment
Share on other sites

  • 0

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Link to comment
Share on other sites

  • 0

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

Link to comment
Share on other sites

  • 0

 

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

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue. See more about our Guidelines and Privacy Policy