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
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)