Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
 Триггер для таблицы Покупка  [new]
igorek774
Member

Откуда:
Сообщений: 9
Здравствуйте, помогите, пожалуйста составить триггер для таблицы Покупка
Логическая схема
Физическая схема

Триггер должен отрабатывать при добавлении записи в таблицу покупка:
1) Если у пользователя хватает денег на игру и ключ для этой игры имеется в бд и при этом имеет Статус = 0, тогда мы добавляем его в в таблицу, при этом отнимает количество денег у Покупателя столько и обновляем количество измененных денег в таблице Покупатель, сколько стоит игра и ставим Статус у купленного ключа в значение Статус = 1.
1.1) Иначе сообщаем пользователю, что игры нет,
1.2) Или сообщаем, что у пользователя недостаточно денег для покупки игры
2) Стоит учесть, что пользователь может купить только одну игру (т.е. Если пользователь уже купил игру Pubg, то он не может ее купить опять, т.к. уже она куплена) и вывести сообщение, что игра у пользователя уже есть.

Сообщение было отредактировано: 19 май 21, 21:43
19 май 21, 21:49    [22324459]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для таблицы Покупка  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21181
Выложите CREATE TABLE всех таблиц и INSERT INTO с примером данных, и несколько запросов на добавление с результатом на показанных данных (если предыдущий запрос что-то изменил, следующий выполняется на изменённых данных) - для воспроизведения ситуации. Ну или сразу сделайте online fiddle.

PS. На самом деле триггер и не нужен. Всё описанное прекрасно помещается в условия отбора плюс CHECK constraint. А н клиенте просто анализируется текст (не код!) сообщения об ошибке.

Сообщение было отредактировано: 19 май 21, 23:06
19 май 21, 23:14    [22324513]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для таблицы Покупка  [new]
igorek774
Member

Откуда:
Сообщений: 9
Akina, я вам базу скину. Я не совсем понял как сделать лучше.

База данных
19 май 21, 23:24    [22324518]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для таблицы Покупка  [new]
polygraph
Member

Откуда:
Сообщений: 7
Akina,

Insert в таблицу Покупки например, вот так
Insert into buy (id_buyer, key_games) values (1, 1);
20 май 21, 01:38    [22324562]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для таблицы Покупка  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21181
igorek774
Я не совсем понял как сделать лучше.

Вот так: fiddle.

Сообщение было отредактировано: 20 май 21, 07:53
20 май 21, 07:57    [22324579]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для таблицы Покупка  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21181
polygraph
Insert в таблицу Покупки например, вот так
Insert into buy (id_buyer, key_games) values (1, 1);

Вот не надо таких "например"! Где, спрашивается, в таблице игр запись с id_games = 1 ??

А вот теперь подробнейшим образом расписывайте алгоритм, но со ссылками на конкретные строки и значения.
Типа:

- По значению id_buyer = 1 получаем buyer.cash = ???
- По значению key_games = 1 получаем game.price = ???
--- Если buyer.cash меньше game.price - сообщаем, что мало денег
- По значению key_games = 1 получаем одну запись из key_in_game со status = 0, с минимальным лексикографическим key_games, её key_games = ???
--- Если такой записи нет, сообщаем, что такой игры в продаже нет
- ...

При этом:

1. Таким образом распишите вставку для каждой возможной ситуации - полупка успешна, денег мало, игры нет, и т.п.
2. Если предыдущая расписанная операция прошла успешно, следующая расписывается для состояния данных с учётом предыдущей.

Сообщение было отредактировано: 20 май 21, 08:04
20 май 21, 08:11    [22324580]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для таблицы Покупка  [new]
igorek774
Member

Откуда:
Сообщений: 9
Akina,
Ошибся. При покупке вставка будет такая: добавляем id_buyer=1, а ключ для игры должен браться так : подзапросом не знаю можно ли это организовать - пишем название игры game_name=??? А по id_game переходим в таблицу key_in_game и находим один ключ и тогда будет вставка в таблицу insert into (id_buyer, key_games) values (1, “какой-то ключ, который нашли по игре)

