Untitled

SET NOCOUNT ON;
use [Cerberus];

DECLARE cur_tables CURSOR FOR (SELECT name FROM sys.tables where name like '_Node%' and name not like '%VT%' AND type = 'U');
DECLARE @nameTables sysname;
DECLARE @sql nvarchar(max);
DECLARE @fields nvarchar(max);
DECLARE @log_db nvarchar(50);

SET @log_db = '[locks_monitor]' -- база логов
SET @fields = '_IDRRef,_Marked,_Code,_Description';


OPEN cur_tables

FETCH NEXT FROM cur_tables
INTO @nameTables;

WHILE @@FETCH_STATUS = 0
BEGIN

 IF @sql IS NULL
  SET @sql = 'SELECT '''+@nameTables+''' AS [TableName], '+@fields+' FROM '+@nameTables+' (nolock)';
 ELSE
  SET @sql = @sql + ' UNION SELECT '''+@nameTables+''' AS [TableName], '+@fields+' FROM '+@nameTables+' (nolock)';

 FETCH NEXT FROM cur_tables
 INTO @nameTables;
END

CLOSE cur_tables;
DEALLOCATE cur_tables;


SET @sql = '
WITH CTE_Nodes
(
 [TableName], '+@fields+'
) AS (
' + @sql + ')
 SELECT TableName,'+@fields+' INTO #tmpNodes FROM CTE_Nodes;

 --select * from #tmpNodes
 
 -- срез последних настроек по дате
 SELECT 
	MAX(CreatedDate) as MaxDate, 
	_IDRRef as _IDRRef
 INTO #tmpCurrentMaxDate
 FROM ' + @log_db + '.[dbo].[_Nodes]

 GROUP BY _IDRRef

 Select nodes.* 
 INTO #tmpCurrentNodes
 FROM '
 + @log_db + '.[dbo].[_Nodes] as nodes
 INNER JOIN #tmpCurrentMaxDate as maxDate
	on nodes.CreatedDate = maxDate.MaxDate
	and nodes._IDRRef = maxDate._IDRRef
 
 -- Выбор измененных заданий
 SELECT tmpNds.*,
	CASE
		WHEN currentNodes._IDRRef is NULL THEN ''Новый план''
		WHEN tmpNds._Description <> currentNodes._Description THEN ''Изменилось название''
		WHEN tmpNds._Marked <> currentNodes._Marked THEN ''Изменилась пометка удаления''
		WHEN tmpNds._Code <> currentNodes._Code THEN ''Изменился код''
	END as TypeChange
 INTO #differentRecord
 from #tmpNodes as tmpNds
 left join #tmpCurrentNodes as currentNodes
	on tmpNds._IDRRef = currentNodes._IDRRef
 WHERE
	 currentNodes._IDRRef is NULL
	 or tmpNds._Marked <> currentNodes._Marked
	 or tmpNds._Description <> currentNodes._Description
	 or tmpNds._Code <> currentNodes._Code

-----------
SELECT
	CURRENT_TIMESTAMP as CreatedDate,
	''Изменение плана обмена'' as Description, 
	CONCAT(dr._Description, '' : '', dr.TypeChange) as Message
 INTO #tmpSJEnd
 FROM #differentRecord as dr

 -- Фиксация настроек измененных заданий
 INSERT INTO'+ @log_db +'.[dbo].[_Nodes]
 SELECT CURRENT_TIMESTAMP, TableName, ' + @fields + ', TypeChange
 FROM #differentRecord

 -- Запись в таблицу уведомлений
 INSERT INTO ' + @log_db + '.[dbo].[Notification]
 SELECT 
	*
 FROM #tmpSJEnd

 -- Селект для Заббикса
 SELECT message
 FROM #tmpSJEnd
  
 
 DROP TABLE #tmpCurrentMaxDate;
 DROP TABLE #tmpCurrentNodes;
 DROP Table #differentRecord;
 DROP TABLE #tmpNodes;
 DROP TABLE #tmpSJEnd;'

EXEC(@sql);