I'm trying to add multiple rows using 'replace into' , but its updating one row.
REPLACE INTO user_balance
(user_id,acc_type,currency,balance,enable_deposit,
enable_withdrawal,pending_balance,update_ip)
VALUES (NEW.id,1,'USD',0,1,0,0,NEW.ip_address),
(NEW.id,1,'GBP',0,1,0,0,NEW.ip_address),
(NEW.id,1,'EUR',0,1,0,0,NEW.ip_address),
(NEW.id,1,'BTC',0,1,0,0,NEW.ip_address);
Is this right, the way am doing it?
PS: am employing a trigger to update the balance table whenever an user is created in the users table. It works well with if I replace one row, for multiple rows, it updates only the last values.
Best Answer
You should avoid using triggers in conjunction with
REPLACE INTO
on multiple rows. Why ?REPLACE INTO
is nothing more than a mechanicalDELETE
andINSERT
. It can incite mysqld to address deadlocks (See my answer to How I prevent deadlock occurrence in my application?)Here are two comments that shows the LIFO approach to processing triggers
Your problem ? An UPDATE trigger will never fire off if this has to be done multiple times in a single operation. At least, I don't see that happening.
SUGGESTIONS
INSERT ... ON DUPLICATE KEY UPDATE
(See my answer to Update one table from another table while sorting that table based on one column)REPLACE
on one row at a time