GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
PRINT N'Dropping [dbo].[FK_4025714_4025713]...';


GO
ALTER TABLE [dbo].[T_TANULOESEMENY] DROP CONSTRAINT [FK_4025714_4025713];


GO
PRINT N'Dropping [dbo].[FK_4025320_4025319]...';


GO
ALTER TABLE [dbo].[T_OSZTALY] DROP CONSTRAINT [FK_4025320_4025319];


GO
PRINT N'Dropping [dbo].[FK_4025323_4025322]...';


GO
ALTER TABLE [dbo].[T_OSZTALY] DROP CONSTRAINT [FK_4025323_4025322];


GO
PRINT N'Dropping [dbo].[FK_4026131_4026130]...';


GO
ALTER TABLE [dbo].[T_TEREM] DROP CONSTRAINT [FK_4026131_4026130];


GO
PRINT N'Dropping [dbo].[FK_4027863_4027861]...';


GO
ALTER TABLE [dbo].[T_OGORARENDIORA] DROP CONSTRAINT [FK_4027863_4027861];


GO
PRINT N'Dropping [dbo].[FK_4027571_4027569]...';


GO
ALTER TABLE [dbo].[T_TOVABBIMUNKAUGYIADATOK] DROP CONSTRAINT [FK_4027571_4027569];


GO
PRINT N'Dropping [dbo].[FK_4025326_4025325]...';


GO
ALTER TABLE [dbo].[T_CSOPORT] DROP CONSTRAINT [FK_4025326_4025325];


GO
PRINT N'Dropping [dbo].[FK_4025281_4025280]...';


GO
ALTER TABLE [dbo].[T_ESZKOZ] DROP CONSTRAINT [FK_4025281_4025280];


GO
PRINT N'Dropping [dbo].[FK_4025850_4025849]...';


GO
ALTER TABLE [dbo].[T_TEVEKENYSEG] DROP CONSTRAINT [FK_4025850_4025849];


GO
PRINT N'Dropping [dbo].[FK_4025633_4025631]...';


GO
ALTER TABLE [dbo].[T_ORAK_TANAROK] DROP CONSTRAINT [FK_4025633_4025631];


GO
PRINT N'Dropping [dbo].[FK_4025670_4025669]...';


GO
ALTER TABLE [dbo].[T_TANULOERTEKELES] DROP CONSTRAINT [FK_4025670_4025669];


GO
PRINT N'Dropping [dbo].[FK_4025437_4025435]...';


GO
ALTER TABLE [dbo].[T_FOGLALKOZASOK_TANAROK] DROP CONSTRAINT [FK_4025437_4025435];


GO
PRINT N'Dropping [dbo].[FK_4026291_4026289]...';


GO
ALTER TABLE [dbo].[T_HELYETTESITESIIDOSZAK] DROP CONSTRAINT [FK_4026291_4026289];


GO
PRINT N'Dropping [dbo].[FK_4027815_4027814]...';


GO
ALTER TABLE [dbo].[T_OGFOGLALKOZAS] DROP CONSTRAINT [FK_4027815_4027814];


GO
PRINT N'Dropping [dbo].[FK_4025629_4025628]...';


GO
ALTER TABLE [dbo].[T_TANITASIORA] DROP CONSTRAINT [FK_4025629_4025628];


GO
PRINT N'Dropping [dbo].[FK_4027463_4027462]...';


GO
ALTER TABLE [dbo].[T_PEDAGOGUSIKTADATSZOLGALTATAS] DROP CONSTRAINT [FK_4027463_4027462];


GO
PRINT N'Dropping [dbo].[FK_4025945_4025944]...';


GO
ALTER TABLE [dbo].[T_TANARITANTARGY] DROP CONSTRAINT [FK_4025945_4025944];


GO
PRINT N'Dropping [dbo].[FK_4025605_4025604]...';


GO
ALTER TABLE [dbo].[T_TANMENET] DROP CONSTRAINT [FK_4025605_4025604];


GO
PRINT N'Dropping [dbo].[FK_4027771_4027769]...';


GO
ALTER TABLE [dbo].[T_OGTANARFOGLALTSAG] DROP CONSTRAINT [FK_4027771_4027769];


GO
PRINT N'Dropping [dbo].[FK_4026327_4026325]...';


GO
ALTER TABLE [dbo].[T_ORARENDIORA] DROP CONSTRAINT [FK_4026327_4026325];


GO
PRINT N'Dropping [dbo].[FK_4025761_4025759]...';


GO
ALTER TABLE [dbo].[T_IGAZOLAS] DROP CONSTRAINT [FK_4025761_4025759];


GO
PRINT N'Dropping [dbo].[FK_4026685_4026684]...';


