324 lines
12 KiB
Transact-SQL
324 lines
12 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS dev.uspIntezmenyRemoveDelete
|
|
GO
|
|
|
|
CREATE PROCEDURE dev.uspIntezmenyRemoveDelete
|
|
@pIntezmenyIds nvarchar(1000)
|
|
,@pIsArchivTanevTorles bit = 0
|
|
,@pDebugMode bit = 0
|
|
AS
|
|
BEGIN
|
|
DECLARE
|
|
@tableName nvarchar(100)
|
|
,@intezmenyIdColumn nvarchar(50)
|
|
,@tanevIdColumn nvarchar(50)
|
|
,@sql nvarchar(max)
|
|
,@firstParentCol nvarchar(50)
|
|
,@firstRefObj nvarchar(50)
|
|
,@secondParentCol nvarchar(50)
|
|
,@secondRefObj nvarchar(50)
|
|
,@message nvarchar(max)
|
|
,@rowCount int
|
|
|
|
-- ==================================================
|
|
-- Index az ENTITYHISTORY-ra
|
|
-- ==================================================
|
|
IF @pIntezmenyIds IS NULL AND @pIsArchivTanevTorles = 1 BEGIN
|
|
PRINT 1
|
|
SET @sql = '
|
|
SELECT @pIntezmenyIds = CONCAT(@pIntezmenyIds + '','', i.ID) FROM dbo.T_INTEZMENY i WHERE i.TOROLT = ''F''
|
|
'
|
|
PRINT @sql
|
|
EXEC sp_executesql @sql, N'@pIntezmenyIds nvarchar(1000) output', @pIntezmenyIds output
|
|
END
|
|
|
|
|
|
|
|
|
|
-- Archiv tanévek törléséhez kell a temp tábla
|
|
CREATE TABLE #Tanev (TanevId int PRIMARY KEY)
|
|
|
|
IF @pIsArchivTanevTorles = 1 BEGIN
|
|
|
|
SET @sql = '
|
|
SELECT tv.ID AS TanevId
|
|
FROM dbo.T_TANEV tv
|
|
WHERE tv.C_ARCHIVADATBAZIS IS NOT NULL
|
|
AND tv.C_INTEZMENYID IN (' + @pIntezmenyIds + ')
|
|
'
|
|
PRINT @pIntezmenyIds
|
|
|
|
PRINT @sql
|
|
INSERT INTO #Tanev (TanevId)
|
|
EXEC sp_executesql @sql
|
|
|
|
UPDATE tm SET
|
|
tm.C_FELMENTESROGZITOTANARID = NULL,
|
|
tm.C_FELMENTESROGZITOTANARNEV = fh.C_NYOMTATASINEV
|
|
FROM T_TANULOMENTESSEG tm
|
|
INNER JOIN T_FELHASZNALO fh ON fh.ID = tm.C_FELMENTESROGZITOTANARID
|
|
INNER JOIN #Tanev tmp ON tmp.TanevId = fh.C_TANEVID
|
|
INNER JOIN T_TANEV tv ON tv.ID = tm.C_TANEVID AND tv.C_ARCHIVADATBAZIS IS NULL
|
|
WHERE tm.C_TANEVID <> fh.C_TANEVID
|
|
|
|
UPDATE z SET
|
|
z.C_ROGZITOID = NULL,
|
|
z.C_ROGZITONEV = fh.C_NYOMTATASINEV
|
|
FROM T_ZARADEK z
|
|
INNER JOIN T_FELHASZNALO fh ON fh.ID = z.C_ROGZITOID
|
|
INNER JOIN #Tanev tmp ON tmp.TanevId = fh.C_TANEVID
|
|
INNER JOIN T_TANEV tv ON tv.ID = z.C_TANEVID AND tv.C_ARCHIVADATBAZIS IS NULL
|
|
WHERE z.C_TANEVID <> fh.C_TANEVID
|
|
|
|
UPDATE z SET
|
|
z.C_UTOLSOMODOSITOID = NULL,
|
|
z.C_UTOLSOMODOSITONEV = fh.C_NYOMTATASINEV
|
|
FROM T_ZARADEK z
|
|
INNER JOIN T_FELHASZNALO fh ON fh.ID = z.C_ROGZITOID
|
|
INNER JOIN #Tanev tmp ON tmp.TanevId = fh.C_TANEVID
|
|
INNER JOIN T_TANEV tv ON tv.ID = z.C_TANEVID AND tv.C_ARCHIVADATBAZIS IS NULL
|
|
WHERE z.C_TANEVID <> fh.C_TANEVID
|
|
|
|
-- Ha kikerül az adatbázisból a két oszlop, akkor ezt is törölni kell az SP-ből
|
|
UPDATE z SET
|
|
z.C_ORATULAJDONOSID = NULL
|
|
FROM T_ORARENDIORA z
|
|
INNER JOIN T_FELHASZNALO fh ON fh.ID = z.C_ORATULAJDONOSID
|
|
INNER JOIN #Tanev tmp ON tmp.TanevId = fh.C_TANEVID
|
|
INNER JOIN T_TANEV tv ON tv.ID = z.C_TANEVID AND tv.C_ARCHIVADATBAZIS IS NULL
|
|
WHERE z.C_TANEVID <> fh.C_TANEVID
|
|
|
|
UPDATE z SET
|
|
z.C_TULAJDONOSID = NULL
|
|
FROM T_NEMKOTOTTMUNKAIDO z
|
|
INNER JOIN T_FELHASZNALO fh ON fh.ID = z.C_TULAJDONOSID
|
|
INNER JOIN #Tanev tmp ON tmp.TanevId = fh.C_TANEVID
|
|
INNER JOIN T_TANEV tv ON tv.ID = z.C_TANEVID AND tv.C_ARCHIVADATBAZIS IS NULL
|
|
WHERE z.C_TANEVID <> fh.C_TANEVID
|
|
|
|
|
|
END
|
|
|
|
IF @pIsArchivTanevTorles = 0 BEGIN
|
|
|
|
SET @message = 'CREATING ENTITYHISTORY INDEX... - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
|
|
SET @sql = N'
|
|
DROP INDEX IF EXISTS IX_TMP_EntityHistory_IntezmenyId_Delete
|
|
ON dbo.T_ENTITYHISTORY
|
|
|
|
CREATE NONCLUSTERED INDEX IX_TMP_EntityHistory_IntezmenyId_Delete
|
|
ON dbo.T_ENTITYHISTORY (C_INTEZMENYID)
|
|
WHERE C_INTEZMENYID IN (' + @pIntezmenyIds + ')
|
|
'
|
|
|
|
IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql
|
|
SET @message = 'CREATING ENTITYHISTORY INDEX DONE - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
|
|
END
|
|
|
|
RAISERROR ('DATA DELETION... ', 10, 1) WITH NOWAIT
|
|
|
|
-- ==================================================
|
|
-- ENTITYATTRIBUTEHISTORY & ENTITYBLOBVALUES törlés
|
|
-- ==================================================
|
|
IF @pIsArchivTanevTorles = 0 BEGIN
|
|
SET @sql = N'
|
|
DELETE eb
|
|
FROM T_ENTITYBLOBVALUES eb
|
|
INNER JOIN T_ENTITYHISTORY e ON e.ID = eb.C_ENTITYHISTORYID
|
|
WHERE e.C_INTEZMENYID IN (' + @pIntezmenyIds + ')
|
|
SET @rowCount = @@ROWCOUNT
|
|
'
|
|
|
|
IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql, N'@rowCount int output', @rowCount output
|
|
SET @message = ' DEL-A ' + FORMAT(GETDATE(), 'HH:mm:ss.ff') + ' - T_ENTITYBLOBVALUES - ' + CAST(@rowCount AS nvarchar(20)) + ' sor'
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
|
|
SET @sql = N'
|
|
DELETE FROM T_ENTITYATTRIBUTEHISTORY
|
|
WHERE EXISTS (SELECT 1 FROM T_ENTITYHISTORY eh WHERE eh.ID = C_ENTITYHISTORYID AND eh.C_INTEZMENYID IN (' + @pIntezmenyIds + '))
|
|
SET @rowCount = @@ROWCOUNT
|
|
'
|
|
|
|
IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql, N'@rowCount int output', @rowCount output
|
|
SET @message = ' DEL-A ' + FORMAT(GETDATE(), 'HH:mm:ss.ff') + ' - T_ENTITYATTRIBUTEHISTORY - ' + CAST(@rowCount AS nvarchar(20)) + ' sor'
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
|
|
SET @sql = N'
|
|
DELETE eh
|
|
FROM T_ENTITYHISTORY eh
|
|
WHERE eh.C_INTEZMENYID IN (' + @pIntezmenyIds + ')
|
|
SET @rowCount = @@ROWCOUNT
|
|
'
|
|
|
|
IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql, N'@rowCount int output', @rowCount output
|
|
SET @message = ' DEL-A ' + FORMAT(GETDATE(), 'HH:mm:ss.ff') + ' - T_ENTITYHISTORY - ' + CAST(@rowCount AS nvarchar(20)) + ' sor'
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
|
|
END
|
|
|
|
-- ==================================================
|
|
-- Kapcsolótábla törlés
|
|
-- ==================================================
|
|
SET @sql = N''
|
|
|
|
DECLARE tbl_cur CURSOR LOCAL FOR
|
|
SELECT t.name
|
|
FROM sys.tables t
|
|
WHERE NOT EXISTS (SELECT 1 FROM sys.columns c WHERE c.object_id = t.object_Id AND c.name = 'ID')
|
|
AND NOT EXISTS (SELECT 1 FROM sys.columns c WHERE c.object_id = t.object_Id AND c.name = 'C_ALINTEZMENYID')
|
|
AND NOT EXISTS (SELECT 1 FROM sys.columns c WHERE c.object_id = t.object_Id AND c.name = 'C_INTEZMENYID')
|
|
AND t.name LIKE 'T[_]%'
|
|
AND t.schema_id = 1
|
|
AND t.name NOT IN ('T_ENTITYBLOBVALUES', 'T_AMIFOTARGY_AMIALTARGY', 'T_ENTITYATTRIBUTEHISTORY')
|
|
OPEN tbl_cur
|
|
FETCH NEXT FROM tbl_cur
|
|
INTO @tableName
|
|
|
|
WHILE @@FETCH_STATUS = 0 BEGIN
|
|
|
|
SELECT TOP(1)
|
|
@firstParentCol = pc.[name]-- AS parent_column_name
|
|
,@firstRefObj = OBJECT_NAME(rc.[object_id]) --as referenced_object_name
|
|
FROM sys.foreign_key_columns f
|
|
INNER JOIN sys.columns pc ON pc.[object_id] = f.parent_object_id AND pc.column_id = f.parent_column_id
|
|
INNER JOIN sys.columns rc ON rc.[object_id] = f.referenced_object_id AND rc.column_id = f.referenced_column_id
|
|
WHERE parent_object_id = OBJECT_ID(@tableName)
|
|
ORDER BY f.constraint_object_id ASC
|
|
|
|
SELECT TOP(1)
|
|
@secondParentCol = pc.[name]-- AS parent_column_name
|
|
,@secondRefObj = OBJECT_NAME(rc.[object_id]) --as referenced_object_name
|
|
FROM sys.foreign_key_columns f
|
|
INNER JOIN sys.columns pc ON pc.[object_id] = f.parent_object_id AND pc.column_id = f.parent_column_id
|
|
INNER JOIN sys.columns rc ON rc.[object_id] = f.referenced_object_id AND rc.column_id = f.referenced_column_id
|
|
WHERE parent_object_id = OBJECT_ID(@tableName)
|
|
ORDER BY f.constraint_object_id DESC
|
|
|
|
SET @sql = N'
|
|
DELETE x
|
|
FROM dbo.' + @tableName + ' x
|
|
INNER JOIN dbo.' + @firstRefObj + ' s ON s.ID = x.' + @firstParentCol + ' AND s.C_' + IIF(@firstRefObj IN ('T_TANULO', 'T_ALKALMAZOTT', 'T_OSZTALY', 'T_CSOPORT'), 'AL', '') + 'INTEZMENYID IN (' + @pIntezmenyIds + ')' + '
|
|
INNER JOIN dbo.' + @secondRefObj + ' z ON z.ID = x.' + @secondParentCol + ' AND z.C_' + IIF(@secondRefObj IN ('T_TANULO', 'T_ALKALMAZOTT', 'T_OSZTALY', 'T_CSOPORT'), 'AL', '') + 'INTEZMENYID IN (' + @pIntezmenyIds + ')' + '
|
|
' + IIF(@pIsArchivTanevTorles = 1, 'INNER JOIN #Tanev t ON t.TanevId = s.C_' + IIF(@firstRefObj IN ('T_TANULO', 'T_ALKALMAZOTT', 'T_OSZTALY', 'T_CSOPORT'), 'AL', '') + 'TANEVID ', '') + '
|
|
SET @rowCount = @@ROWCOUNT
|
|
'
|
|
|
|
IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql, N'@rowCount int output', @rowCount output
|
|
SET @message = ' DEL-B ' + FORMAT(GETDATE(), 'HH:mm:ss.ff') + ' - ' + @tableName + ' - ' + CAST(@rowCount AS nvarchar(20)) + ' sor'
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
|
|
FETCH NEXT FROM tbl_cur
|
|
INTO @tableName
|
|
END
|
|
|
|
CLOSE tbl_cur
|
|
DEALLOCATE tbl_cur
|
|
|
|
-- ==================================================
|
|
-- Normál tábla törlés
|
|
-- ==================================================
|
|
DECLARE tbl_cur CURSOR LOCAL FOR
|
|
SELECT DISTINCT t.name AS TableName, c.name AS IntezmenyIdColumn, c2.name AS TanevIdColumn--, COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), 'ID', 'IsIdentity')
|
|
FROM sys.tables t
|
|
INNER JOIN sys.columns c ON c.object_id = t.object_id
|
|
LEFT JOIN sys.columns c2 ON c2.object_id = t.object_id AND c2.name IN (N'C_ALTANEVID', N'C_TANEVID')
|
|
WHERE c.name IN (N'C_ALINTEZMENYID', N'C_INTEZMENYID')
|
|
AND t.name NOT IN (IIF(@pIsArchivTanevTorles = 1, 'T_ENTITYHISTORY', ''))
|
|
|
|
AND t.name LIKE 'T[_]%'
|
|
AND t.schema_id = 1
|
|
ORDER BY t.name
|
|
|
|
OPEN tbl_cur
|
|
FETCH NEXT FROM tbl_cur
|
|
INTO @tableName, @intezmenyIdColumn, @tanevIdColumn
|
|
|
|
WHILE @@FETCH_STATUS = 0 BEGIN
|
|
|
|
-- PRINT @tableName
|
|
-- Achive törléskor csak azokból a táblákból törlünk, ahol van C_(AL)TANEVID oszlop
|
|
IF @pIsArchivTanevTorles = 1 OR @tanevIdColumn IS NOT NULL BEGIN
|
|
SET @sql = N'
|
|
DELETE x
|
|
FROM dbo.' + @tableName + ' x
|
|
' + IIF(@pIsArchivTanevTorles = 1, 'INNER JOIN #Tanev t ON t.TanevId = x.' + @tanevIdColumn, '') + '
|
|
WHERE x.' + @intezmenyIdColumn + ' IN (' + @pIntezmenyIds + ')
|
|
|
|
SET @rowCount = @@ROWCOUNT
|
|
'
|
|
|
|
IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql, N'@rowCount int output', @rowCount output
|
|
SET @message = ' DEL-C ' + FORMAT(GETDATE(), 'HH:mm:ss.ff') + ' - ' + @tableName + ' - ' + CAST(@rowCount AS nvarchar(20)) + ' sor'
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
END
|
|
FETCH NEXT FROM tbl_cur
|
|
INTO @tableName, @intezmenyIdColumn, @tanevIdColumn
|
|
END
|
|
|
|
CLOSE tbl_cur
|
|
DEALLOCATE tbl_cur
|
|
|
|
|
|
|
|
-- ==================================================
|
|
-- INTEZMENY törlés
|
|
-- ==================================================
|
|
IF @pIsArchivTanevTorles = 0 BEGIN
|
|
SET @sql = N'
|
|
DELETE x
|
|
FROM dbo.T_TANEV x
|
|
WHERE x.C_INTEZMENYID IN (' + @pIntezmenyIds + ')
|
|
|
|
SET @rowCount = @@ROWCOUNT
|
|
'
|
|
|
|
|
|
IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql, N'@rowCount int output', @rowCount output
|
|
SET @message = ' DEL-C ' + FORMAT(GETDATE(), 'HH:mm:ss.ff') + ' - T_TANEV - ' + CAST(@rowCount AS nvarchar(20)) + ' sor'
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
|
|
|
|
SET @sql = N'
|
|
DELETE x
|
|
FROM dbo.T_INTEZMENY x
|
|
WHERE x.ID IN (' + @pIntezmenyIds + ')
|
|
|
|
SET @rowCount = @@ROWCOUNT
|
|
'
|
|
|
|
|
|
IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql, N'@rowCount int output', @rowCount output
|
|
SET @message = ' DEL-C ' + FORMAT(GETDATE(), 'HH:mm:ss.ff') + ' - T_INTEZMENY - ' + CAST(@rowCount AS nvarchar(20)) + ' sor'
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
END
|
|
|
|
-- ==================================================
|
|
-- ENTITYHISTORY index törlés
|
|
-- ==================================================
|
|
SET @message = 'DROPPING ENTITYHISTORY INDEX... - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
|
|
SET @sql = N'
|
|
DROP INDEX IF EXISTS IX_TMP_EntityHistory_IntezmenyId_Delete
|
|
ON dbo.T_ENTITYHISTORY
|
|
'
|
|
|
|
IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql
|
|
SET @message = 'DROPPING ENTITYHISTORY INDEX DONE - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
|
|
END
|
|
GO
|
|
|