MySQL Database Function to reduce value in other table by 1

Question

I have a MySQL db. When a field in a table is updated can I write a function or by some other process get a field in another table to be automatically updated?

ie, and this is what I want to do, if table 1 field discount_code and discount code is 123456, then in table 2 can I have times_code_can_be_used (starts at say 20) automatically reduced by 1???

Not done this before and need some guidance.

Thank you.

You'll need a trigger for such behaviour. Se: http://dev.mysql.com/doc/refman/5.1/en/triggers.html — ypercubeᵀᴹ, Sep 30 '11 at 11:49
Try this similar question to get started: http://stackoverflow.com/questions/7404805/using-an-update-trigger-to-update-another-table — ypercubeᵀᴹ, Sep 30 '11 at 11:53
The similar is `AFTER UPDATE ON A ... FOR EACH ROW BEGIN UPDATE TABLE B ...` — ypercubeᵀᴹ, Sep 30 '11 at 11:54

score 3 · Accepted Answer · answered Sep 30 '11 at 11:52

3

yes you can do it using mysql trigger

example :-

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

delimiter ;

Note :Support for triggers is included beginning with MySQL 5.0.2

answered Sep 30 '11 at 11:52

Gowri

16,587
26
100
160

Thank you.. having a look now.. trying to work out how change my current UPDATE to incorporate.. – user718359 Sep 30 '11 at 12:07
Got a quick question you still around? – user718359 Sep 30 '11 at 12:30
@user718359:yes shoot it please – Gowri Sep 30 '11 at 12:36
How do we chat on this thing? – user718359 Sep 30 '11 at 12:45
How do I add a trigger to that? – user718359 Sep 30 '11 at 12:47
WHERE Table 2 field desc_id = table other_options_table desc_id.. then in other_option_table field 'counter' to be reduced by 1 (ie -1).. – user718359 Sep 30 '11 at 12:49
The field Counter will start at maybe 50 and each time table2 updated IF in desc_id same in bothe tables then counter to go down by 1.. – user718359 Sep 30 '11 at 12:51

score 0 · Answer 2 · answered Sep 30 '11 at 11:50

you can just use a trigger function. You can set it to run for a particular table if a field is updated or inserted or deleted. Inside that trigger function, you can put any kind of sql you want with whatever logic you need, in your example, just get data from the updated field and use it to update fields in another table.

MySQL Database Function to reduce value in other table by 1

2 Answers2