﻿/* Kitöröltjük a 2023/2024 tanév  dictionary-ket, mert eddig nem volt rá follow up, nem tudhatjuk, mennyire aktuálisak. */
  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_SORSZAM = 46 AND tv.C_ISKIVALASZTHATO = 'F' AND tv.C_KOVETKEZO = 'F' AND tv.TOROLT ='F' 

--  PRINT CONCAT('IntezmenyID: ', @intezmenyId)


    ;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;
    

    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_OKTATASINEVELESIFELADAT','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
