kreta/Kreta.DataAccess.Migrations/DBScripts/Database/create.triggers.elearning.sql

DROP TRIGGER IF EXISTS [dbo].[tru_TanuloTanugyiAdatok_ElearningFelhasznalo]
GO

CREATE TRIGGER [dbo].[tru_TanuloTanugyiAdatok_ElearningFelhasznalo]
  ON [dbo].[T_TANULOTANUGYIADATOK]
  WITH EXECUTE AS 'Kreta_tech_user'
  AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  IF UPDATE (C_TANULOEVFOLYAMTIPUSID) BEGIN
    --elearningfelhasznalo beszúrása
    INSERT INTO T_ELEARNINGFELHASZNALO (
        C_ELEARNINGELERHETOSEGID
      ,C_FELHASZNALOID
      ,C_INTEZMENYID
      ,C_TANEVID
      ,TOROLT
      ,SERIAL
    ) SELECT
        ee.ID AS C_ELEARNINGELERHETOSEGID 
      ,tcs.C_TANULOID AS C_FELHASZNALOID 
      ,ee.C_INTEZMENYID AS C_INTEZMENYID
      ,ee.C_TANEVID AS C_TANEVID 
      ,'F' AS TOROLT 
      ,0 AS SERIAL 
    FROM Inserted tta
      INNER JOIN T_ELEARNINGELERHETOSEG ee ON tta.C_TANEVID = ee.C_TANEVID AND ee.TOROLT = 'F'
      INNER JOIN dev.ElearningKurzusEvfolyamMapping mp ON mp.Azonosito = ee.C_ELEARNINGAZONOSITO AND tta.C_TANULOEVFOLYAMTIPUSID = mp.evfolyamId
      INNER JOIN T_TANULOCSOPORT tcs ON tcs.ID = tta.C_TANULOCSOPORTID AND tcs.TOROLT = 'F'
      INNER JOIN T_TANEV tv ON tv.ID = ee.C_TANEVID 
        AND tv.TOROLT='F' 
        AND tv.C_AKTIV = 'T'
      INNER JOIN T_OSZTALYCSOPORT ocs ON ocs.ID = tcs.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F'
    WHERE ee.TOROLT='F'
      AND ocs.C_FELADATKATEGORIAID = 7553
      AND NOT EXISTS (
        SELECT 1 
        FROM T_ELEARNINGFELHASZNALO efh
        WHERE efh.C_TANEVID = ee.C_TANEVID AND efh.C_ELEARNINGELERHETOSEGID = ee.ID AND efh.C_FELHASZNALOID = tcs.C_TANULOID AND efh.C_GONDVISELOID IS NULL AND efh.TOROLT='F' 
      )
	END	
END
GO
--------------------------------------------------------------------------------------------
DROP TRIGGER IF EXISTS [dbo].[tru_TanuloCsoport_ElearningFelhasznalo]
GO

CREATE TRIGGER [dbo].[tru_TanuloCsoport_ElearningFelhasznalo]
  ON [dbo].[T_TANULOCSOPORT]
  WITH EXECUTE AS 'Kreta_tech_user'
  AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON;
  IF UPDATE (C_OSZTALYCSOPORTID)  BEGIN

		  --elearningfelhasznalo beszúrása
	  INSERT INTO T_ELEARNINGFELHASZNALO (
		    C_ELEARNINGELERHETOSEGID
		  ,C_FELHASZNALOID
		  ,C_INTEZMENYID
		  ,C_TANEVID
		  ,TOROLT
		  ,SERIAL
	  ) SELECT
		    ee.ID AS C_ELEARNINGELERHETOSEGID 
		  ,tcs.C_TANULOID AS C_FELHASZNALOID 
		  ,tcs.C_INTEZMENYID AS C_INTEZMENYID
		  ,tcs.C_TANEVID AS C_TANEVID 
		  ,'F' AS TOROLT 
		  ,0 AS SERIAL 
	  FROM  inserted tcs
      INNER JOIN T_ELEARNINGELERHETOSEG ee ON ee.C_TANEVID = tcs.C_TANEVID
      INNER JOIN T_OSZTALYCSOPORT ocs ON  tcs.C_OSZTALYCSOPORTID = ocs.ID AND ocs.C_TANEVID = ee.C_TANEVID AND ocs.TOROLT='F'
		  INNER JOIN dev.ElearningKurzusEvfolyamMapping mp ON mp.Azonosito = ee.C_ELEARNINGAZONOSITO AND ocs.C_EVFOLYAMTIPUSA = mp.evfolyamId
		  INNER JOIN T_TANEV tv ON tv.ID = ee.C_TANEVID AND tv.TOROLT = 'F' AND tv.C_AKTIV = 'T'
    WHERE ee.TOROLT='F'
      AND ocs.C_FELADATKATEGORIAID = 7553
		  AND NOT EXISTS (
		    SELECT 1 
		    FROM T_ELEARNINGFELHASZNALO efh
		    WHERE efh.C_TANEVID = tcs.C_TANEVID AND efh.TOROLT='F' AND efh.C_ELEARNINGELERHETOSEGID = ee.ID AND efh.C_FELHASZNALOID = tcs.C_TANULOID
		  )
  END