Дальше мы должны проверить если покупатель id_buyer =1 и в таблице buy есть этот покупатель и по ключу который он купил находим в таблице key_in_games по полю id_games получаем из таблицы game название игры , то выдать сообщение , что игра эта уже куплена. Иначе если такой игры по названию он не купил , тогда добавляем в таблицу buy

Если же покупатель ввёл для покупки название для игры какое-то, и мы должны проверить также есть есть для этой игры ключ со статусом = 0 если нет , то сообщение об ошибке : игры нет в наличии

По значению id_buyer = 1 получаем buyer.cash = ???
По значению key_games = 1 получаем game.price = ???
Если buyer.cash меньше game.price - сообщаем, что мало
И у значения ключа key_games , который мы добавили пишет ему в status = 1 (это значит что ключ куплен)

Как-то так? Пытался понятно объяснить :)
20 май 21, 15:38    [22324800]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для таблицы Покупка  [new]
igorek774
Member

Откуда:
Сообщений: 9
Akina,

Попробую по-проще

При покупке вставка будет такая: INSERT INTO (`id_buyer`, `key_games`) VALUES (1, KeyInGame('Minecraft'));

KeyInGame('Minecraft') - Некая функция, которая принимает 1 параметр, название игры.
Она по таблице game через ключ id_games найдет в таблице key_in_games ключ для этой игры и вернет результат.
GameInKey(KeyInGame('Minecraft')) - Функция, которая по ключу находит название игры.
Дальше проверки в триггере:

1) Мы должны проверить если покупатель id_buyer =1 и в таблице buy есть этот покупатель и ключ KeyInGame('Minecraft') он уже купил, тогда мы проверяем:
1.1) Находим по ключу GameInKey(KeyInGame('Minecraft')) название игры и сравниваем, если в таблице games.name = 'Minecraft' = GameInKey(KeyInGame('Minecraft')), то появляется, сообщение об ошибке, что игра уже такая имеется у вас.

-------------------------НАДЕЮСЬ, ОПИСАННОЕ ВЫШЕ БУДЕТ ПРАВИЛЬНЫМ ПОДХОДОМ.

Если же INSERT INTO (`id_buyer`, `key_games`) VALUES (1, KeyInGame('Minecraft'));, мы также должны проверить, какой есть ли эта игра в наличии, т.е. ищем игру GameInKey(KeyInGame('Minecraft'))в таблице game, чтобы получить game_id и в таблице key_in_game по id_games находим все ключи к этой игре и при этом status должен быть равен = 0 (1 - значит, что игра куплена), тогда добавляем INSERT INTO (`id_buyer`, `key_games`) VALUES (1, KeyInGame('Minecraft')) в таблицу buy, иначе говорим, что игры в наличии нет.


По значению id_buyer = 1 получаем buyer.cash = ???
По значению key_games = 1 получаем game.price = ???
Если buyer.cash < game.price - сообщаем, что мало

Как только покупатель смог успешно купить игру, то необходимо у этого покупателя (id_buyer = 1) в таблице buyer вычесть buyer.cash < game.price. Обновить данные.

И у купленного ключа поставить status = 1

Сообщение было отредактировано: 20 май 21, 16:00
20 май 21, 16:07    [22324824]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для таблицы Покупка  [new]
igorek774
Member

Откуда:
Сообщений: 9
Akina,

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

DELIMITER ;
DELIMITER $$
BEGIN
SET @isGame = (SELECT COUNT(buy.key_games) FROM buy INNER JOIN key_in_game USING (key_games) INNER JOIN game USING (id_games) WHERE buy.id_buyer = NEW.id_buyer AND game.name = GameInKey(NEW.key_games) GROUP BY(game.name) HAVING COUNT(buy.key_games) > 0);
SET @isMoney = (select IsMoneyTrue(buyer.cash, game.price) FROM buyer INNER JOIN buy USING (id_buyer) INNER JOIN key_in_game USING (key_games) INNER JOIN game USING (id_games) WHERE buyer.ID_buyer = NEW.ID_buyer AND key_in_game.key_games=NEW.key_games);
IF (SELECT IFNULL(GameInKey(NEW.key_games),0)) = 0 THEN
 	SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Игры нет в наличии';