GO
ALTER TABLE [dbo].[T_NEMKOTOTTMUNKAIDO] DROP CONSTRAINT [FK_4026685_4026684];


GO
PRINT N'Dropping [dbo].[FK_4025792_4025791]...';


GO
ALTER TABLE [dbo].[T_ORALATOGATAS] DROP CONSTRAINT [FK_4025792_4025791];


GO
PRINT N'Dropping [dbo].[FK_4025910_4025909]...';


GO
ALTER TABLE [dbo].[T_JOGYAKORLAT] DROP CONSTRAINT [FK_4025910_4025909];


GO
PRINT N'Dropping [dbo].[FK_4026129_4026127]...';


GO
ALTER TABLE [dbo].[T_MUNKAUGYIADATOK] DROP CONSTRAINT [FK_4026129_4026127];


GO
PRINT N'Dropping [dbo].[FK_T_8935_4025165]...';


GO
ALTER TABLE [dbo].[T_ALKALMAZOTT] DROP CONSTRAINT [FK_T_8935_4025165];


GO
PRINT N'Dropping [dbo].[FK_402513902_402513900]...';


GO
ALTER TABLE [dbo].[T_ALKALMAZOTT] DROP CONSTRAINT [FK_402513902_402513900];


GO
PRINT N'Dropping [dbo].[FK_402513905_402513903]...';


GO
ALTER TABLE [dbo].[T_ALKALMAZOTT] DROP CONSTRAINT [FK_402513905_402513903];


GO
PRINT N'Starting rebuilding table [dbo].[T_ALKALMAZOTT]...';


GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_T_ALKALMAZOTT] (
    [ID]                             INT      NOT NULL,
    [C_ALLANDOHELYETTESITESRE]       CHAR (1) DEFAULT ('F') NULL,
    [C_FOALLAS]                      CHAR (1) DEFAULT ('T') NOT NULL,
    [C_TANARIAUTOMATAMEGAJANLASENGE] CHAR (1) NULL,
    [C_TANARIERTEKELESHATARERTEK]    INT      NULL,
    [C_DIAKOLIMPIA]                  CHAR (1) DEFAULT ('F') NULL,
    [C_SZAKERTOIVIZSGAELNOKI]        CHAR (1) DEFAULT ('F') NULL,
    [C_SZAKVIZSGA]                   CHAR (1) DEFAULT ('F') NULL,
    [C_TOVABBKEPZES]                 CHAR (1) DEFAULT ('F') NULL,
    [C_ORAKEGYHUZAMBAN]              CHAR (1) NULL,
    [C_KOZOSSEGISZOLGALATKEZELO]     CHAR (1) DEFAULT ('F') NULL,
    [C_ELFOGADOTTSZANDEKNYILATKOZAT] CHAR (1) DEFAULT ('F') NULL,
    [C_ALINTEZMENYID]                INT      NOT NULL,
    [C_ALTANEVID]                    INT      NOT NULL,
    [TOROLT]                         CHAR (1) DEFAULT ('F') NOT NULL,
    [SERIAL]                         INT      DEFAULT ((0)) NOT NULL,
    [LASTCHANGED]                    DATETIME DEFAULT (getdate()) NOT NULL,
    [CREATED]                        DATETIME DEFAULT (getdate()) NOT NULL,
    [MODIFIER]                       INT      NULL,
    [CREATOR]                        INT      NULL,
    [ELOZOTANEVIREKORDID]            INT      NULL,
    [ELOZOTANEVIREKORDIDA]           INT      NULL,
    [NNID]                           INT      NULL,
    [NNIDA]                          INT      NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_T_40251391] PRIMARY KEY CLUSTERED ([ID] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[T_ALKALMAZOTT])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_T_ALKALMAZOTT] ([ID], [C_ALLANDOHELYETTESITESRE], [C_FOALLAS], [C_TANARIAUTOMATAMEGAJANLASENGE], [C_TANARIERTEKELESHATARERTEK], [C_DIAKOLIMPIA], [C_SZAKERTOIVIZSGAELNOKI], [C_SZAKVIZSGA], [C_TOVABBKEPZES], [C_ORAKEGYHUZAMBAN], [C_KOZOSSEGISZOLGALATKEZELO], [C_ALINTEZMENYID], [C_ALTANEVID], [TOROLT], [SERIAL], [LASTCHANGED], [CREATED], [MODIFIER], [CREATOR], [ELOZOTANEVIREKORDID], [ELOZOTANEVIREKORDIDA], [NNID], [NNIDA])
        SELECT   [ID],
                 [C_ALLANDOHELYETTESITESRE],
                 [C_FOALLAS],
                 [C_TANARIAUTOMATAMEGAJANLASENGE],
                 [C_TANARIERTEKELESHATARERTEK],
                 [C_DIAKOLIMPIA],
                 [C_SZAKERTOIVIZSGAELNOKI],
                 [C_SZAKVIZSGA],
                 [C_TOVABBKEPZES],
                 [C_ORAKEGYHUZAMBAN],
                 [C_KOZOSSEGISZOLGALATKEZELO],
                 [C_ALINTEZMENYID],
                 [C_ALTANEVID],
                 [TOROLT],
                 [SERIAL],
                 [LASTCHANGED],
                 [CREATED],
                 [MODIFIER],
                 [CREATOR],
                 [ELOZOTANEVIREKORDID],
                 [ELOZOTANEVIREKORDIDA],
                 [NNID],
                 [NNIDA]
        FROM     [dbo].[T_ALKALMAZOTT]
        ORDER BY [ID] ASC;
    END

