USE [weightsDB]
GO
/****** Object: Trigger [db_owner].[UpdateWight2] Script Date: 2021/2/1 8:16:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [db_owner].[UpdateWight2]
ON [db_owner].[weights]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare
@insert_equipid as varchar,
@insert_equipWeight as float
select @insert_equipid=EquipId,@insert_equipWeight=ScalesWeight from inserted
SET NOCOUNT ON;
-- Insert statements for trigger here
if exists(select * from db_owner.nowWeight where EquipID=@insert_equipid)
update db_owner.nowWeight set nowWeight=@insert_equipWeight where EquipID=@insert_equipid
else
insert into db_owner.nowWeight select EquipId,ScalesWeight from inserted
END
上面是表的列。
我想讓觸發器檢查到weights表有新記錄時，檢查nowWeight表，判斷EquipID是否存在，如果存在：修改它對應的nowWeight欄位；如果不存在，則添加新記錄。
但是實際運行后的結果：
請大佬幫忙指正。
uj5u.com熱心網友回復：
如果單次對weights表插入多條資料，那“select @insert_equipid=EquipId,@insert_equipWeight=ScalesWeight from inserted”這樣只能取到其中一條資料，可能會造成后續的 IF exists的判斷錯誤。你改成下面的試試
ALTER TRIGGER [db_owner].[UpdateWight2]
ON [db_owner].[weights]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare
@insert_equipid as varchar,
@insert_equipWeight as float
--select @insert_equipid=EquipId,@insert_equipWeight=ScalesWeight from inserted
SET NOCOUNT ON;
-- Insert statements for trigger here
if exists(select * from db_owner.nowWeight A join inserted B ON A.EquipID=B.EquipID)
update db_owner.nowWeight
set nowWeight=B.ScalesWeight
from db_owner.nowWeight A
join inserted B ON A.EquipID=B.EquipID
else
insert into db_owner.nowWeight
select EquipId,ScalesWeight from inserted WHERE EquipId NOT IN (select EquipId from db_owner.nowWeight)
END
uj5u.com熱心網友回復：
你這個就不用判斷是否存在，直接用merge就可以了。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/256117.html
標籤：疑難問題
上一篇：三表聯合查詢,好像有些復雜
下一篇：視圖能使用臨時表嗎？