gpt4 book ai didi

插入、更新时的 MySql 触发器

转载 作者:行者123 更新时间:2023-11-30 21:53:27 25 4
gpt4 key购买 nike

我已经使用 MySQL 一段时间了,但我对 MySQL 触发器完全陌生。

我有这个数据库:

  • jours(jour (日期, 主键), ventes (int), soldeinitial( int)) ;
  • achatscharges(Id (int, 主键), libelle (varchar), prix (int), #jour (date, forein键)。

在我的表('achatscharges')上每次插入或更新后,触发器应检查表'jours 中的'soldeinitial' 值' 为 null 然后用这个公式更新它:

'Something like( the value of 'jours.soldeinitial' of the last row in 'jours' - sum(achatscharges.prix) + jours.ventes'

这是我试过的:

CREATE TRIGGER UpdateSolde
AFTER INSERT, UPDATE ON achatscharges
FOR EACH ROW
BEGIN
IF((SELECT jours.soldeinitial FROM jours WHERE jours.jour=New.jour) IS NULL)
THEN
UPDATE jours SET jours.soldeinitial=((SELECT jours.soldeinitial FROM jours WHERE jours.soldeinitial IS NOT NULL ORDER BY jours.jour DESC LIMIT 1)-SUM(New.prix)+jours.ventes) WHERE jours.jour=New.jour);
END IF;
END

这是例子:

+------------+-------- +---------------------+
| jour | ventes | soldeinitial |
+------------+---------+---------------------+
| 11-09-2017 | 1500 | 3000 |
| 12-09-2017 | 2000 | (must be calculated)|
| 13-09-2017 | 4000 | (must be calculated)|
+------------+---------+---------------------+
+------------+-------- +---------------------+
| id | libelle | prix | #jour |
+------------+---------+---------------------+
| 1 | somthing | 300 | 12-09-2017 |
| 2 | somthing | 200 | 12-09-2017 |
| 3 | somthing | 800 | 13-09-2017 |
+------------+---------+---------------------+
after triggger insert or update in achatscharges :
i should have this result :
+------------+-------- +---------------------------+
| jour | ventes | soldeinitial |
+------------+---------+---------------------------+
| 11-09-2017 | 1500 | 3000 |
| 12-09-2017 | 2000 | 4500 =(3000-(300+500)+2000|
| 13-09-2017 | 4000 | 7700 =4500-(800)+4000 |
+------------+---------+---------------------------+

不好意思解释的不好,就是这个例子

+------------+-------- +-------------------------+
| jour | ventes | soldeinitial |
+------------+---------+-------------------------+
| 11-09-2017 | 1500 | 3000 manually |
| 12-09-2017 | 2000 | 4500 calculated |
| 13-09-2017 | 4000 | 7700 calculated |
| 14-09-2017 | 3000 | *3000* manually |
| 15-09-2017 | 1500 | 3700 = *3000*-(800)+1500| the trigger doesn't work here the value still null
+------------+---------+-------------------------+
+------------+-------- +---------------------+
| id | libelle | prix | #jour |
+------------+---------+---------------------+
| 1 | somthing | 300 | 12-09-2017 |
| 2 | somthing | 200 | 12-09-2017 |
| 3 | somthing | 800 | 13-09-2017 |
| 4 | somthing | 800 | 15-09-2017 |
+------------+---------+---------------------+

希望澄清我注意到的内容。

最佳答案

我使用过这个测试数据:

CREATE TABLE jours
(`jour` DATE, `ventes` int, `soldeinitial` int)
;
CREATE TABLE achatscharges
(`id` int, `libelle` varchar(8), `prix` int, `jour` DATE)
;

INSERT INTO jours
(`jour`, `ventes`, `soldeinitial`)
VALUES
('2017-09-11', 1500, 3000),
('2017-09-12', 2000, null),
('2017-09-13', 4000, null)
;

我创建了这样的插入触发器:

DELIMITER //
DROP TRIGGER IF EXISTS InsertSolde//
CREATE TRIGGER InsertSolde
AFTER INSERT ON achatscharges
FOR EACH ROW
BEGIN
DECLARE l_previous INTEGER;
DECLARE l_sumprix INTEGER;
IF EXISTS(SELECT jour FROM jours WHERE jour = NEW.jour)
THEN
SET l_previous = (SELECT soldeinitial FROM jours WHERE soldeinitial IS NOT NULL AND jour < NEW.jour ORDER BY jour DESC LIMIT 1);
SET l_sumprix = (SELECT SUM(prix) FROM achatscharges WHERE jour = NEW.jour GROUP BY jour);
UPDATE jours SET soldeinitial = l_previous-l_sumprix+ventes WHERE jour=New.jour;
END IF;
END //
DELIMITER ;

并通过三个单一步骤对其进行了测试

INSERT INTO achatscharges
(`id`, `libelle`, `prix`, `jour`)
VALUES
(1, 'somthing', 300, '2017-09-12');

SELECT * FROM jours;

INSERT INTO achatscharges
(`id`, `libelle`, `prix`, `jour`)
VALUES
(2, 'somthing', 200, '2017-09-12');

SELECT * FROM jours;

INSERT INTO achatscharges
(`id`, `libelle`, `prix`, `jour`)
VALUES
(3, 'somthing', 800, '2017-09-13')
;
SELECT * FROM jours;

希望对您有所帮助。更新触发器类似。

关于插入、更新时的 MySql 触发器,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/46164738/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com