mysql触发器流水账结存,MYSQL触发器-如何存储计算字段的结果

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 -

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.

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.

Thanks!

解决方案

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

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

);

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;

As a result we have

| 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

Here is