ELSEIF (@isGame>0) THEN
 	SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Вы уже купили эту игру';
ELSEIF (@isMoney = 0) THEN
 	SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Недостаточно средств для покупки игры';
ELSE
	UPDATE key_in_game SET `status` = 1 WHERE key_in_game.key_games = NEW.key_games;
END IF;
END$$
DELIMITER ;


Я не понимаю почему запрос отлично работает, а триггер нет

К сообщению приложен файл (shop (1).sql - 18Kb) cкачать

Сообщение было отредактировано: 21 май 21, 02:47
21 май 21, 02:55    [22325076]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для таблицы Покупка  [new]
igorek774
Member

Откуда:
Сообщений: 9
Akina,

Вот fiddle

Сообщение было отредактировано: 21 май 21, 11:52
21 май 21, 11:59    [22325209]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для таблицы Покупка  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21181
Давай чётко определяться с алгоритмом и избавляться от каши.

Первое - исходные данные. Как я понимаю, их 2:

- пользователь, имеется его buyer.id_buyer
- игра, имеется её наименование game.name

Последовательность действий следующая.

1. Проверяем, что игра имеется к продаже. Т.е. для игры имеется запись в key_in_game со значением status = 0. Если нет - выводим сообщение 'Игры нет в наличии'.

2. Ок, игра есть. Проверяем, что у пользователя нет этой игры. Т.е. для пользователя получаем список уже купленных им ключей, и убеждаемся, что ли один из них не соответствует этой игре. Если это не так - выводим сообщение 'Вы уже купили эту игру'.

3. Ок, такой игры ранее не куплено. Проверяем, что у пользователя достаточно средств для покупки. Получаем стоимость игры, сравниваем с балансом пользователя. Если баланс меньше стоимости - выводим сообщение 'Недостаточно средств для покупки игры'.

4. Ок, денег хватает. Берём одну случайную запись из key_in_game, устанавливаем ей status = 1. Берём key_games этой записи, и, наконец, создаём соотв. запись в таблице buy.

Всё верно?

Сообщение было отредактировано: 21 май 21, 13:53
21 май 21, 14:01    [22325282]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для таблицы Покупка  [new]
igorek774
Member

Откуда:
Сообщений: 9
Akina,

Да, все верно
И последнее после того, как запись попала в таблицу buy у покупателя ответь количество денег столько, сколько стоит игра
21 май 21, 14:05    [22325284]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для таблицы Покупка  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21181
Ок. Да, про уменьшение баланса забыл.

Но это было рассуждение "что надо делать с данными"..

Переходим к следующему этапу - как это делать. И имеем проблему. У нас 2 исходных данных. INSERT может записывать только в одну таблицу, значит, в остальные может записывать код триггера. Вроде бы всё нормально, но есть подвох. А куда мы будем передавать исходные данные и как? Если данные, передаваемые для таблицы buy, у нас честно фигурируют в VALUES clause, то для данных, которые должны лечь в другие таблицы или использоваться для извлечения таких данных, а конкретно - для game.name,- у нас места в запросе на вставку в таблицу buy просто нет.

Засада.

Отсюда простой вывод - триггеры нам не помощники. И запрос задачу не решит.

Решение же - простое. Реализовывать всё вышеописанное нужно не запросом, а хранимой процедурой. В неё можно спокойно передавать любые параметры, ибо они не привязаны к таблице, и реализовывать любую нужную логику. К тому же - триггеры при таком подходе просто не нужны.
21 май 21, 14:31    [22325300]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для таблицы Покупка  [new]
igorek774
Member

Откуда:
Сообщений: 9
Akina,

