Business scenario: balance shared by different business systems, hjmallind_user and ims_cjdc_user two tables have different balance fields, but share the balance value.
DROP TRIGGER IF EXISTS `test-up_ds_wallet`; CREATE TRIGGER `test-up_ds_wallet` AFTER UPDATE ON `ims_cjdc_user` FOR EACH ROW BEGIN DECLARE ds_money decimal(10,2); IF new.wallet <> old.wallet THEN select money into ds_money from hjmallind_user where ptuserid=new.id; #Resolve trigger deadlock IF ds_money <> new.wallet THEN UPDATE hjmallind_user set money=new.wallet where ptuserid=new.id; END IF ; END IF ; END; DROP TRIGGER IF EXISTS `test-up_wm_wallet`; CREATE TRIGGER `test-up_wm_wallet` AFTER UPDATE ON `hjmallind_user` FOR EACH ROW BEGIN DECLARE wm_wallet decimal(10,2); IF new.money <> old.money THEN select wallet into wm_wallet from ims_cjdc_user where id=new.ptuserid; #Resolve trigger deadlock IF wm_wallet <> new.money THEN UPDATE ims_cjdc_user set wallet=new.money where id=new.ptuserid; END IF ; END IF ; END;
select id,wallet from ims_cjdc_user where id=164438; select id,ptuserid,money from hjmallind_user where ptuserid=164438; -- update hjmallind_user set money=money+50 where ptuserid=8426; update ims_cjdc_user set wallet=wallet+20.50 where id=164438; select id,wallet from ims_cjdc_user where id=164438; select id,ptuserid,money from hjmallind_user where ptuserid=164438;
The values before and after updating are often used in database triggers. It is important to understand the role of new and old. At that time, I had a situation like this: I want to insert a row of data, get a unit price in other tables in the row, then multiply the data in this row to the total amount, and replace the amount in this row with the result of multiplication.
First I use after, and then change my value.
|old||null||actual value||actual value|
|new||actual value||actual value||null|
In Oracle, the lines before and after execution are represented by: old and: new. In MySQL, old and new are used to represent the data before and after execution.
The origin of the problem
The previous trigger for the database was written like this,
1 CREATE TRIGGER triggerName after insert ON consumeinfo 2 FOR EACH ROW 3 BEGIN 4. Update consumeinfo set new. Amount = 0; 5 END;
Trigger creation is OK, but the following error occurred when inserting data.
[Err] 1442 - Can't update table 'consumeinfo' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
However, according to the results of online search, you can modify this table without using update consumeinfo and directly use SET new. Amount = 0. This is right, because if you use new to change the current amount and then save it to the database, you don't need to use update consumeinfo.
After some efforts, the following is the successful code. Post it and have a look
CREATE TRIGGER addnewReco BEFORE INSERT ON consumeinfo FOR EACH ROW BEGIN SET new. Amount =( SELECT ` unit price` FROM pricenow WHERE ` type '= new )* new. Quantity; END;
Later, when I was eating and drinking soup, I suddenly thought that new and old were used differently on after and before. In fact, it's because new can't be assigned after, but can only be read. Copy needs to be assigned before.
Usage of new and old
Let's talk about the use of old and new. When assigning a value to new, it can only be used in the trigger before and cannot be used in after, for example (the following is correct).
CREATE TRIGGER updateprice BEFORE insert ON consumeinfo FOR EACH ROW BEGIN set new. Amount = 0; END;
This indicates that before is used to update the current inserted data before it is inserted into the database. In the after trigger, the assignment of new has ended and only the content can be read. If you use after, you can't use new assignment, you can only take values, otherwise an error will occur, such as
1 CREATE TRIGGER updateprice 2 AFTER insert 3 ON consumeinfo 4 FOR EACH ROW 5 BEGIN 6. Set new. Amount = 0; 7 END;
The following error occurred:
[Err] 1362 - Updating of NEW row is not allowed in after trigger
Summary: new is assigned a value in the before trigger; Value in after trigger. old is used for value selection? Because assignment doesn't make sense?