DROP TABLE [dbo].[T_ALKALMAZOTT];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_T_ALKALMAZOTT]', N'T_ALKALMAZOTT';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_T_40251391]', N'PK_T_4025139', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
PRINT N'Creating [dbo].[T_ALKALMAZOTT].[I_T_402513902_402513900]...';


GO
CREATE NONCLUSTERED INDEX [I_T_402513902_402513900]
    ON [dbo].[T_ALKALMAZOTT]([C_ALINTEZMENYID] ASC);


GO
PRINT N'Creating [dbo].[T_ALKALMAZOTT].[I_T_402513905_402513903]...';


GO
CREATE NONCLUSTERED INDEX [I_T_402513905_402513903]
    ON [dbo].[T_ALKALMAZOTT]([C_ALTANEVID] ASC);


GO
PRINT N'Creating [dbo].[FK_4025714_4025713]...';


GO
ALTER TABLE [dbo].[T_TANULOESEMENY] WITH NOCHECK
    ADD CONSTRAINT [FK_4025714_4025713] FOREIGN KEY ([C_FELJEGYZOID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4025320_4025319]...';


GO
ALTER TABLE [dbo].[T_OSZTALY] WITH NOCHECK
    ADD CONSTRAINT [FK_4025320_4025319] FOREIGN KEY ([C_OSZTALYFONOKID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4025323_4025322]...';


GO
ALTER TABLE [dbo].[T_OSZTALY] WITH NOCHECK
    ADD CONSTRAINT [FK_4025323_4025322] FOREIGN KEY ([C_OFOHELYETTESID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4026131_4026130]...';


GO
ALTER TABLE [dbo].[T_TEREM] WITH NOCHECK
    ADD CONSTRAINT [FK_4026131_4026130] FOREIGN KEY ([C_TEREMFELELOSID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4027863_4027861]...';


GO
ALTER TABLE [dbo].[T_OGORARENDIORA] WITH NOCHECK
    ADD CONSTRAINT [FK_4027863_4027861] FOREIGN KEY ([C_ALKALMAZOTTID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4027571_4027569]...';


GO
ALTER TABLE [dbo].[T_TOVABBIMUNKAUGYIADATOK] WITH NOCHECK
    ADD CONSTRAINT [FK_4027571_4027569] FOREIGN KEY ([C_ALKALMAZOTTID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4025326_4025325]...';


GO
ALTER TABLE [dbo].[T_CSOPORT] WITH NOCHECK
    ADD CONSTRAINT [FK_4025326_4025325] FOREIGN KEY ([C_CSOPORTVEZETOID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4025281_4025280]...';


GO
ALTER TABLE [dbo].[T_ESZKOZ] WITH NOCHECK
    ADD CONSTRAINT [FK_4025281_4025280] FOREIGN KEY ([C_FELELOSID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4025850_4025849]...';


GO
ALTER TABLE [dbo].[T_TEVEKENYSEG] WITH NOCHECK
    ADD CONSTRAINT [FK_4025850_4025849] FOREIGN KEY ([C_ESEMENYTULAJDONOSID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4025633_4025631]...';


GO
ALTER TABLE [dbo].[T_ORAK_TANAROK] WITH NOCHECK
    ADD CONSTRAINT [FK_4025633_4025631] FOREIGN KEY ([C_TANAROKID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4025670_4025669]...';


GO
ALTER TABLE [dbo].[T_TANULOERTEKELES] WITH NOCHECK
    ADD CONSTRAINT [FK_4025670_4025669] FOREIGN KEY ([C_ERTEKELOID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4025437_4025435]...';


GO
ALTER TABLE [dbo].[T_FOGLALKOZASOK_TANAROK] WITH NOCHECK
    ADD CONSTRAINT [FK_4025437_4025435] FOREIGN KEY ([C_TANAROKID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4026291_4026289]...';


GO
ALTER TABLE [dbo].[T_HELYETTESITESIIDOSZAK] WITH NOCHECK
    ADD CONSTRAINT [FK_4026291_4026289] FOREIGN KEY ([C_HELYETTESTANAROKID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4027815_4027814]...';


GO
ALTER TABLE [dbo].[T_OGFOGLALKOZAS] WITH NOCHECK
    ADD CONSTRAINT [FK_4027815_4027814] FOREIGN KEY ([C_ALKALMAZOTTID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4025629_4025628]...';


GO
ALTER TABLE [dbo].[T_TANITASIORA] WITH NOCHECK
    ADD CONSTRAINT [FK_4025629_4025628] FOREIGN KEY ([C_HELYETTESITOTANARID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4027463_4027462]...';


GO
ALTER TABLE [dbo].[T_PEDAGOGUSIKTADATSZOLGALTATAS] WITH NOCHECK
    ADD CONSTRAINT [FK_4027463_4027462] FOREIGN KEY ([C_ALKALMAZOTTID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4025945_4025944]...';


GO
ALTER TABLE [dbo].[T_TANARITANTARGY] WITH NOCHECK
    ADD CONSTRAINT [FK_4025945_4025944] FOREIGN KEY ([C_ALKALMAZOTTID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4025605_4025604]...';


GO
ALTER TABLE [dbo].[T_TANMENET] WITH NOCHECK
    ADD CONSTRAINT [FK_4025605_4025604] FOREIGN KEY ([C_FELTOLTOID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4027771_4027769]...';


GO
ALTER TABLE [dbo].[T_OGTANARFOGLALTSAG] WITH NOCHECK
    ADD CONSTRAINT [FK_4027771_4027769] FOREIGN KEY ([C_ALKALMAZOTTID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4026327_4026325]...';


GO
ALTER TABLE [dbo].[T_ORARENDIORA] WITH NOCHECK
    ADD CONSTRAINT [FK_4026327_4026325] FOREIGN KEY ([C_TANARID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4025761_4025759]...';


GO
ALTER TABLE [dbo].[T_IGAZOLAS] WITH NOCHECK
    ADD CONSTRAINT [FK_4025761_4025759] FOREIGN KEY ([C_ROGZITOID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4026685_4026684]...';


GO
ALTER TABLE [dbo].[T_NEMKOTOTTMUNKAIDO] WITH NOCHECK
    ADD CONSTRAINT [FK_4026685_4026684] FOREIGN KEY ([C_TANARID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4025792_4025791]...';


GO
ALTER TABLE [dbo].[T_ORALATOGATAS] WITH NOCHECK
    ADD CONSTRAINT [FK_4025792_4025791] FOREIGN KEY ([C_ERTEKELOID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4025910_4025909]...';


GO
ALTER TABLE [dbo].[T_JOGYAKORLAT] WITH NOCHECK
    ADD CONSTRAINT [FK_4025910_4025909] FOREIGN KEY ([C_JOGYAKORLATTULAJDONOSID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4026129_4026127]...';


GO
ALTER TABLE [dbo].[T_MUNKAUGYIADATOK] WITH NOCHECK
    ADD CONSTRAINT [FK_4026129_4026127] FOREIGN KEY ([C_ALKALMAZOTTID]) REFERENCES [dbo].[T_ALKALMAZOTT] ([ID]);


GO
PRINT N'Creating [dbo].[FK_T_8935_4025165]...';


GO
ALTER TABLE [dbo].[T_ALKALMAZOTT] WITH NOCHECK
    ADD CONSTRAINT [FK_T_8935_4025165] FOREIGN KEY ([ID]) REFERENCES [dbo].[T_FELHASZNALO] ([ID]);


GO
PRINT N'Creating [dbo].[FK_402513902_402513900]...';


GO
ALTER TABLE [dbo].[T_ALKALMAZOTT] WITH NOCHECK
    ADD CONSTRAINT [FK_402513902_402513900] FOREIGN KEY ([C_ALINTEZMENYID]) REFERENCES [dbo].[T_INTEZMENY] ([ID]);


GO
PRINT N'Creating [dbo].[FK_402513905_402513903]...';


GO
ALTER TABLE [dbo].[T_ALKALMAZOTT] WITH NOCHECK
    ADD CONSTRAINT [FK_402513905_402513903] FOREIGN KEY ([C_ALTANEVID]) REFERENCES [dbo].[T_TANEV] ([ID]);


GO
PRINT N'Creating [dbo].[tr_alkalmazottLog]...';


GO

  CREATE TRIGGER tr_alkalmazottLog ON T_ALKALMAZOTT 
  FOR INSERT, DELETE, UPDATE AS
  BEGIN
    SET NOCOUNT ON
    DECLARE
      @insTable TABLE (objectId int, intezmenyId int, tanevId int, creator int, modifier int, col nvarchar(30), val varchar(max))
    DECLARE  
      @delTable TABLE (objectId int, intezmenyId int, tanevId int, creator int, modifier int, col nvarchar(30), val varchar(max))
    DECLARE 
      @linkTable TABLE (id int, objectId int, intezmenyId int, tanevId int)
    DECLARE  
      @action char(1),
      @xml xml    
    
    SET @action =
      CASE 
        WHEN NOT EXISTS (SELECT 1 FROM DELETED) THEN 'I'
        WHEN NOT EXISTS (SELECT 1 FROM INSERTED) THEN 'D'
        ELSE 'U'
      END  
    
      
    INSERT INTO @insTable (objectId, intezmenyId, tanevId, creator, modifier, col, val)
    SELECT ID, C_INTEZMENYID, C_TANEVID, CREATOR, MODIFIER, col, val
      FROM 
       (
       SELECT  ID, C_ALINTEZMENYID AS C_INTEZMENYID, C_ALTANEVID AS C_TANEVID, CREATOR, MODIFIER, CAST( C_ALLANDOHELYETTESITESRE AS nvarchar(MAX)) AS C_ALLANDOHELYETTESITESRE, CAST( C_FOALLAS AS nvarchar(MAX)) AS C_FOALLAS, CAST( C_TANARIAUTOMATAMEGAJANLASENGE AS nvarchar(MAX)) AS C_TANARIAUTOMATAMEGAJANLASENGE, CAST( C_TANARIERTEKELESHATARERTEK AS nvarchar(MAX)) AS C_TANARIERTEKELESHATARERTEK, CAST( C_DIAKOLIMPIA AS nvarchar(MAX)) AS C_DIAKOLIMPIA, CAST( C_SZAKERTOIVIZSGAELNOKI AS nvarchar(MAX)) AS C_SZAKERTOIVIZSGAELNOKI, CAST( C_SZAKVIZSGA AS nvarchar(MAX)) AS C_SZAKVIZSGA, CAST( C_TOVABBKEPZES AS nvarchar(MAX)) AS C_TOVABBKEPZES, CAST( C_ORAKEGYHUZAMBAN AS nvarchar(MAX)) AS C_ORAKEGYHUZAMBAN, CAST( C_KOZOSSEGISZOLGALATKEZELO AS nvarchar(MAX)) AS C_KOZOSSEGISZOLGALATKEZELO, CAST( C_ELFOGADOTTSZANDEKNYILATKOZAT AS nvarchar(MAX)) AS C_ELFOGADOTTSZANDEKNYILATKOZAT, CAST( TOROLT AS nvarchar(MAX)) AS TOROLT, CAST( ELOZOTANEVIREKORDID AS nvarchar(MAX)) AS ELOZOTANEVIREKORDID, CAST( ELOZOTANEVIREKORDIDA AS nvarchar(MAX)) AS ELOZOTANEVIREKORDIDA, CAST( NNID AS nvarchar(MAX)) AS NNID, CAST( NNIDA AS nvarchar(MAX)) AS NNIDA
       FROM INSERTED) p
       UNPIVOT (val FOR col IN (C_ALLANDOHELYETTESITESRE, C_FOALLAS, C_TANARIAUTOMATAMEGAJANLASENGE, C_TANARIERTEKELESHATARERTEK, C_DIAKOLIMPIA, C_SZAKERTOIVIZSGAELNOKI, C_SZAKVIZSGA, C_TOVABBKEPZES, C_ORAKEGYHUZAMBAN, C_KOZOSSEGISZOLGALATKEZELO, C_ELFOGADOTTSZANDEKNYILATKOZAT, TOROLT, ELOZOTANEVIREKORDID, ELOZOTANEVIREKORDIDA, NNID, NNIDA)) AS unpvt

    INSERT INTO @delTable (objectId, intezmenyId, tanevId, creator, modifier, col, val)
    SELECT ID, C_INTEZMENYID, C_TANEVID, CREATOR, MODIFIER, col, val
      FROM 
       (
       SELECT  ID, C_ALINTEZMENYID AS C_INTEZMENYID, C_ALTANEVID AS C_TANEVID, CREATOR, MODIFIER, CAST( C_ALLANDOHELYETTESITESRE AS nvarchar(MAX)) AS C_ALLANDOHELYETTESITESRE, CAST( C_FOALLAS AS nvarchar(MAX)) AS C_FOALLAS, CAST( C_TANARIAUTOMATAMEGAJANLASENGE AS nvarchar(MAX)) AS C_TANARIAUTOMATAMEGAJANLASENGE, CAST( C_TANARIERTEKELESHATARERTEK AS nvarchar(MAX)) AS C_TANARIERTEKELESHATARERTEK, CAST( C_DIAKOLIMPIA AS nvarchar(MAX)) AS C_DIAKOLIMPIA, CAST( C_SZAKERTOIVIZSGAELNOKI AS nvarchar(MAX)) AS C_SZAKERTOIVIZSGAELNOKI, CAST( C_SZAKVIZSGA AS nvarchar(MAX)) AS C_SZAKVIZSGA, CAST( C_TOVABBKEPZES AS nvarchar(MAX)) AS C_TOVABBKEPZES, CAST( C_ORAKEGYHUZAMBAN AS nvarchar(MAX)) AS C_ORAKEGYHUZAMBAN, CAST( C_KOZOSSEGISZOLGALATKEZELO AS nvarchar(MAX)) AS C_KOZOSSEGISZOLGALATKEZELO, CAST( C_ELFOGADOTTSZANDEKNYILATKOZAT AS nvarchar(MAX)) AS C_ELFOGADOTTSZANDEKNYILATKOZAT, CAST( TOROLT AS nvarchar(MAX)) AS TOROLT, CAST( ELOZOTANEVIREKORDID AS nvarchar(MAX)) AS ELOZOTANEVIREKORDID, CAST( ELOZOTANEVIREKORDIDA AS nvarchar(MAX)) AS ELOZOTANEVIREKORDIDA, CAST( NNID AS nvarchar(MAX)) AS NNID, CAST( NNIDA AS nvarchar(MAX)) AS NNIDA
       FROM DELETED) p
       UNPIVOT (val FOR col IN (C_ALLANDOHELYETTESITESRE, C_FOALLAS, C_TANARIAUTOMATAMEGAJANLASENGE, C_TANARIERTEKELESHATARERTEK, C_DIAKOLIMPIA, C_SZAKERTOIVIZSGAELNOKI, C_SZAKVIZSGA, C_TOVABBKEPZES, C_ORAKEGYHUZAMBAN, C_KOZOSSEGISZOLGALATKEZELO, C_ELFOGADOTTSZANDEKNYILATKOZAT, TOROLT, ELOZOTANEVIREKORDID, ELOZOTANEVIREKORDIDA, NNID, NNIDA)) AS unpvt
    
    IF @action = 'I' BEGIN
      INSERT INTO T_ENTITYHISTORY (C_ALTERATIONDATE, C_ENTITYID, C_ENTITYNAME, C_REASON, C_FELHASZNALOID, C_INTEZMENYID, C_TANEVID)
      OUTPUT INSERTED.ID, INSERTED.C_ENTITYID, INSERTED.C_INTEZMENYID, INSERTED.C_TANEVID INTO @linkTable
      SELECT DISTINCT GETDATE(), objectId, 'T_ALKALMAZOTT', 'New', creator, intezmenyId, tanevId FROM @insTable
      
      INSERT INTO T_ENTITYATTRIBUTEHISTORY (C_CURRENTVALUE, C_ORIGINALVALUE, C_PROPERTYNAME, C_ENTITYHISTORYID)
      SELECT val, NULL, col, lk.id 
      FROM @insTable i
      INNER JOIN @linkTable lk ON i.objectId = lk.objectId AND i.intezmenyId = lk.intezmenyId AND i.tanevId = lk.tanevId
      
    END

    IF @action = 'D' BEGIN
      INSERT INTO T_ENTITYHISTORY (C_ALTERATIONDATE, C_ENTITYID, C_ENTITYNAME, C_REASON, C_FELHASZNALOID, C_INTEZMENYID, C_TANEVID)
      OUTPUT INSERTED.ID, INSERTED.C_ENTITYID, INSERTED.C_INTEZMENYID, INSERTED.C_TANEVID INTO @linkTable
      SELECT DISTINCT GETDATE(), objectId, 'T_ALKALMAZOTT', 'Removed', modifier, intezmenyId, tanevId FROM @delTable
      
      INSERT INTO T_ENTITYATTRIBUTEHISTORY (C_CURRENTVALUE, C_ORIGINALVALUE, C_PROPERTYNAME, C_ENTITYHISTORYID)
      SELECT val, NULL, col, lk.id 
      FROM @delTable i
      INNER JOIN @linkTable lk ON i.objectId = lk.objectId AND i.intezmenyId = lk.intezmenyId AND i.tanevId = lk.tanevId
      
    END

    IF @action = 'U' BEGIN
      INSERT INTO T_ENTITYHISTORY (C_ALTERATIONDATE, C_ENTITYID, C_ENTITYNAME, C_REASON, C_FELHASZNALOID, C_INTEZMENYID, C_TANEVID)
      OUTPUT INSERTED.ID, INSERTED.C_ENTITYID, INSERTED.C_INTEZMENYID, INSERTED.C_TANEVID INTO @linkTable
      SELECT DISTINCT GETDATE(), i.objectId, 'T_ALKALMAZOTT', 
        CASE 
          WHEN i.val = 'T' AND d.val = 'F' THEN 'Logical Removed'
          WHEN i.val = 'F' AND d.val = 'T' THEN 'Restored'
          ELSE 'Modified' 
        END, i.modifier, i.intezmenyId, i.tanevId 
      FROM @insTable i
      INNER JOIN @delTable d ON i.intezmenyId = d.intezmenyId AND i.objectId = d.objectId AND i.tanevId = d.tanevId AND i.col = d.col
      WHERE i.col = 'TOROLT' AND d.COL = 'TOROLT'
      
      INSERT INTO T_ENTITYATTRIBUTEHISTORY (C_CURRENTVALUE, C_ORIGINALVALUE, C_PROPERTYNAME, C_ENTITYHISTORYID)
      SELECT i.val, d.val, ISNULL(i.col, d.col), lk.id 
      FROM @delTable d
        FULL JOIN @insTable i ON i.objectId = d.objectId AND i.intezmenyId = d.intezmenyId AND i.tanevId = d.tanevId AND i.col = d.col
        INNER JOIN @linkTable lk ON ISNULL(i.objectId, d.objectId) = lk.objectId AND ISNULL(i.intezmenyId, d.intezmenyId) = lk.intezmenyId AND ISNULL(i.tanevId, d.tanevId) = lk.tanevId
      WHERE (i.val <> d.val OR ((i.val IS NULL OR d.val IS NULL) AND (i.val IS NOT NULL OR d.val IS NOT NULL)))
    END  
  END
GO
PRINT N'Refreshing [dbo].[T_ALKALMAZOTT_OSSZES]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[T_ALKALMAZOTT_OSSZES]';


GO
PRINT N'Refreshing [dbo].[sp_Global_GenerateTanevAdatokFromExistingTanev]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[sp_Global_GenerateTanevAdatokFromExistingTanev]';


GO
PRINT N'Checking existing data against newly created constraints';


GO
ALTER TABLE [dbo].[T_TANULOESEMENY] WITH CHECK CHECK CONSTRAINT [FK_4025714_4025713];

ALTER TABLE [dbo].[T_OSZTALY] WITH CHECK CHECK CONSTRAINT [FK_4025320_4025319];

ALTER TABLE [dbo].[T_OSZTALY] WITH CHECK CHECK CONSTRAINT [FK_4025323_4025322];

ALTER TABLE [dbo].[T_TEREM] WITH CHECK CHECK CONSTRAINT [FK_4026131_4026130];

ALTER TABLE [dbo].[T_OGORARENDIORA] WITH CHECK CHECK CONSTRAINT [FK_4027863_4027861];

ALTER TABLE [dbo].[T_TOVABBIMUNKAUGYIADATOK] WITH CHECK CHECK CONSTRAINT [FK_4027571_4027569];

ALTER TABLE [dbo].[T_CSOPORT] WITH CHECK CHECK CONSTRAINT [FK_4025326_4025325];

ALTER TABLE [dbo].[T_ESZKOZ] WITH CHECK CHECK CONSTRAINT [FK_4025281_4025280];

ALTER TABLE [dbo].[T_TEVEKENYSEG] WITH CHECK CHECK CONSTRAINT [FK_4025850_4025849];

ALTER TABLE [dbo].[T_ORAK_TANAROK] WITH CHECK CHECK CONSTRAINT [FK_4025633_4025631];

ALTER TABLE [dbo].[T_TANULOERTEKELES] WITH CHECK CHECK CONSTRAINT [FK_4025670_4025669];

ALTER TABLE [dbo].[T_FOGLALKOZASOK_TANAROK] WITH CHECK CHECK CONSTRAINT [FK_4025437_4025435];

ALTER TABLE [dbo].[T_HELYETTESITESIIDOSZAK] WITH CHECK CHECK CONSTRAINT [FK_4026291_4026289];

ALTER TABLE [dbo].[T_OGFOGLALKOZAS] WITH CHECK CHECK CONSTRAINT [FK_4027815_4027814];

ALTER TABLE [dbo].[T_TANITASIORA] WITH CHECK CHECK CONSTRAINT [FK_4025629_4025628];

ALTER TABLE [dbo].[T_PEDAGOGUSIKTADATSZOLGALTATAS] WITH CHECK CHECK CONSTRAINT [FK_4027463_4027462];

ALTER TABLE [dbo].[T_TANARITANTARGY] WITH CHECK CHECK CONSTRAINT [FK_4025945_4025944];

ALTER TABLE [dbo].[T_TANMENET] WITH CHECK CHECK CONSTRAINT [FK_4025605_4025604];

ALTER TABLE [dbo].[T_OGTANARFOGLALTSAG] WITH CHECK CHECK CONSTRAINT [FK_4027771_4027769];

ALTER TABLE [dbo].[T_ORARENDIORA] WITH CHECK CHECK CONSTRAINT [FK_4026327_4026325];

ALTER TABLE [dbo].[T_IGAZOLAS] WITH CHECK CHECK CONSTRAINT [FK_4025761_4025759];

ALTER TABLE [dbo].[T_NEMKOTOTTMUNKAIDO] WITH CHECK CHECK CONSTRAINT [FK_4026685_4026684];

ALTER TABLE [dbo].[T_ORALATOGATAS] WITH CHECK CHECK CONSTRAINT [FK_4025792_4025791];

ALTER TABLE [dbo].[T_JOGYAKORLAT] WITH CHECK CHECK CONSTRAINT [FK_4025910_4025909];

ALTER TABLE [dbo].[T_MUNKAUGYIADATOK] WITH CHECK CHECK CONSTRAINT [FK_4026129_4026127];

ALTER TABLE [dbo].[T_ALKALMAZOTT] WITH CHECK CHECK CONSTRAINT [FK_T_8935_4025165];

ALTER TABLE [dbo].[T_ALKALMAZOTT] WITH CHECK CHECK CONSTRAINT [FK_402513902_402513900];

ALTER TABLE [dbo].[T_ALKALMAZOTT] WITH CHECK CHECK CONSTRAINT [FK_402513905_402513903];


GO
PRINT N'Update complete.';


GO



PRINT '--- intézményi view-k (dbo táblákkal megegyezõ nevû és a tábla_OSSZES nevûek) frissítése ---'

DECLARE  @viewname nvarchar(100), @pureview nvarchar(100)

DECLARE semaviewk CURSOR LOCAL FOR 
  SELECT '['+TABLE_SCHEMA + '].[' + TABLE_NAME + ']', TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE = 'VIEW' and TABLE_SCHEMA LIKE 'KR[_]%[_]Schema'
  AND TABLE_NAME in ('T_ALKALMAZOTT', 'T_ALKALMAZOTT_OSSZES') -- ha csak néhány VIEW frissült, akkor a gyorsabb futásért ide fel lehet sorolni
  ORDER BY TABLE_SCHEMA, TABLE_NAME

OPEN semaviewk
FETCH NEXT FROM semaviewk INTO @viewname, @pureview

WHILE @@FETCH_STATUS = 0 BEGIN
	PRINT @viewname
	EXEC sp_refreshview @viewname

    FETCH NEXT FROM semaviewk INTO @viewname, @pureview
END             

CLOSE semaviewk
DEALLOCATE semaviewk
GO


-- Triggerek inicializálás az összes táblára
DECLARE tableCursor CURSOR LOCAL FOR 
  SELECT DISTINCT TABLE_NAME
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE COLUMN_NAME IN ('CREATOR', 'MODIFIER')
	AND TABLE_NAME NOT IN ('T_ENTITYHISTORY', 'T_ENTITYATTRIBUTEHISTORY', 'T_LOG', 'T_GLOBALLOCK', 'T_USERPROFILE', 'T_OLDALLATOGATOTTSAG')
	AND TABLE_NAME IN (
		SELECT t.name 
		FROM sys.tables t
		INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
		WHERE s.name ='dbo' 
	)

DECLARE @table nvarchar(35)

OPEN tableCursor  
FETCH NEXT FROM tableCursor INTO @table  

WHILE @@FETCH_STATUS = 0 BEGIN  
  EXEC sp_Global_CreateTriggers @table
  FETCH NEXT FROM tableCursor INTO @table  
END  

CLOSE tableCursor  
DEALLOCATE tableCursor 


EXEC dev.sp_Global_GenerateAsyncAuditTriggerAll 1
GO

EXEC dev.sp_Global_GenerateAsyncAuditProcedureAll
GO

EXEC dev.sp_Global_GenerateAsyncAuditAuditPocessing
GO

ALTER QUEUE auditlog.AuditQueue WITH STATUS=OFF, ACTIVATION 
(STATUS = OFF)
GO

ALTER QUEUE auditlog.AuditQueue WITH STATUS=ON, ACTIVATION 
(STATUS = ON, MAX_QUEUE_READERS = 2)
GO