Want to use the trigger of the corresponding number when there is a record of the changes, no added.-CodePudding

 USE [weightsDB] 
GO 
/* * * * * * Object: the Trigger [db_owner] [UpdateWight2] Script Date: 2021/2/1 8:16:52 * * * * * */
The SET ANSI_NULLS ON 
GO 
The SET QUOTED_IDENTIFIER ON 
GO 
-=============================================
Author: & lt; The Author, Name> 
- the Create date: & lt; The Create Date,, & gt; 
- Description: & lt; The Description, & gt; 
-=============================================
ALTER the TRIGGER [db_owner] [UpdateWight2] 
ON [db_owner] [weights] 
AFTER INSERT 
AS 
The BEGIN 
- SET NOCOUNT ON added to prevent extra result sets from 
- interfering with the 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 the trigger here 
If the exists (select * from db_owner. NowWeight where EquipID=@ insert_equipid) 
The update db_owner. NowWeight set nowWeight=@ insert_equipWeight where EquipID=@ insert_equipid 
The else 
Insert into db_owner. NowWeight select EquipId, ScalesWeight from inserted 
END

The above is the columns of the table,

I want to trigger weights table has a new record, check nowWeight table, whether EquipID exists, if there is: modify its corresponding nowWeight field; If not, then add a new record,
But after the actual operation results:

Please correct me big help,

CodePudding user response:

IF more than single of weights table insert data, the "select @ insert_equipid=EquipId, @ insert_equipWeight=ScalesWeight from inserted" can only take one of the data that may cause a subsequent IF exists errors of judgment,

You to try the following

 
ALTER the TRIGGER [db_owner] [UpdateWight2] 
ON [db_owner] [weights] 
AFTER INSERT 
AS 
The BEGIN 
- SET NOCOUNT ON added to prevent extra result sets from 
- interfering with the 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 the trigger here 
If the exists (select * from db_owner. NowWeight A join inserted ON B A.E quipID=B.E quipID) 
The update db_owner. NowWeight 
The set nowWeight=B.S calesWeight 
The from db_owner. NowWeight A 
The join inserted ON B A.E quipID=B.E quipID 
The else 
Insert into db_owner. NowWeight 
The select EquipId, ScalesWeight from inserted WHERE EquipId NOT IN (select EquipId from db_owner. NowWeight) 
END

CodePudding user response:

You don't have to determine whether there is any, it is ok to directly use the merge,