The BEGIN 
IF NEW. PriceType=1 & amp; & New. ItemId is not null Then 
SELECT ItemType, Status INTO @ ItemType, @ ItemStatus FROM TtProposalXItems WHERE ItemId=New. The ItemId. 
IF @ ItemType=5 or @ ItemType=7 Then 
IF NEW. ExpenseData IS NULL OR NEW. ExpenseData.='OR NEW ExpenseData.=' https://bbs.csdn.net/topics/{} 'OR NEW ExpenseData=' https://bbs.csdn.net/topics/null '
THEN 
The SET NEW.ExpenseData=https://bbs.csdn.net/topics/JSON_COMPACT (JSON_OBJECT (' payProgress ', '00')); 
The ELSE 
The SET NEW.ExpenseData=https://bbs.csdn.net/topics/JSON_COMPACT (JSON_SET (NEW ExpenseData, '$. PayProgress', '00')); 
END the IF; 
The SET NEW.ExpenseData=https://bbs.csdn.net/topics/Json_compact (New ExpenseData); 
SELECT Code into @ Code FROM FnTenants WHERE TenantId=NEW. The additional TenantId; 
IF (@ Code='XXXXX') 
THEN 
The SET @ isNeedUploadInvoice='true'; 
The ELSE 
The SELECT JSON_VALUE (ExtData, '$. IsNeedUploadInvoice) into @ isNeedUploadInvoice 
The FROM FnDictionaryValView 
WHERE KeyCode='CPC - City AND Val=(SELECT CityDictVal 
The FROM TtProposals WHERE ProposalId=New. ProposalId); 
END the IF; 
IF NEW. PayStatus=0 THEN 
The SET NEW.ExpenseData=https://bbs.csdn.net/topics/Json_replace (New ExpenseData, '$. PayProgress', '00'); 
ELSEIF NEW. 10 AND @ isNeedUploadInvoice PayStatus=='true' THEN 
SET NEW.ExpenseData=https://bbs.csdn.net/topics/Json_replace (New ExpenseData, '$. PayProgress', '6'); 
ELSEIF NEW. 20 AND @ isNeedUploadInvoice PayStatus=='true' THEN 
SET NEW.ExpenseData=https://bbs.csdn.net/topics/Json_replace (New ExpenseData, '$. PayProgress', '6'); 
ELSEIF NEW. 30 AND @ isNeedUploadInvoice PayStatus=='true' THEN 
SET NEW.ExpenseData=https://bbs.csdn.net/topics/Json_replace (NEW ExpenseData, '$. PayProgress', '6'); 
ELSEIF NEW. 40 AND @ isNeedUploadInvoice PayStatus=='true' THEN 
SET NEW.ExpenseData=https://bbs.csdn.net/topics/Json_replace (New ExpenseData, '$. PayProgress', '6'); 
The Else 
The SET New.ExpenseData=https://bbs.csdn.net/topics/Json_replace (New ExpenseData, '$. PayProgress', '10'); 
End the IF; 
IF @ ItemStatus=5 Then 
The SET New.ExpenseData=https://bbs.csdn.net/topics/Json_replace (New ExpenseData, '$. PayProgress', '10'); 
End the IF; 
End the IF; 
End the IF; 
END 

CodePudding user response:

SQL program code written clear
Code to access multiple tables and views: TtProposalXItems, FnTenants, FnDictionaryValView, if this Before the INSERT trigger in the table is different from the above tables and views, and not a deadlock, if view associated with the trigger in the table, as long as the view is manually update view (not dynamic view), a deadlock problem will not appear,

CodePudding user response:

The database is not recommended and trigger and trigger or so complex logic

CodePudding user response:

Try not to use the trigger, especially for the row of that kind of trigger, impact performance

CodePudding user response:

This kind of demand, it is better to let the program implementation, I see you in the code to handle json,,,