Project

General

Profile

Download (1.39 KB)

Экстендер MdbHistoryBrowser » PH_HISTORY_Update.sql

Sergey Kolyshkin, 26/01/2023 18:17

 
--USE [pioner]

DECLARE @ver int
SET @ver = 4

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[components]') AND type in (N'U'))
BEGIN
IF EXISTS( SELECT veri FROM [components] WHERE name='mdb.ph_history' AND veri>=@ver )
BEGIN
PRINT N'Current PH_HISTORY version is equal or higher than '+ CAST(@ver AS varchar(10))
--RAISERROR('Script terminated...',16,1)
RETURN
END
END
ELSE
BEGIN
PRINT N'Components table not found. Script exit'
RETURN
END

BEGIN TRY

BEGIN TRAN

PRINT N'Script begin'

--добавляем колонку

EXEC sp_executesql
N'
ALTER TABLE [dbo].[PH_HISTORY] ADD [GroupID] [int] NULL

PRINT ''Column GroupID added to table [PH_HISTORY]''
'

-- вычисляем значение для этой колонки
EXEC sp_executesql
N'
UPDATE ph_hist
SET ph_hist.GroupID =
(
SELECT MIN(ph_hist_sub.ID)
FROM PH_HISTORY as ph_hist_sub
WHERE ph_hist_sub.PH_ID = ph_hist.PH_ID
AND ph_hist_sub.UserName = ph_hist.UserName
AND ph_hist_sub.HostName = ph_hist.HostName
AND ABS(DATEDIFF(second, ph_hist_sub.ModifyDate, ph_hist.ModifyDate)) < 5
)
FROM [dbo].[PH_HISTORY] AS ph_hist
WHERE ph_hist.GroupID IS NULL

PRINT ''GroupID calculated for existing items''
'


UPDATE [dbo].[components]
SET veri = @ver
WHERE name='mdb.ph_history'

COMMIT TRAN

PRINT N'Script end!'

END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT N'Script error'
END CATCH;
(5-5/7)
Add picture from clipboard (Maximum size: 742 MB)