-- Datei: hwtrg.sql
-- Stand: 06/2022, 12/2022
-- Autor: Steffen Anders
-- Trigger fuer Rekursives Loeschen

IF 0 < (SELECT COUNT(*) FROM sysobjects where [xtype] = 'TR' AND [name] = 'Tr_Del_Stammpositionen')
  DROP TRIGGER Tr_Del_Stammpositionen
GO

CREATE TRIGGER Tr_Del_Stammpositionen ON ut_Stammpositionen FOR DELETE
AS
  IF @@NESTLEVEL < 10
  BEGIN

   DELETE FROM Dimensionstexte
   WHERE RecId IN
   (  SELECT X.RecId FROM Dimensionstexte X 
       INNER JOIN DELETED D ON X.LKZ = D.Lkz AND X.ARTNR = D.Artnr
   )

   DELETE FROM Langtextzeilen
   WHERE RecId IN
   (  SELECT X.RecId FROM Langtextzeilen X 
       INNER JOIN DELETED D ON X.Art = '13' AND X.Lkz = D.Lkz AND X.Nr = D.Artnr
   )

   DELETE FROM Langtexte
   WHERE RecId IN
   (  SELECT X.RecId FROM Langtexte X 
       INNER JOIN DELETED D ON X.Art = '13' AND X.Lkz = D.Lkz AND X.Nr = D.Artnr
   )

   DELETE FROM StammPakete 
   WHERE RecId IN
   (  SELECT X.RecId FROM StammPakete X 
       INNER JOIN DELETED D ON X.ArtLkz = D.Lkz AND X.ArtNr = D.Artnr AND X.LfdNr <> '000'
   )

   DELETE FROM Kleinteile 
   WHERE RecId IN
   (  SELECT X.RecId FROM Kleinteile X 
       INNER JOIN DELETED D ON X.RefLkz = D.Lkz AND X.RefArtnr = D.Artnr
   )

   DELETE FROM Kleinteile 
   WHERE RecId IN
   (  SELECT X.RecId FROM Kleinteile X 
       INNER JOIN DELETED D ON X.Lkz = D.Lkz AND X.Artnr = D.Artnr
   )

   DELETE FROM Lagerbewegungen
   WHERE RecId IN
   (  SELECT X.RecId FROM Lagerbewegungen X 
       INNER JOIN DELETED D ON X.ARTNR = D.Lkz+D.Artnr
   )

   DELETE FROM NeZuschlaege
   WHERE RecId IN
   (  SELECT X.RecId FROM NeZuschlaege X 
       INNER JOIN DELETED D ON X.Lkz = D.Lkz AND X.Artnr = D.Artnr
   )

  END
GO

IF 0 < (SELECT COUNT(*) FROM sysobjects where [xtype] = 'TR' AND [name] = 'Tr_Del_StammPakete')
  DROP TRIGGER Tr_Del_StammPakete
GO


CREATE TRIGGER Tr_Del_StammPakete ON ut_StammPakete FOR DELETE
AS
  IF @@NESTLEVEL < 10
  BEGIN
   DELETE FROM StammPakete 
   WHERE RecId IN
   (  SELECT X.RecId FROM StammPakete X 
       INNER JOIN DELETED D ON D.PktNr = X.PktNr AND D.LfdNr = '000' AND X.LfdNr <> '000'
   )
 END
GO

IF 0 < (SELECT COUNT(*) FROM sysobjects where [xtype] = 'TR' AND [name] = 'Tr_Del_Langtexte')
  DROP TRIGGER Tr_Del_Langtexte
GO

CREATE TRIGGER Tr_Del_Langtexte ON ut_Langtexte FOR DELETE
AS
  IF @@NESTLEVEL < 10
  BEGIN
   DELETE FROM Langtextzeilen WHERE RecId IN
   (  SELECT X.RecId FROM Langtextzeilen X 
       INNER JOIN DELETED D ON 
            D.Art = X.Art  AND D.Lkz = X.Lkz AND D.Nr = X.Nr
   )
  END
GO

IF 0 < (SELECT COUNT(*) FROM sysobjects where [xtype] = 'TR' AND [name] = 'Tr_Del_Adressen')
  DROP TRIGGER Tr_Del_Adressen
GO

CREATE TRIGGER Tr_Del_Adressen ON ut_Adressen FOR DELETE
AS
  IF @@NESTLEVEL < 10
  BEGIN
   DELETE FROM ut_Langtextzeilen    WHERE Art = '22' AND Lkz = 'KI' AND Nr IN (SELECT DESTDEBNR FROM DELETED)
   DELETE FROM ut_Langtexte         WHERE Art = '22' AND Lkz = 'KI' AND Nr IN (SELECT DESTDEBNR FROM DELETED)
   DELETE FROM ut_AdressBemerkungen WHERE AdrNr IN (SELECT DESTDEBNR FROM DELETED)
   DELETE FROM ut_Statusfelder      WHERE KdNr IN (SELECT DESTDEBNR FROM DELETED)
  END
