CLIENT SUPPORT » IMFI Bugs

USE [MinovaHR_Indomobil_Dev]

GO

/****** Object:  Trigger [dbo].[CombinedTrigger]    Script Date: 06/06/2023 17:39:14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[CombinedTriggerDelimit]

ON [dbo].[hr_md_orm_object]

AFTER INSERT

AS

BEGIN

    SET NOCOUNT ON;

    -- Simpan record baru yang di-insert pertama kali

    INSERT INTO hr_md_orm_object

    SELECT *

    FROM inserted

    WHERE object NOT IN (

        SELECT object

        FROM hr_md_orm_object

        WHERE class = 'DE'

    );

    -- Hapus catatan duplikat berdasarkan objectid dan start_date maksimum

    DELETE FROM hr_md_orm_object

    WHERE object IN (

        SELECT object

        FROM (

            SELECT object, ROW_NUMBER() OVER (PARTITION BY object ORDER BY start_date DESC) AS RowNumber

            FROM hr_md_orm_object

            WHERE class = 'DE' AND object IN (

                SELECT object

                FROM hr_md_orm_object

                WHERE class = 'DE'

                GROUP BY object

                HAVING COUNT(*) > 1

            )

        ) AS duplicates

        WHERE duplicates.RowNumber = 1

    ) AND class = 'DE'

    AND end_date = (

        SELECT MAX(end_date)

        FROM hr_md_orm_object

        WHERE object IN (

            SELECT object

            FROM (

                SELECT object, ROW_NUMBER() OVER (PARTITION BY object ORDER BY start_date DESC) AS RowNumber

                FROM hr_md_orm_object

                WHERE class = 'DE' AND object IN (

                    SELECT object

                    FROM hr_md_orm_object

                    WHERE class = 'DE'

                    GROUP BY object

                    HAVING COUNT(*) > 1

                )

            ) AS duplicates

            WHERE duplicates.RowNumber > 1

        )

    );

    -- Memperbarui field enddate pada tabel hr_md_orm_relationship

    UPDATE r

    SET r.end_date = i.end_date

    FROM hr_md_orm_relationship AS r

    INNER JOIN hr_md_orm_object AS i ON r.[object] = i.[object]

    WHERE r.class = 'DE'

      AND i.class = 'DE'; -- Ubah sesuai dengan hubungan antara tabel yang sesuai

END;