DROP TRIGGER LD0T0H_BVK_UPD
GO

DROP TRIGGER LD0T0H_BVK_DEL
GO

CREATE TRIGGER LD0T0H_BVK_UPD ON LD0P0H
AFTER UPDATE
AS 
SET NOCOUNT ON
IF
  UPDATE(INSNR) OR 
  UPDATE(AEVERKNR) OR 
  UPDATE(BESLUTNR) OR 
  UPDATE(HAND) OR 
  UPDATE(HAND2) OR 
  UPDATE(AKTNR) OR 
  UPDATE(DIARIENR)
DECLARE
  @OLD_AKTUALNR DECIMAL(9),
  @OLD_HAND     VARCHAR(10),
  @OLD_HAND2    VARCHAR(10),
  @OLD_INSNR    DECIMAL(9),
  @OLD_AEVERKNR DECIMAL(9),
  @OLD_BESLUTNR DECIMAL(9),
  @OLD_AKTNR    DECIMAL(9),
  @OLD_DIARIENR DECIMAL(9),
  @NEW_HAND     VARCHAR(10),
  @NEW_HAND2    VARCHAR(10),
  @NEW_INSNR    DECIMAL(9),
  @NEW_AEVERKNR DECIMAL(9),
  @NEW_BESLUTNR DECIMAL(9),
  @NEW_AKTNR    DECIMAL(9),
  @NEW_DIARIENR DECIMAL(9),
  @EXIST_HAND   DECIMAL(9),
  @EXIST_HAND2  DECIMAL(9),
  @EXIST_AKTUAL DECIMAL(9)

	SET @OLD_AKTUALNR = NULL
	SET @OLD_HAND     = NULL
	SET @OLD_HAND2    = NULL
  SET @OLD_INSNR    = NULL
	SET @OLD_AEVERKNR = NULL
  SET @OLD_AKTNR    = NULL
  SET @OLD_DIARIENR = NULL
  SET @NEW_BESLUTNR = NULL
	SET @NEW_HAND     = NULL
	SET @NEW_HAND2    = NULL
	SET @NEW_INSNR    = NULL
	SET @NEW_AEVERKNR = NULL
	SET @NEW_BESLUTNR = NULL
  SET @NEW_AKTNR    = NULL
  SET @NEW_DIARIENR = NULL
	SET @EXIST_HAND   = 0
	SET @EXIST_HAND2  = 0
	SET @EXIST_AKTUAL = (SELECT COUNT(*) FROM LD0P0BVK 
                             WHERE OBJEKTID = 7040 
                               AND OBJEKTNR IN (SELECT CONVERT(VARCHAR,AKTUALNR) FROM DELETED))
