MYSQL触发器-如何存储计算字段的结果 [英] MYSQL Triggers - how to store the result of a calculated field

问题描述

我正在使用MySQL 5.5.我需要使用mysql触发器语法将触发器添加到我的表中: http ://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html

I am using MySQL 5.5. I need to add a Trigger to my table using mysql trigger syntax: http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html

他们给出的示例并未说明我该如何做-

The example they have given doesn't explain how I can go about doing this -

我有一张桌子-table(a INT, b INT, c INT);.字段a和b是数字，而字段c应该是a + b.现在，我确定您想知道为什么不只是在视图中拍一下并完成它，或者为什么不将其放入我的代码中呢?原因是因为我正在与一个需要自动计算字段便利的客户端一起工作，并且能够在需要变化的情况下修改值.他们是一家审计公司，由于公司缺少审计日期等原因，经常需要对数字进行汇总.

I have a table - table(a INT, b INT, c INT);. field a and b are numbers, while field c should be a + b. Now i'm sure you are wondering why not just slap this in a view and be done with it, or why not put this in my code. The reason is because I am working with a client that needs the convenience of an auto calc'ed field, with the ability to modify the value incase it needs variation. They are an auditing company and massaging the numbers is often required because of companies missing audit dates etc.

那么我该如何创建一个触发器，该触发器将:

So how can I create a trigger that will:

on insert:
make `c` the value of `a` + `b`.

on update: 
if the value of NEW.`c`==OLD.`c` THEN
make `c` the value of `a` + `b`.
ELSE 
no change

如果新值与旧值不同，则更新不会更改的原因是因为这意味着他们希望将数字修改为与实际总和略有不同.

The reason for the update not changing if the new value is different to the old value is because that would mean they want to modify the number to be slightly different to what the actual sum is.

请随时更改我的逻辑-我的目的是保留c的值(如果它是手动输入的)，以及将其炸开(如果没有手动操作的话).

Please feel free to change my logic - my aim is to preserve the value of c if it has been entered manually and to blast it if it hasn't been touched manually.

谢谢！

推荐答案

我知道这是一个老问题，但是如果仍然需要答案，那就是

I know this is an old question, but if the answer is still needed here it is.

例如，首先将id列添加到表中，以便进行更直接的更新.

First of all an id column has been added to the table for example's sake to have more direct updates.

CREATE TABLE table1
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a INT, b INT, c INT
);

现在，在INSERT触发器中更改了逻辑，以允许在C列中插入预先计算的值.

Now in INSERT trigger the logic is changed to allow an insert of a pre-calculated value to C column.

CREATE TRIGGER tg_table1_before_insert
BEFORE INSERT ON table1
FOR EACH ROW
  SET NEW.c = IF(NEW.c IS NULL, NEW.a + NEW.b, NEW.c);

更新触发器根据您的要求实现逻辑

An update trigger implements the logic per your requirements

CREATE TRIGGER tg_table1_before_update
BEFORE UPDATE ON table1
FOR EACH ROW
  SET NEW.c = IF(NEW.c <=> OLD.c, NEW.a + NEW.b, NEW.c);

现在让我们进行一些插入和更新

Now lets do some inserts and updates

INSERT INTO table1 (a, b) VALUES (1, 2), (3, 4);
INSERT INTO table1 (a, b, c) VALUES (5, 6, 0), (7, 8, 100);
UPDATE table1 SET c = 25 WHERE id = 2;
UPDATE table1 SET c = c  WHERE id = 3;

因此我们拥有

| ID | A | B |   C |
--------------------
|  1 | 1 | 2 |   3 | -- calculated on insert
|  2 | 3 | 4 |  25 | -- explicitly set on update
|  3 | 5 | 6 |  11 | -- re-calculated on update
|  4 | 7 | 8 | 100 | -- explicitly set on insert

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于MYSQL触发器-如何存储计算字段的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！