GO


IF 0 < (SELECT COUNT(*) FROM sysobjects where [xtype] = 'TR' AND [name] = 'Tr_Del_Belege')
  DROP TRIGGER Tr_Del_Belege
GO


CREATE TRIGGER Tr_Del_Belege ON ut_Belege FOR DELETE
--ALTER TRIGGER Tr_Del_Belege ON ut_Belege FOR DELETE
AS
  IF @@NESTLEVEL < 10
  BEGIN

   DELETE FROM BelNe WHERE RecId IN
   (  SELECT X.RecId FROM BelNe X 
       INNER JOIN DELETED D ON 
            D.PRJNR = X.PrjNr  AND D.BELEGART = X.BelArt AND D.BELEGNR = X.BelNr
   )

   DELETE FROM BelMassPosRef WHERE RecId IN
   (  SELECT X.RecId FROM BelMassPosRef X 
       INNER JOIN DELETED D ON 
            D.PRJNR = X.PrjNr  AND D.BELEGART = X.BelArt AND D.BELEGNR = X.BelNr
   )

   DELETE FROM BelText WHERE RecId IN
   (  SELECT X.RecId FROM BelText X 
       INNER JOIN DELETED D ON 
            D.PRJNR = X.PrjNr  AND D.BELEGART = X.BelArt AND D.BELEGNR = X.BelNr
   )

   DELETE FROM BelMassRef WHERE RecId IN
   (  SELECT X.RecId FROM BelMassRef X 
       INNER JOIN DELETED D ON 
            D.PRJNR = X.PrjNr  AND D.BELEGART = X.BelArt AND D.BELEGNR = X.BelNr
   )

   DELETE FROM BelPos WHERE RecId IN
   (  SELECT X.RecId FROM BelPos X 
       INNER JOIN DELETED D ON 
            D.PRJNR = X.PrjNr  AND D.BELEGART = X.BelArt AND D.BELEGNR = X.BelNr
   )

   DELETE FROM BelKopfExt WHERE RecId IN
   (  SELECT X.RecId FROM BelKopfExt X 
       INNER JOIN DELETED D ON 
            D.PRJNR = X.PrjNr  AND D.BELEGART = X.BelArt AND D.BELEGNR = X.BelNr
   )

   DELETE FROM BelAdresse WHERE RecId IN
   (  SELECT X.RecId FROM BelAdresse X 
       INNER JOIN DELETED D ON 
            D.PRJNR = X.PrjNr  AND D.BELEGART = X.BelArt AND D.BELEGNR = X.BelNr
   )

   DELETE FROM BelTransaction WHERE RecId IN
   (  SELECT X.RecId FROM BelTransaction X 
       INNER JOIN DELETED D ON 
            D.PRJNR = X.PrjNr  AND D.BELEGART = X.BelArt AND D.BELEGNR = X.BelNr
   )

   DELETE FROM BelAufmasse WHERE RecId IN
   (  SELECT X.RecId FROM BelAufmasse X 
       INNER JOIN DELETED D ON 
            D.PRJNR = X.PrjNr  AND D.BELEGART = X.BelArt AND D.BELEGNR = X.BelNr
   )

   DELETE FROM BelBauabschnitte WHERE RecId IN
   (  SELECT X.RecId FROM BelBauabschnitte X 
       INNER JOIN DELETED D ON 
            D.PRJNR = X.PrjNr  AND D.BELEGART = X.BelArt AND D.BELEGNR = X.BelNr
   )

   DELETE FROM BelRaum WHERE RecId IN
   (  SELECT X.RecId FROM BelRaum X 
       INNER JOIN DELETED D ON 
            D.PRJNR = X.PrjNr  AND D.BELEGART = X.BelArt AND D.BELEGNR = X.BelNr
   )

  END
GO

IF 0 < (SELECT COUNT(*) FROM sysobjects where [xtype] = 'TR' AND [name] = 'Tr_Del_Bestellungen')
  DROP TRIGGER Tr_Del_Bestellungen
GO

CREATE TRIGGER Tr_Del_Bestellungen ON ut_Bestellungen FOR DELETE
AS
  IF @@NESTLEVEL < 10
  BEGIN

   DELETE FROM BestellAbrufMengen
   WHERE NRHW IN ( SELECT NRHW FROM DELETED )

   DELETE FROM BestellAbruf
   WHERE NRHW IN ( SELECT NRHW FROM DELETED )

   DELETE FROM Bestelltexte
   WHERE NRHW IN ( SELECT NRHW FROM DELETED )

   DELETE FROM Bestellpositionen
   WHERE NRHW IN ( SELECT NRHW FROM DELETED )
   
  END
GO


IF 0 < (SELECT COUNT(*) FROM sysobjects where [xtype] = 'TR' AND [name] = 'Tr_Del_Bestellpositionen')
  DROP TRIGGER Tr_Del_Bestellpositionen
