mysql - How to create insert trigger on table? -

mysql - How to create insert trigger on table? -

i have table warehouse:

++++++++++++++ + id + count + ++++++++++++++ + 1 + 10 + ++++++++++++++ + 10 + 100 + ++++++++++++++ + 3 + 200 + ++++++++++++++

i want create trigger, if insert info value, exists in table, count of row updated , new row wouldn't inserted. if there no info in table such id, new row must added.

expected result:

after query:

insert warehouse (id, count) values (1, 15);

warehouse table contents be:

++++++++++++++ + id + count + ++++++++++++++ + 1 + 25 + ++++++++++++++ + 10 + 100 + ++++++++++++++ + 3 + 200 + ++++++++++++++

after query:

insert warehouse (id, count) values (8, 11);

warehouse contents become:

++++++++++++++ + id + count + ++++++++++++++ + 1 + 25 + ++++++++++++++ + 10 + 100 + ++++++++++++++ + 3 + 200 + ++++++++++++++ + 8 + 11 + ++++++++++++++

after this:

insert warehouse (id, count) values (3, 5);

warehouse contents become:

++++++++++++++ + id + count + ++++++++++++++ + 1 + 25 + ++++++++++++++ + 10 + 100 + ++++++++++++++ + 3 + 205 + ++++++++++++++ + 8 + 11 + ++++++++++++++

i using mysql 5.1.

update

what if utilize such approach insert warehouse table?

insert warehouse ( count, id ) select count, id shopordergoods order_id = 1

is possible such behaviour want approach?

mysql not back upwards instead of trigger; is, it's not possible trigger.

assuming id column primary key of table (or @ minimum, unique key on table) can utilize on duplicate key form of insert statement accomplish behavior describe.

for example:

insert warehouse (id, count) values (1, 15) on duplicate key update count = count + values(`count`);

mysql effort insert, , if throws "duplicate key" exception, update action attempted. in illustration above, current value of count column have new value supplied column (in values clause of insert) added it.

(note: if current value of count column null, or value beingness inserted null, null assigned count column. if want handle null value beingness zero, enhance look handle cases:

on duplicate key update `count` = ifnull(`count`,0) + ifnull(values(`count`),0)

n.b. if there column in table has auto_increment property, auto increment table increased each insert attempt, including dupliate key exception thrown.

update

(based on update of question)

the on duplicate key form insert work insert ... select insert ... values.

insert warehouse (count,id) select s.count, s.id shopordergoods s s.order_id = 1 on duplicate key update `warehouse`.`count` = `warehouse`.`count` + values(`count`);

(in first illustration in answer, enclosed column name "count" in backticks, because i'm not sure if count reserved word. looks reserved word, didn't check list. in lastly example, i've not enclosed column name in backticks. don't ever utilize "count" column name or alias.) t utilize never name )

mysql sql triggers