CODEWITHSUNDEEP
mysqlsqltriggers
Sharikov Vladislav
Sharikov Vladislav

Reputation: 7279

How to create insert trigger on table?

I have table warehouse:

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

I want to create trigger, so if I insert data with value, which exists in table, count of this row will be updated just and new row wouldn't be inserted. If there are no data in table with such id, then new row must be added.

Expected result:

After query:

INSERT INTO Warehouse (id, count) VALUES (1, 15);

Warehouse table contents will be:

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

After this query:

INSERT INTO Warehouse (id, count) VALUES (8, 11);

Warehouse contents would become:

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

After this:

INSERT INTO Warehouse (id, count) VALUES (3, 5);

Warehouse contents would become:

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

I am using MySQL 5.1.

Update

What if I use such approach to insert something into Warehouse table?

INSERT INTO 
    Warehouse
    (
        count,
        id
    )
    SELECT 
        count,
        id
    FROM 
        ShopOrderGoods
    WHERE 
        order_id = 1

Is it possible to get such behaviour as I want with that approach?

Upvotes: 0

Views: 100

Answers (1)

spencer7593
spencer7593

Reputation: 108500

MySQL does not support INSTEAD OF trigger; that is, it's not possible to do this with a trigger.

Assuming the id column is the PRIMARY KEY of the table (or at a minimum, a UNIQUE KEY on the table) you can use the ON DUPLICATE KEY form of the INSERT statement to achieve the behavior you describe.

For example:

INSERT INTO Warehouse (id, count) VALUES (1, 15)
ON DUPLICATE KEY UPDATE count = count + VALUES(`count`);

MySQL will attempt the insert, and if that throws a "duplicate key" exception, then the UPDATE action will be attempted. In the example above, the current value of the count column will have the new value supplied for that column (in the VALUES clause of the insert) added to it.

(NOTE: If the current value of the count column is NULL, or the value being inserted is NULL, then a NULL will be assigned to the count column. If you want to handle a NULL value as being zero, then you could enhance the expression to handle those cases:

ON DUPLICATE KEY UPDATE `count` = IFNULL(`count`,0) + IFNULL(VALUES(`count`),0)

N.B. If there is another column in the table that has the AUTO_INCREMENT property, the auto increment for the table will be increased for each insert attempt, including those where the dupliate key exception is thrown.

UPDATE

(based on UPDATE of question)

The ON DUPLICATE KEY form the INSERT will work with an INSERT ... SELECT just like it does with INSERT ... VALUES.

INSERT INTO Warehouse (count,id)
SELECT s.count, s.id
 FROM ShopOrderGoods s
WHERE s.order_id = 1
   ON DUPLICATE KEY UPDATE `Warehouse`.`count` = `Warehouse`.`count` + VALUES(`count`);

(In the first example in my answer, I enclosed the column name "count" in backticks, because I'm not sure if COUNT is a reserved word. It looks like a reserved word, but I didn't check the list. In this last example, I've not enclosed the column name in backticks. I don't ever use "count" as a column name or alias.) t use never name )

Upvotes: 3

Related Questions