BEGIN 
SET XACT_ABORT ON
SET NOCOUNT ON
    IF (@EXIST_AKTUAL > 0)
    BEGIN

        DECLARE C_DELBVK CURSOR FOR 
            SELECT AKTUALNR, HAND, HAND2, INSNR, AEVERKNR, BESLUTNR, AKTNR, DIARIENR FROM DELETED
        DECLARE C_INSBVK CURSOR FOR 
            SELECT INSNR, AEVERKNR, BESLUTNR, HAND, HAND2, AKTNR, DIARIENR FROM INSERTED

        OPEN C_DELBVK
            FETCH NEXT FROM C_DELBVK INTO @OLD_AKTUALNR, @OLD_HAND, @OLD_HAND2, @OLD_INSNR, @OLD_AEVERKNR, @OLD_BESLUTNR, @OLD_AKTNR, @OLD_DIARIENR  
        OPEN C_INSBVK
            FETCH NEXT FROM C_INSBVK INTO @NEW_INSNR, @NEW_AEVERKNR, @NEW_BESLUTNR, @NEW_HAND, @NEW_HAND2, @NEW_AKTNR, @NEW_DIARIENR 
    
        WHILE @@FETCH_STATUS = 0
        BEGIN
	      SET @EXIST_HAND    = (SELECT COUNT(*) FROM LD0P0BVK 
                              WHERE OBJEKTID = 7040
                              AND KNYTNING = 1 
                              AND OBJEKTNR = CONVERT(VARCHAR,@OLD_AKTUALNR)
                              AND LD0P0BVK.HAND = @OLD_HAND)
	      SET @EXIST_HAND2   = (SELECT COUNT(*) FROM LD0P0BVK 
                              WHERE OBJEKTID = 7040
                              AND KNYTNING = 2 
                              AND OBJEKTNR = CONVERT(VARCHAR,@OLD_AKTUALNR)
                              AND LD0P0BVK.HAND = @OLD_HAND2)
	      SET @EXIST_AKTUAL  = (SELECT COUNT(*) FROM LD0P0BVK 
                              WHERE OBJEKTID = 7040 
                              AND OBJEKTNR = CONVERT(VARCHAR,@OLD_AKTUALNR))

        IF (@EXIST_HAND > 0 AND @OLD_HAND <> @NEW_HAND)
	        UPDATE LD0P0BVK SET
	        LD0P0BVK.HAND = @NEW_HAND
	        WHERE LD0P0BVK.OBJEKTID = 7040
          AND LD0P0BVK.KNYTNING = 1
          AND LD0P0BVK.OBJEKTNR = CONVERT(VARCHAR,@OLD_AKTUALNR)
          AND LD0P0BVK.HAND = @OLD_HAND

        IF (@EXIST_HAND2 > 0 AND @OLD_HAND2 <> @NEW_HAND2)
          UPDATE LD0P0BVK SET
	        LD0P0BVK.HAND = @NEW_HAND2
	        WHERE LD0P0BVK.OBJEKTID = 7040
          AND LD0P0BVK.KNYTNING = 2
          AND LD0P0BVK.OBJEKTNR = CONVERT(VARCHAR,@OLD_AKTUALNR)
          AND LD0P0BVK.HAND = @OLD_HAND2

        IF (@EXIST_AKTUAL > 0 AND ((@NEW_INSNR = 0 AND @OLD_INSNR > 0) OR (@NEW_AEVERKNR = 0 AND @OLD_AEVERKNR > 0)))
          UPDATE LD0P0BVK SET
          HUVUDOBJID = 7040,
          HUVUDOBJNR = CONVERT(VARCHAR,@OLD_AKTUALNR)
          WHERE OBJEKTID = 7040 
          AND OBJEKTNR = CONVERT(VARCHAR,@OLD_AKTUALNR)

        IF (@EXIST_AKTUAL > 0 AND 
        ((@OLD_INSNR <> @NEW_INSNR AND @NEW_INSNR > 0) OR 
        (@OLD_AEVERKNR <> @NEW_AEVERKNR AND @NEW_AEVERKNR > 0) OR 
        (@OLD_BESLUTNR <> @NEW_BESLUTNR AND @NEW_BESLUTNR > 0) OR 
        (@OLD_AKTNR <> @NEW_AKTNR AND @NEW_AKTNR > 0) OR 
        (@OLD_DIARIENR <> @NEW_DIARIENR AND @NEW_DIARIENR > 0)))

          DELETE FROM LD0P0BVK
          WHERE OBJEKTID = 7040 
          AND OBJEKTNR = CONVERT(VARCHAR,@OLD_AKTUALNR)

        FETCH NEXT FROM C_DELBVK INTO @OLD_AKTUALNR, @OLD_HAND, @OLD_HAND2, @OLD_INSNR, @OLD_AEVERKNR, @OLD_BESLUTNR, @OLD_AKTNR, @OLD_DIARIENR
        FETCH NEXT FROM C_INSBVK INTO @NEW_INSNR, @NEW_AEVERKNR, @NEW_BESLUTNR, @NEW_HAND, @NEW_HAND2, @NEW_AKTNR, @NEW_DIARIENR 
      END

      CLOSE C_DELBVK
      CLOSE C_INSBVK

      DEALLOCATE C_DELBVK
      DEALLOCATE C_INSBVK
  END
END
GO

CREATE TRIGGER LD0T0H_BVK_DEL ON LD0P0H
AFTER DELETE
AS 
SET NOCOUNT ON
BEGIN
    IF EXISTS (SELECT * FROM LD0P0BVK WHERE OBJEKTID = 7040 AND OBJEKTNR IN (SELECT CONVERT(VARCHAR,AKTUALNR) FROM DELETED))
    BEGIN 
        DELETE FROM LD0P0BVK
          WHERE OBJEKTID = 7040
            AND OBJEKTNR IN (SELECT CONVERT(VARCHAR,AKTUALNR) FROM DELETED)
    END
END
GO