END
GO
--------------------------------------------------------------------------------------------
DROP TRIGGER IF EXISTS [dbo].[tru_OsztalyCsoport_ElearningFelhasznalo]
GO

CREATE TRIGGER [dbo].[tru_OsztalyCsoport_ElearningFelhasznalo] 
  ON [dbo].[T_OSZTALYCSOPORT]
  WITH EXECUTE AS 'Kreta_tech_user'
  AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  IF UPDATE (C_EVFOLYAMTIPUSA) BEGIN

		--elearningfelhasznalo beszúrása, tanuló
		INSERT INTO T_ELEARNINGFELHASZNALO (
		   C_ELEARNINGELERHETOSEGID
		  ,C_FELHASZNALOID
		  ,C_INTEZMENYID
		  ,C_TANEVID
		  ,TOROLT
		  ,SERIAL
		) SELECT DISTINCT
		   ee.ID AS C_ELEARNINGELERHETOSEGID 
		  ,tcs.C_TANULOID AS C_FELHASZNALOID 
		  ,ocs.C_INTEZMENYID AS C_INTEZMENYID
		  ,ocs.C_TANEVID AS C_TANEVID 
		  ,'F' AS TOROLT 
		  ,0 AS SERIAL 
		FROM Inserted ocs
      INNER JOIN T_OSZTALY o ON o.ID = ocs.ID AND o.TOROLT = 'F'
      INNER JOIN T_ELEARNINGELERHETOSEG ee ON ocs.C_TANEVID = ee.C_TANEVID
		  INNER JOIN dev.ElearningKurzusEvfolyamMapping mp ON mp.Azonosito = ee.C_ELEARNINGAZONOSITO  AND ocs.C_EVFOLYAMTIPUSA = mp.evfolyamId
		  INNER JOIN T_TANULOCSOPORT tcs ON tcs.TOROLT = 'F' AND tcs.C_TANEVID = ee.C_TANEVID
		    AND tcs.C_OSZTALYCSOPORTID = ocs.ID
      INNER JOIN T_TANEV tv ON tv.ID = ee.C_TANEVID AND tv.TOROLT = 'F' AND tv.C_AKTIV = 'T'
		WHERE  ocs.TOROLT = 'F'
      AND ocs.C_FELADATKATEGORIAID = 7553
      AND NOT EXISTS (
		    SELECT 1 
		    FROM T_ELEARNINGFELHASZNALO efh
		    WHERE efh.C_TANEVID = ocs.C_TANEVID AND TOROLT='F' AND efh.C_ELEARNINGELERHETOSEGID = ee.ID AND efh.C_FELHASZNALOID = tcs.C_TANULOID
		)

    --elearningfelhasznalo beszúrása, tanuló, összevont osztály
		INSERT INTO T_ELEARNINGFELHASZNALO (
		   C_ELEARNINGELERHETOSEGID
		  ,C_FELHASZNALOID
		  ,C_INTEZMENYID
		  ,C_TANEVID
		  ,TOROLT
		  ,SERIAL
		)
		SELECT DISTINCT
		   ee.ID AS C_ELEARNINGELERHETOSEGID 
		  ,tcs.C_TANULOID AS C_FELHASZNALOID 
		  ,ocs.C_INTEZMENYID AS C_INTEZMENYID
		  ,ocs.C_TANEVID AS C_TANEVID 
		  ,'F' AS TOROLT 
		  ,0 AS SERIAL 
		FROM Inserted ocs
      INNER JOIN T_OSZTALY o ON o.ID = ocs.ID AND o.TOROLT = 'F'
      INNER JOIN T_ELEARNINGELERHETOSEG ee ON ocs.C_TANEVID = ee.C_TANEVID AND ee.TOROLT = 'F'
		  INNER JOIN T_TANULOCSOPORT tcs ON tcs.TOROLT = 'F' AND tcs.C_TANEVID = ee.C_TANEVID
      INNER JOIN T_TANULOTANUGYIADATOK tta ON tta.C_TANULOCSOPORTID = tcs.ID AND tta.TOROLT = 'F'
		    AND tcs.C_OSZTALYCSOPORTID = ocs.ID		  
      INNER JOIN dev.ElearningKurzusEvfolyamMapping mp ON mp.Azonosito = ee.C_ELEARNINGAZONOSITO 
        AND tta.C_TANULOEVFOLYAMTIPUSID = mp.evfolyamId
      INNER JOIN T_TANEV tv ON tv.ID = ee.C_TANEVID AND tv.TOROLT = 'F' AND tv.C_AKTIV = 'T'
		WHERE ocs.C_EVFOLYAMTIPUSA = 7366 
      AND ocs.C_FELADATKATEGORIAID = 7553
      AND ocs.TOROLT = 'F'
      AND NOT EXISTS (
		    SELECT 1 
		    FROM T_ELEARNINGFELHASZNALO efh
		    WHERE efh.C_TANEVID = ocs.C_TANEVID AND TOROLT='F' AND efh.C_ELEARNINGELERHETOSEGID = ee.ID AND efh.C_FELHASZNALOID = tcs.C_TANULOID
		)
	END
    
END
GO