“This is the fourth day of my participation in the First Challenge 2022. For details: First Challenge 2022”

The previous mysql review covered the basic operation of triggers. In this article, I will share a case study I came across in my project

The original requirements

The person in charge wants to leave traces in the system after the data in the material table is changed by others, for example, record 1, which data was updated by someone at a certain time, the old value and the new value of these data, what are the differences

In addition, the person in charge also needs to know that the data has been updated to facilitate some work adjustments (cross-departmental pain).

Demand analysis

Intuitive feeling, when the table in the update, need to record the relevant data, these data is best stored in a new, similar to the log table

Consider two options:

First, use AOP

With AOP, the aspect is positioned on the corresponding xxmapper.update method. When Java performs the update operation, AOP also comes to work, and the working logic is as follows:

  1. Method is called into update, triggering the AOP aspect
  2. In AOP, you first go to the DB’s corresponding table to retrieve the corresponding record’s existing data
  3. Compare the values of each attribute in the old and new data and record the changing data
  4. Record the changing data to the library
  5. Beautify the data display format and send this data to the person in charge

This program, basically in Java to achieve this function, not good or bad, the rules

Second, use triggers

The whole logic is similar to the previous scheme, the same need to compare the old and new values + record, and notify the person

However, this is database level, and triggers can directly fetch two versions of data when fired, and there are some logical operations supported in mysql

Finally, consider option 2, which has less coupling to the source code and does not require adding AOP code and processing logic to Java

Design log table

Key elements of the log table:

  • Who did
  • Which records were moved
  • Operation time
  • Fluctuating data
  • Whether this log record was processed (e.g. notification sent)

The final SQL table is as follows:

drop table if EXISTS mdm_basics_change_log;
create table mdm_basics_change_log(
    id BIGINT PRIMARY key auto_increment COMMENT 'id',
    mdm_code varchar(50) COMMENT 'MATERIAL Code',
    update_by varchar(20) COMMENT 'Update person account',
    update_name varchar(20) COMMENT 'Update person's name',
    update_date TIMESTAMP DEFAULT now() COMMENT 'Update date',
    change_data LONGTEXT COMMENT 'Changed data',
    notified int default 0 COMMENT 'Has the person in charge been notified?'
);
Copy the code

Trigger function analysis

Since the val1 -> val2 state needs to be logged, this can only be triggered before the update

Because there are too many fields (70+ columns) in the material table, and they may not all be used, it is not convenient to do one-to-one mapping storage in the log table. For example, record 1 uses COL1, COL2, and record 2 uses COL1, col3. Then an appropriate solution is to store changing data in the form of string. The format of the storage is defined as follows:

Field 1_ old value _ New value Field 3_ old value _ New valueCopy the code

When the trigger is executed, it needs to check whether the value of the field of interest is consistent with the value of the old one and the new one, and if it is inconsistent, it needs to record it and append it to the temporary string variable STR

After all the checks are complete, we check whether STR value is “”, if yes, there is no need to record the log table

In the notification part, the string can be directly intercepted + formatted and displayed, and then sent to the person in charge by email. This article will not pay attention to this detail

Flip-flop writing

From the above analysis, it is clear that the body of the trigger has many SQL operations, and therefore requires the begin End code block

A STR variable of type string is required to receive the result of the concatenation

You need to use IF to determine whether the old and new values of a key field have changed, and only IF they have changed should you concatenate them into the STR variable

After the data is processed and concatenated to STR, conditions are used to determine whether the record needs to be inserted into the log table

The final effect of the trigger is as follows (which omits a lot of if and old values) :

DELIMITER //
drop TRIGGER if EXISTS trigger_recordMdmBasicsUpdate ;
CREATE TRIGGER trigger_recordMdmBasicsUpdate BEFORE UPDATE ON mdm_basics FOR EACH ROW
BEGIN
    DECLARE str LONGTEXT DEFAULT ' ';
    DECLARE split_op LONGTEXT DEFAULT ',,.;
	IF old.mdm_name! =new.mdm_name THEN
		set str = CONCAT_WS(split_op,'Material Name',old.mdm_name,new.mdm_name,'\n');
	END IF;
	IF old.mdm_note! =new.mdm_note THEN
		set str = CONCAT_WS(split_op,'Material Description (Chinese)',old.mdm_note,new.mdm_note,'\n');
	END IF;
	if str ! = ' ' and old.mdm_code ! = ' ' then 
		insert into mdm_basics_change_log(mdm_code,update_by,update_name,change_data) VALUES (new.mdm_code,new.update_by,new.update_name,str);
	end if;
END
//
DELIMITER ;
Copy the code

Original article, without permission, prohibit reprinting

-Leonard: Create by the comfort of salt fish