Форум пользователей MySQL

Eugene86 · 23.10.2009 11:51:00

Есть 2 таблицы: material и product.

CREATE TABLE material(

  Name VARCHAR (20) DEFAULT NULL,

  CountM DOUBLE DEFAULT NULL,

  Price DECIMAL (16, 2) UNSIGNED DEFAULT NULL,

  DeliverDate DATETIME DEFAULT NULL,

  id_Material BIGINT (20) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (id_Material)

);

CREATE TABLE product(

  Material VARCHAR (20) DEFAULT NULL,

  CountM FLOAT DEFAULT NULL,

  id_product BIGINT (20) NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (id_product)

);

Таблица Material содержит данные.

|Name |CountM |Price |DeliverDate |id_Material|
|Нитки | 100,0 |25,40 |15 октября 2009 г. |1 |
|Молнии | 200,0 |16,30 |16 октября 2009 г. |2 |
|Ткань синяя |1 000,0 |12,76 |19 октября 2009 г. |3 |
|Ткань синяя |1 000,0 |10,00 |21 октября 2009 г. |4 |
|Нитки | 250,0 |4,00 |22 октября 2009 г. |5 |

При добавлении данных в таблицу product

insert into product(Material ,CountM ) values('Нитки',300)

должно происходить следующее:
if material.Name=product.Name then
данные из таблицы material вычитаются, причём сначала из минимальной даты, затем из более большой.
При приведённом примере insert должно получиться следующее:

|Name |CountM |Price |DeliverDate |id_Material|
|Нитки | 0 |25,40 |15 октября 2009 г. |1 |
|Молнии | 200,0 |16,30 |16 октября 2009 г. |2 |
|Ткань синяя |1 000,0 |12,76 |19 октября 2009 г. |3 |
|Ткань синяя |1 000,0 |10,00 |21 октября 2009 г. |4 |
|Нитки | 50,0 |4,00 |22 октября 2009 г. |5 |

Не могу понять как это правильно реализовать

CREATE 

TRIGGER aft_ins_product

    AFTER INSERT

    ON product

    FOR EACH ROW

BEGIN

  DECLARE a, b   DECIMAL (10, 2);

  DECLARE d         CHAR (20);

  DECLARE done      INT DEFAULT 0;

  DECLARE cur1 CURSOR FOR SELECT

    Name, CountM, id_material

  FROM

    material

  ORDER BY

    deliverdate;

  OPEN cur1;

  SET @cm = new.countm;

  REPEAT

    FETCH cur1 INTO d, a, b;

    IF NOT done THEN

      IF d = new.Material THEN

        SET @c = a - @cm;

        IF @c >= 0 THEN

          BEGIN

            SET @cm = 0;

            UPDATE  material  SET  countm = @c  WHERE

              material.id_Material = b;

          END;

...

  UNTIL done

  END REPEAT;

  CLOSE cur1;

END

Отредактированно Eugene86 (24.10.2009 22:10:18)

paulus · 23.10.2009 16:43:21

А зачем Вам такая странная схема организации данных? Таблички одновременно
несут смысл лога и количества материала? Когда материал заканчивается, лог
оказывается бессмысленным — не известно, сколько принесли. Известно только
когда.

Также задачка будет очень нетривиальна в случае, когда в material материала
(во всех строках) уже не осталось, а в product добавляется новая запись... у Вас
значения ниже нуля не опускаются ведь?

Обычно ведут отдельно журнал поступлений (Ваш material), отдельно табличку
«сколько осталось», и отдельно журнал расходов (Ваш products). Тогда и логика
простая, и работает быстро, и даже понятно, как жить с отрицательными значениями.

Eugene86 · 23.10.2009 22:50:39

paulus написал:
...
Также задачка будет очень нетривиальна в случае, когда в material материала
(во всех строках) уже не осталось, а в product добавляется новая запись... у Вас
значения ниже нуля не опускаются ведь?

Опускаются в дефицит (Deficit). Просто здесь писать не стал.

CREATE TABLE material(

  Name VARCHAR (20) DEFAULT NULL,

  CountM DOUBLE DEFAULT NULL,

  Price DECIMAL (16, 2) UNSIGNED DEFAULT NULL,

                                             Deficit decimal (16,2) unsigned default null,

  DeliverDate DATETIME DEFAULT NULL,

  id_Material BIGINT (20) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (id_Material)

);

paulus написал:
Обычно ведут отдельно журнал поступлений (Ваш material), отдельно табличку
«сколько осталось», и отдельно журнал расходов (Ваш products). Тогда и логика
простая, и работает быстро, и даже понятно, как жить с отрицательными значениями.

