96 lines
2.7 KiB
Transact-SQL
96 lines
2.7 KiB
Transact-SQL
DECLARE @tanevNev nvarchar(20) = '2021/2022'
|
|
|
|
SET NOCOUNT on
|
|
|
|
DROP TABLE IF EXISTS #tanev
|
|
|
|
|
|
CREATE TABLE #tanev (id int PRIMARY KEY)
|
|
|
|
INSERT INTO #tanev (id)
|
|
SELECT tv.ID
|
|
FROM T_INTEZMENY i
|
|
INNER JOIN T_TANEV tv ON tv.C_INTEZMENYID = i.ID
|
|
WHERE i.TOROLT = 'F'
|
|
AND tv.C_NEV = @tanevNev AND tv.C_ISKIVALASZTHATO = 'F' AND tv.C_KOVETKEZO = 'F' AND tv.TOROLT ='F'
|
|
|
|
-- PRINT CONCAT('IntezmenyID: ', @intezmenyId)
|
|
|
|
/* Kitöröltjük a 2021/2022 tanévből a dictionary-ket, mert eddig nem volt rá follow up, nem tudhatjuk, mennyire aktuálisak. */
|
|
;DISABLE TRIGGER ALL ON dbo.T_DICTIONARYITEMBASENYELV;
|
|
DELETE d
|
|
FROM dbo.T_DICTIONARYITEMBASENYELV d
|
|
INNER JOIN #tanev tv ON tv.ID = d.C_TANEVID;
|
|
;ENABLE TRIGGER ALL ON dbo.T_DICTIONARYITEMBASENYELV;
|
|
|
|
|
|
DISABLE TRIGGER ALL ON dbo.T_OKTATASINEVELESIFELADAT;
|
|
DELETE d
|
|
FROM dbo.T_OKTATASINEVELESIFELADAT d
|
|
INNER JOIN #tanev tv ON tv.ID = d.C_ALTANEVID;
|
|
ENABLE TRIGGER ALL ON dbo.T_OKTATASINEVELESIFELADAT;
|
|
|
|
|
|
DISABLE TRIGGER ALL ON dbo.T_OKTNEVELESIKATEGORIA;
|
|
DELETE d
|
|
FROM dbo.T_OKTNEVELESIKATEGORIA d
|
|
INNER JOIN #tanev tv ON tv.ID = d.C_ALTANEVID;
|
|
ENABLE TRIGGER ALL ON dbo.T_OKTNEVELESIKATEGORIA;
|
|
|
|
|
|
DISABLE TRIGGER ALL ON dbo.T_TANTERV;
|
|
DELETE d
|
|
FROM dbo.T_TANTERV d
|
|
INNER JOIN #tanev tv ON tv.ID = d.C_TANEVID;
|
|
ENABLE TRIGGER ALL ON dbo.T_TANTERV;
|
|
|
|
|
|
DECLARE
|
|
@tableName nvarchar(200)
|
|
,@sql nvarchar(max)
|
|
|
|
DECLARE tableCursor CURSOR FAST_FORWARD LOCAL FOR
|
|
SELECT DISTINCT OBJECT_NAME(parent_object_id)
|
|
FROM sys.foreign_key_columns
|
|
WHERE referenced_object_id = OBJECT_ID('T_DICTIONARYITEMBASE')
|
|
AND OBJECT_NAME(parent_object_id) NOT IN ('T_OKTNEVELESIKATEGORIA','T_DICTIONARYITEMBASENYELV')
|
|
|
|
OPEN tableCursor
|
|
|
|
FETCH NEXT FROM tableCursor
|
|
INTO @tableName
|
|
|
|
WHILE @@FETCH_STATUS = 0 BEGIN
|
|
SET @sql = '
|
|
DISABLE TRIGGER ALL ON dbo.' + @tableName + ';
|
|
DELETE x
|
|
FROM dbo.' + @tableName + ' x
|
|
INNER JOIN #tanev tv ON tv.ID = x.C_ALTANEVID;
|
|
|
|
ENABLE TRIGGER ALL ON dbo.' + @tableName + ';
|
|
'
|
|
PRINT @tableName
|
|
EXEC sys.sp_executesql @sql
|
|
|
|
FETCH NEXT FROM tableCursor
|
|
INTO @tableName
|
|
END
|
|
|
|
CLOSE tableCursor
|
|
DEALLOCATE tableCursor
|
|
|
|
;
|
|
DISABLE TRIGGER ALL ON dbo.T_DICTIONARYITEMBASE;
|
|
DELETE d
|
|
FROM dbo.T_DICTIONARYITEMBASE d
|
|
INNER JOIN #tanev tv ON tv.ID = d.C_TANEVID;
|
|
ENABLE TRIGGER ALL ON dbo.T_DICTIONARYITEMBASE;
|
|
|
|
|
|
DISABLE TRIGGER ALL ON dbo.T_DICTIONARYTYPE;
|
|
DELETE d
|
|
FROM dbo.T_DICTIONARYTYPE d
|
|
INNER JOIN #tanev tv ON tv.ID = d.C_TANEVID;
|
|
ENABLE TRIGGER ALL ON dbo.T_DICTIONARYTYPE;
|
|
|
|
GO
|