在SQL中插入触发器问题 - 如何解决？ [英] Insert Trigger issue in SQL - How to resolve?

问题描述

这是我的SQL触发器代码。触发器将根据公差时间从table1到table3插入记录以获取活动服务。 table3在某些轮询间隔中被清除。在表3中插入（从触发器）和删除（轮询间隔）发生



例如，如果活动服务计数为3，但是记录只插入两个服务。

This is my SQL trigger code. trigger will insert the record from table1 to table3 for active services based on tolerancetime. table3 is getting cleared in certain polling interval.in table3 insertion (from trigger) and deletion (polling interval) happens

for example if active service count is 3 , but the record inserted for only two services.

ALTER TRIGGER [dbo].[insTrigger]
ON [dbo].[table1]
AFTER INSERT
AS
BEGIN
	
	DECLARE @servicecount INT   
	DECLARE @count INT	  
	DECLARE @tolerancetime INT	
	
	
	SET @tolerancetime =60
	SET @servicecount = (SELECT count(1) FROM [dbo].[table2] WITH (NOLOCK) WHERE servicetime >= DATEADD(mi,-@tolerancetime,GETDATE())) 
  
	
  IF(@servicecount != 0)

   BEGIN	 

	-- Looping till active service count
      WHILE(@count <= @servicecount)
	   BEGIN
	
	INSERT INTO table3 
			SELECT logid
			servicetype,			
			(SELECT rn.servicename FROM 
			(SELECT  row_number() OVER(ORDER BY t2.servicename ASC) AS rowno,t2.servicename,t2.servicetime FROM table2 t2 
			 WHERE t2.servicetime >= DATEADD(mi,-@tolerancetime,GETDATE())) rn
			 WHERE rn.rowno=@count)			 
			FROM inserted  
		WHERE 
			servicetype = started
	  	    AND 
			servicestarttime >= (SELECT t2.servicetime FROM(SELECT  row_number() OVER(ORDER BY t2.servicename ASC) AS rowno,t2.servicetime FROM table2 t2 
				WHERE t2.servicetime >= DATEADD(mi,-@tolerancetime,GETDATE())) rn
				WHERE rn.rowno = @count)
			
		SET @count = @count + 1
								
	   END
   END
END

请建议任何想法解决此问题以插入所有服务的记录

please suggest any idea to resolve this issue to insert record for all services

推荐答案

ROW_NUMBER（Transact-SQL） [ ^ ]从1开始，但你的循环从0开始...所以没有第0行，它插入第1行和第2行，永远不会到达插入第3行。



初始化计数为1

ROW_NUMBER (Transact-SQL)[^] starts from 1 but your loop starts from 0... so there is no row 0, it inserts rows 1 and 2 and never gets to insert row 3.

Initialise count to 1

这篇关于在SQL中插入触发器问题 - 如何解决？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！