Это пример, содержащий 2 таблицы из БД. На самом деле таблиц разумеется больше:
Журнал поступлений: закупка сырья (таблица purchase);
табличка «сколько осталось»: склад сырья material;
журнал расходов: products
и есть ещё несколько.

CREATE TABLE purchase(

  Name VARCHAR (20) DEFAULT NULL,

  CountM DOUBLE DEFAULT NULL,

  Price DECIMAL (16, 2) UNSIGNED DEFAULT NULL,

  DeliverDate DATETIME DEFAULT NULL,

  id_purchase BIGINT (20) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (id_Material)

);

|Name |CountM |Price |Deficit |DeliverDate |id |
|Нитки | 100,0 |25,40 |0 |15 октября 2009 г. |1 |
|Молнии | 200,0 |16,30 |0 |16 октября 2009 г. |2 |
|Ткань синяя |1 000,0 |12,76 |0 |19 октября 2009 г. |3 |
|Ткань синяя |1 000,0 |10,00 |0 |21 октября 2009 г. |4 |
|Нитки | 250,0 |4,00 |0 |22 октября 2009 г. |5 |

Добавляем данные в таблицу product

insert into product(Material ,CountM ) values('Нитки',500)

Так как ниток всего 350, то в дефицит отправляются 150, причём купленных по последней дате.

|Name |CountM |Price |Deficit |DeliverDate |id |
|Нитки | 0 |25,40 |0 |15 октября 2009 г. |1 |
|Молнии | 200,0 |16,30 |0 |16 октября 2009 г. |2 |
|Ткань синяя |1 000,0 |12,76 |0 |19 октября 2009 г. |3 |
|Ткань синяя |1 000,0 |10,00 |0 |21 октября 2009 г. |4 |
|Нитки | 0 |4,00 |150 |22 октября 2009 г. |5 |

Отредактированно Eugene86 (23.10.2009 22:59:58)

paulus · 23.10.2009 23:24:05

А когда потом покупаются новые нитки? Я правда не понимаю схемы, она кажется
какой-то страшной, ужасной и непонятной. Я Вас предупредил

Что касается триггера — попробуйте упростить его. Например, в курсоре Вам не нужно
бегать по всем строкам ведь. Добавьте соответствующий WHERE и упростите себе жизнь.
Когда делаете UPDATE — следите за условием (нужно делать по уникальному id), в
Вашем примере Вы обновляете сразу несколько строк (разные даты).

Если писать всё компактнее, то читать будет тоже легче

Eugene86 · 24.10.2009 13:08:17

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

/*mname - название сырья

 kol - количество, добавляемого в product сырья*/

create procedure addproduct(IN mname char(20), kol DECIMAL(10,2))

begin

.......

end

Для того, чтобы триггер вызывал процедуру только один раз в нём нужно указывать EXIT?

create TRIGGER aft_ins_product AFTER INSERT ON product

    FOR EACH ROW BEGIN

             CALL addproduct(new.material,new.CountM);

             EXIT;

    END

Отредактированно Eugene86 (24.10.2009 22:06:54)

vasya · 24.10.2009 16:44:47

Eugene86 написал:
Для того, чтобы триггер вызывал процедуру только один раз в нём нужно указывать EXIT?

create TRIGGER aft_ins_product AFTER INSERT ON product
FOR EACH ROW BEGIN
CALL addproduct(new.material,new.CountM);
EXIT;
END

Не понял, а почему он должен вызывать её несколько раз?
EXIT в триггере указать нельзя.
Если вы имеете в виду множнственный инсерт, то у вас несколько раз будет вызываться сам триггер.

Eugene86 · 24.10.2009 21:03:56

vasya написал:
Не понял, а почему он должен вызывать её несколько раз?

Думал, что for each row
будет вызывать процедуру столько раз сколько записей в таблице.

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

Теперь ясно, спасибо.

Отредактированно Eugene86 (24.10.2009 21:08:10)

Форум пользователей MySQL

#1 23.10.2009 11:51:00

Помогите с триггером (процедурой)

#2 23.10.2009 16:43:21

Re: Помогите с триггером (процедурой)

#3 23.10.2009 22:50:39

Re: Помогите с триггером (процедурой)

paulus написал:

paulus написал:

#4 23.10.2009 23:24:05

Re: Помогите с триггером (процедурой)

#5 24.10.2009 13:08:17

Re: Помогите с триггером (процедурой)

#6 24.10.2009 16:44:47

Re: Помогите с триггером (процедурой)

Eugene86 написал:

#7 24.10.2009 21:03:56

Re: Помогите с триггером (процедурой)

vasya написал:

vasya написал:

Board footer