GO


CREATE TRIGGER Tr_Del_Bestellpositionen ON ut_Bestellpositionen FOR DELETE
AS
  IF @@NESTLEVEL < 10
  BEGIN

   DECLARE @x_NrHw    char(15)
   DECLARE @x_PosNrHw char(10)
   DECLARE C CURSOR FOR SELECT NRHW,POSNRHW FROM DELETED

   OPEN C
   FETCH NEXT FROM C INTO @x_NrHw,@x_PosNrHw
   WHILE @@FETCH_STATUS = 0
   BEGIN
     DELETE FROM BestellAbrufMengen
     WHERE NRHW = @x_NrHw AND POSNRHW = @x_PosNrHw
     DELETE FROM Bestelltexte
     WHERE NRHW = @x_NrHw AND POSNRHW = @x_PosNrHw
     FETCH NEXT FROM C INTO @x_NrHw,@x_PosNrHw
   END
   CLOSE C
   DEALLOCATE C

  END
GO

IF 0 < (SELECT COUNT(*) FROM sysobjects where [xtype] = 'TR' AND [name] = 'Tr_Del_BelPos')
  DROP TRIGGER Tr_Del_BelPos
GO


CREATE TRIGGER Tr_Del_BelPos ON ut_BelPos FOR DELETE
AS
  IF @@NESTLEVEL < 10
  BEGIN

   DELETE FROM BelNe WHERE RecId IN
   (  SELECT X.RecId FROM BelNe X 
       INNER JOIN DELETED D ON 
            D.PrjNr  = X.PrjNr  
        AND D.BelArt = X.BelArt 
        AND D.BelNr  = X.BelNr
        AND D.RefKey = X.RefKey
   )

   DELETE FROM BelMassPosRef WHERE RecId IN
   (  SELECT X.RecId FROM BelMassPosRef X 
       INNER JOIN DELETED D ON 
            D.PrjNr  = X.PrjNr  
        AND D.BelArt = X.BelArt 
        AND D.BelNr  = X.BelNr
        AND D.RefKey = X.PosRefKey
   )

   DELETE FROM BelText WHERE RecId IN
   (  SELECT X.RecId FROM BelText X 
       INNER JOIN DELETED D ON 
            D.PrjNr  = X.PrjNr  
        AND D.BelArt = X.BelArt 
        AND D.BelNr  = X.BelNr
        AND X.TextArt= '0'
        AND D.RefKey = X.RefKey
   )

   DELETE FROM BelText WHERE RecId IN
   (  SELECT X.RecId FROM BelText X 
       INNER JOIN DELETED D ON 
            D.PrjNr  = X.PrjNr  
        AND D.BelArt = X.BelArt 
        AND D.BelNr  = X.BelNr
        AND X.TextArt= '1'
        AND D.RefKey = X.RefKey
   )

   DELETE FROM BelText WHERE RecId IN
   (  SELECT X.RecId FROM BelText X 
       INNER JOIN DELETED D ON 
            D.PrjNr  = X.PrjNr  
        AND D.BelArt = X.BelArt 
        AND D.BelNr  = X.BelNr
        AND X.TextArt= '2'
        AND D.RefKey = X.RefKey
   )

  END
GO


IF 0 < (SELECT COUNT(*) FROM sysobjects where [xtype] = 'TR' AND [name] = 'Tr_Del_BelMassRef')
  DROP TRIGGER Tr_Del_BelMassRef
GO
CREATE TRIGGER Tr_Del_BelMassRef ON ut_BelMassRef FOR DELETE
AS
  IF @@NESTLEVEL < 10
  BEGIN

   DELETE FROM BelMassPosRef WHERE RecId IN
   (  SELECT X.RecId FROM BelMassPosRef X 
       INNER JOIN DELETED D ON 
            D.PrjNr    = X.PrjNr  
        AND D.BelArt   = X.BelArt 
        AND D.BelNr    = X.BelNr
        AND D.AmRefKey = X.AmRefKey
   )
   DELETE FROM BelText WHERE RecId IN
   (  SELECT X.RecId FROM BelText X 
       INNER JOIN DELETED D ON 
            D.PrjNr    = X.PrjNr  
        AND D.BelArt   = X.BelArt 
        AND D.BelNr    = X.BelNr
        AND X.TextArt  = '9'
        AND D.AmRefKey = X.RefKey
   )

  END
GO


IF 0 < (SELECT COUNT(*) FROM sysobjects where [xtype] = 'TR' AND [name] = 'Tr_Del_BelegArchiv')
  DROP TRIGGER Tr_Del_BelegArchiv
GO
CREATE TRIGGER Tr_Del_BelegArchiv ON dbo.ut_BelegArchiv FOR DELETE
AS
  IF @@NESTLEVEL < 10 BEGIN
    DELETE FROM [dbo].[ut_Blobs] WHERE RecId IN ( SELECT BlobId FROM DELETED )
  END
GO