Хм, а можете показать пример ?
21 май 21, 14:35    [22325305]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для таблицы Покупка  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21181
CREATE PROCEDURE save_purchase (IN buyer_id INT, 
                                IN game_name VARCHAR(25),
                                OUT result VARCHAR(255))
save_purchase:BEGIN
DECLARE game_key VARCHAR(12) DEFAULT NULL;
-- На случай непредвиденной ошибки
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    SET result := 'Непредвиденная ошибка.\nНе удалось оформить покупку, повторите позже.';
    ROLLBACK;
END;

-- Проверка, не куплена ли уже эта игра
IF EXISTS ( SELECT NULL
            FROM buy
            JOIN key_in_game USING (key_games)
            JOIN game USING (id_games)
            WHERE game_name = game.name
              AND buyer_id = buy.ID_buyer ) THEN
    SET result := 'Вы уже купили эту игру';
    LEAVE save_purchase;
END IF;
-- Проверка, достаточно ли средств
IF ( SELECT cash
     FROM buyer
     WHERE buyer_id = buyer.ID_buyer ) < ( SELECT price
                                           FROM game
                                           WHERE game_name = game.name ) THEN
    SET result := 'Недостаточно средств для покупки игры';
    LEAVE save_purchase;
END IF;

START TRANSACTION;
-- Выбираем произвольный ключ
SELECT key_in_game.key_games INTO game_key
FROM key_in_game
JOIN game USING (id_games)
WHERE game_name = game.name
  AND !status LIMIT 1;
-- Удалось ли найти свободный ключ
IF game_key IS NULL THEN 
    SET result := 'Игры нет в наличии';
    LEAVE save_purchase;
END IF;
-- Пробуем оформить приобретение
UPDATE key_in_game
SET status = 1
WHERE key_in_game.key_games = game_key
  AND !status;
-- Кто-то вклинился и уже купил этот ключ
IF !ROW_COUNT() THEN
    SET result := 'Не удалось оформить покупку, повторите позже.';
    ROLLBACK;
    LEAVE save_purchase;
END IF;
-- Вносим сведения о покупке
INSERT INTO buy (date_buyer, id_buyer, key_games)
VALUES (CURRENT_DATE, buyer_id, game_key);
-- При записи возникла ошибка
IF !ROW_COUNT() THEN
    SET result := 'Не удалось оформить покупку, повторите позже.';
    ROLLBACK;
    LEAVE save_purchase;
END IF;
-- Обновляем баланс
UPDATE buyer
CROSS JOIN game
SET buyer.cash = buyer.cash - game.price
WHERE buyer_id = buyer.ID_buyer
  AND game_name = game.name;
-- При обновлении возникла ошибка
IF !ROW_COUNT() THEN
    SET result := 'Не удалось оформить покупку, повторите позже.';
    ROLLBACK;
    LEAVE save_purchase;
END IF;
-- Всё прошло нормально
COMMIT;
-- Формируем отчёт о покупке
SELECT CONCAT( 'Покупка оформлена успешно.\n',
               'Приобретена игра "', game_name, '".\n',
               'Номер лицензии: ', game_key, '\n', 
               'Остаток средств на счёте: ', cash ) INTO result
FROM buyer
WHERE buyer_id = buyer.ID_buyer;

END


fiddle

1. Триггер - удалён за ненадобностью.
2. Вставка записи в buy - закомментирована за ненадобностью.
3. Триггер buyer.Correctfio_update содержит ошибку и требует исправления. Закомментирован.
21 май 21, 15:40    [22325339]     Ответить | Цитировать Сообщить модератору
 Re: Триггер для таблицы Покупка  [new]
igorek774
Member

Откуда:
Сообщений: 9
Akina,

Обалдеть, спасибо большое. Я правда первый раз вижу транзакции. Надо теперь узнать как они работают))))
21 май 21, 16:22    [22325373]     Ответить | Цитировать Сообщить модератору
Все форумы / MySQL Ответить