请各位大侠，帮忙看一下下面触发器Before Insert 语句是否会引起死锁问题？

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='{}' OR NEW.ExpenseData='null'
THEN
SET NEW.ExpenseData=JSON_COMPACT(JSON_OBJECT('payProgress','00'));
ELSE
SET NEW.ExpenseData=JSON_COMPACT(JSON_SET(NEW.ExpenseData,'$.payProgress','00'));
END IF;
SET NEW.ExpenseData=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=Json_replace(New.ExpenseData,'$.payProgress','00');
ELSEIF NEW.PayStatus=10 AND @isNeedUploadInvoice='true' THEN
SET NEW.ExpenseData=Json_replace(New.ExpenseData,'$.payProgress','06');
ELSEIF NEW.PayStatus=20 AND @isNeedUploadInvoice='true' THEN
SET NEW.ExpenseData=Json_replace(New.ExpenseData,'$.payProgress','06');
ELSEIF NEW.PayStatus=30 AND @isNeedUploadInvoice='true' THEN
SET NEW.ExpenseData=Json_replace(NEW.ExpenseData,'$.payProgress','06');
ELSEIF NEW.PayStatus=40 AND @isNeedUploadInvoice='true' THEN
SET NEW.ExpenseData=Json_replace(New.ExpenseData,'$.payProgress','06');
Else
SET New.ExpenseData=Json_replace(New.ExpenseData,'$.payProgress','10');
End IF;
IF @ItemStatus=5 Then
SET New.ExpenseData=Json_replace(New.ExpenseData,'$.payProgress','10');
      End IF;
End IF;
End IF;
END

4 个解决方案

#1

SQL程序的代码写的很清晰请各位大侠，帮忙看一下下面触发器Before Insert 语句是否会引起死锁问题？

代码中访问了多个表和视图：TtProposalXItems、FnTenants、FnDictionaryValView，如果这个Before INSERT触发器在的表跟上述表和视图不同，不会出现死锁。如果视图跟触发器在的表相关，只要视图是手工更新的视图（不是动态视图），死锁问题也不会出现。

#2

不建议在数据库加触发器并且还是这么复杂逻辑的触发器

#3

尽量不要用触发器，特别是for row的那种触发器，很影响性能

#4

这种需求，最好让程序实现，我看你代码里还要处理json。。。

#1