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.

Trigger definition:

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;

Check code

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;

Understanding of database triggers new and old

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.

	insert	update	delete
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?