BEGIN
IF NEW.PriceType=1 && New.ItemId is not null Then
SELECT ItemType,Status INTO @ItemType,@ItemStatus FROM TtProposalXItems WHERE ItemId=New.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
SET NEW.ExpenseData=https://bbs.csdn.net/topics/JSON_COMPACT(JSON_OBJECT('payProgress','00'));
ELSE
SET NEW.ExpenseData=https://bbs.csdn.net/topics/JSON_COMPACT(JSON_SET(NEW.ExpenseData,'$.payProgress','00'));
END IF;
SET NEW.ExpenseData=https://bbs.csdn.net/topics/Json_compact(New.ExpenseData);
SELECT Code into @Code FROM FnTenants WHERE TenantId=NEW.TenantId;
IF(@Code='XXXXX')
THEN
SET @isNeedUploadInvoice='true';
ELSE
SELECT JSON_VALUE(ExtData,'$.isNeedUploadInvoice') into @isNeedUploadInvoice
FROM FnDictionaryValView
WHERE KeyCode='CPC-City' AND Val=(SELECT CityDictVal
FROM TtProposals WHERE ProposalId=New.ProposalId);
END IF;
IF NEW.PayStatus=0 THEN
SET NEW.ExpenseData=https://bbs.csdn.net/topics/Json_replace(New.ExpenseData,'$.payProgress','00');
ELSEIF NEW.PayStatus=10 AND @isNeedUploadInvoice='true' THEN
SET NEW.ExpenseData=https://bbs.csdn.net/topics/Json_replace(New.ExpenseData,'$.payProgress','06');
ELSEIF NEW.PayStatus=20 AND @isNeedUploadInvoice='true' THEN
SET NEW.ExpenseData=https://bbs.csdn.net/topics/Json_replace(New.ExpenseData,'$.payProgress','06');
ELSEIF NEW.PayStatus=30 AND @isNeedUploadInvoice='true' THEN
SET NEW.ExpenseData=https://bbs.csdn.net/topics/Json_replace(NEW.ExpenseData,'$.payProgress','06');
ELSEIF NEW.PayStatus=40 AND @isNeedUploadInvoice='true' THEN
SET NEW.ExpenseData=https://bbs.csdn.net/topics/Json_replace(New.ExpenseData,'$.payProgress','06');
Else
SET New.ExpenseData=https://bbs.csdn.net/topics/Json_replace(New.ExpenseData,'$.payProgress','10');
End IF;
IF @ItemStatus=5 Then
SET New.ExpenseData=https://bbs.csdn.net/topics/Json_replace(New.ExpenseData,'$.payProgress','10');
End IF;
End IF;
End IF;
END
uj5u.com熱心網友回復：
SQL程式的代碼寫的很清晰
代碼中訪問了多個表和視圖：TtProposalXItems、FnTenants、FnDictionaryValView，如果這個Before INSERT觸發器在的表跟上述表和視圖不同，不會出現死鎖。如果視圖跟觸發器在的表相關，只要視圖是手工更新的視圖（不是動態視圖），死鎖問題也不會出現。
uj5u.com熱心網友回復：
不建議在資料庫加觸發器 并且還是這么復雜邏輯的觸發器uj5u.com熱心網友回復：
盡量不要用觸發器，特別是for row的那種觸發器，很影響性能uj5u.com熱心網友回復：
這種需求，最好讓程式實作，我看你代碼里還要處理json。。。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/106440.html
標籤：MySQL