Create example model database

-- KNOTS --------------------------------------------------------------------------------------------------------------
--
-- Knots are used to store finite sets of values, normally used to describe states
-- of entities (through knotted attributes) or relationships (through knotted ties).
-- Knots have their own surrogate identities and are therefore immutable.
-- Values can be added to the set over time though.
-- Knots should have values that are mutually exclusive and exhaustive.
--
-- Knot table ---------------------------------------------------------------------------------------------------------
-- PAT_ParentalType table
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('PAT_ParentalType', 'U') IS NULL
CREATE TABLE [dbo].[PAT_ParentalType] (
    PAT_ID tinyint not null,
    PAT_ParentalType varchar(42) not null,
    Metadata_PAT int not null,
    constraint pkPAT_ParentalType primary key (
        PAT_ID asc
    ),
    constraint uqPAT_ParentalType unique (
        PAT_ParentalType
    )
);
GO
-- Knot table ---------------------------------------------------------------------------------------------------------
-- PLV_ProfessionalLevel table
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('PLV_ProfessionalLevel', 'U') IS NULL
CREATE TABLE [dbo].[PLV_ProfessionalLevel] (
    PLV_ID tinyint not null,
    PLV_ProfessionalLevel varchar(42) not null,
    Metadata_PLV int not null,
    constraint pkPLV_ProfessionalLevel primary key (
        PLV_ID asc
    ),
    constraint uqPLV_ProfessionalLevel unique (
        PLV_ProfessionalLevel
    )
);
GO
-- Knot table ---------------------------------------------------------------------------------------------------------
-- RAT_Rating table
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('RAT_Rating', 'U') IS NULL
CREATE TABLE [dbo].[RAT_Rating] (
    RAT_ID tinyint not null,
    RAT_Rating varchar(42) not null,
    Metadata_RAT int not null,
    constraint pkRAT_Rating primary key (
        RAT_ID asc
    ),
    constraint uqRAT_Rating unique (
        RAT_Rating
    )
);
GO
-- Knot table ---------------------------------------------------------------------------------------------------------
-- GEN_Gender table
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('GEN_Gender', 'U') IS NULL
CREATE TABLE [dbo].[GEN_Gender] (
    GEN_ID bit not null,
    GEN_Gender varchar(42) not null,
    Metadata_GEN int not null,
    constraint pkGEN_Gender primary key (
        GEN_ID asc
    ),
    constraint uqGEN_Gender unique (
        GEN_Gender
    )
);
GO
-- ANCHORS AND ATTRIBUTES ---------------------------------------------------------------------------------------------
--
-- Anchors are used to store the identities of entities.
-- Anchors are immutable.
-- Attributes are used to store values for properties of entities.
-- Attributes are mutable, their values may change over one or more types of time.
-- Attributes have four flavors: static, historized, knotted static, and knotted historized.
-- Anchors may have zero or more adjoined attributes.
--
-- Anchor table -------------------------------------------------------------------------------------------------------
-- EV_Event table (with 0 attributes)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('EV_Event', 'U') IS NULL
CREATE TABLE [dbo].[EV_Event] (
    EV_ID int IDENTITY(1,1) not null,
    Metadata_EV int not null,
    constraint pkEV_Event primary key (
        EV_ID asc
    )
);
GO
-- Anchor table -------------------------------------------------------------------------------------------------------
-- PR_Program table (with 1 attributes)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('PR_Program', 'U') IS NULL
CREATE TABLE [dbo].[PR_Program] (
    PR_ID int IDENTITY(1,1) not null,
    Metadata_PR int not null,
    constraint pkPR_Program primary key (
        PR_ID asc
    )
);
GO
-- Static attribute table ---------------------------------------------------------------------------------------------
-- PR_NAM_Program_Name table (on PR_Program)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('PR_NAM_Program_Name', 'U') IS NULL
CREATE TABLE [dbo].[PR_NAM_Program_Name] (
    PR_NAM_PR_ID int not null,
    PR_NAM_Program_Name varchar(42) not null,
    Metadata_PR_NAM int not null,
    constraint fkPR_NAM_Program_Name foreign key (
        PR_NAM_PR_ID
    ) references [dbo].[PR_Program](PR_ID),
    constraint pkPR_NAM_Program_Name primary key (
        PR_NAM_PR_ID asc
    )
);
GO
-- Anchor table -------------------------------------------------------------------------------------------------------
-- ST_Stage table (with 2 attributes)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('ST_Stage', 'U') IS NULL
CREATE TABLE [dbo].[ST_Stage] (
    ST_ID int IDENTITY(1,1) not null,
    Metadata_ST int not null,
    constraint pkST_Stage primary key (
        ST_ID asc
    )
);
GO
-- Historized attribute table -----------------------------------------------------------------------------------------
-- ST_NAM_Stage_Name table (on ST_Stage)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('ST_NAM_Stage_Name', 'U') IS NULL
CREATE TABLE [dbo].[ST_NAM_Stage_Name] (
    ST_NAM_ST_ID int not null,
    ST_NAM_Stage_Name varchar(42) not null,
    ST_NAM_ChangedAt datetime not null,
    Metadata_ST_NAM int not null,
    constraint fkST_NAM_Stage_Name foreign key (
        ST_NAM_ST_ID
    ) references [dbo].[ST_Stage](ST_ID),
    constraint pkST_NAM_Stage_Name primary key (
        ST_NAM_ST_ID asc,
        ST_NAM_ChangedAt desc
    )
);
GO
-- Static attribute table ---------------------------------------------------------------------------------------------
-- ST_LOC_Stage_Location table (on ST_Stage)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('ST_LOC_Stage_Location', 'U') IS NULL
CREATE TABLE [dbo].[ST_LOC_Stage_Location] (
    ST_LOC_ST_ID int not null,
    ST_LOC_Stage_Location varchar(42) not null,
    Metadata_ST_LOC int not null,
    constraint fkST_LOC_Stage_Location foreign key (
        ST_LOC_ST_ID
    ) references [dbo].[ST_Stage](ST_ID),
    constraint pkST_LOC_Stage_Location primary key (
        ST_LOC_ST_ID asc
    )
);
GO
-- Anchor table -------------------------------------------------------------------------------------------------------
-- PE_Performance table (with 3 attributes)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('PE_Performance', 'U') IS NULL
CREATE TABLE [dbo].[PE_Performance] (
    PE_ID int IDENTITY(1,1) not null,
    Metadata_PE int not null,
    constraint pkPE_Performance primary key (
        PE_ID asc
    )
);
GO
-- Static attribute table ---------------------------------------------------------------------------------------------
-- PE_DAT_Performance_Date table (on PE_Performance)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('PE_DAT_Performance_Date', 'U') IS NULL
CREATE TABLE [dbo].[PE_DAT_Performance_Date] (
    PE_DAT_PE_ID int not null,
    PE_DAT_Performance_Date datetime not null,
    Metadata_PE_DAT int not null,
    constraint fkPE_DAT_Performance_Date foreign key (
        PE_DAT_PE_ID
    ) references [dbo].[PE_Performance](PE_ID),
    constraint pkPE_DAT_Performance_Date primary key (
        PE_DAT_PE_ID asc
    )
);
GO
-- Static attribute table ---------------------------------------------------------------------------------------------
-- PE_AUD_Performance_Audience table (on PE_Performance)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('PE_AUD_Performance_Audience', 'U') IS NULL
CREATE TABLE [dbo].[PE_AUD_Performance_Audience] (
    PE_AUD_PE_ID int not null,
    PE_AUD_Performance_Audience int not null,
    Metadata_PE_AUD int not null,
    constraint fkPE_AUD_Performance_Audience foreign key (
        PE_AUD_PE_ID
    ) references [dbo].[PE_Performance](PE_ID),
    constraint pkPE_AUD_Performance_Audience primary key (
        PE_AUD_PE_ID asc
    )
);
GO
-- Static attribute table ---------------------------------------------------------------------------------------------
-- PE_REV_Performance_Revenue table (on PE_Performance)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('PE_REV_Performance_Revenue', 'U') IS NULL
CREATE TABLE [dbo].[PE_REV_Performance_Revenue] (
    PE_REV_PE_ID int not null,
    PE_REV_Performance_Revenue money not null,
    Metadata_PE_REV int not null,
    constraint fkPE_REV_Performance_Revenue foreign key (
        PE_REV_PE_ID
    ) references [dbo].[PE_Performance](PE_ID),
    constraint pkPE_REV_Performance_Revenue primary key (
        PE_REV_PE_ID asc
    )
);
GO
-- Anchor table -------------------------------------------------------------------------------------------------------
-- AC_Actor table (with 3 attributes)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('AC_Actor', 'U') IS NULL
CREATE TABLE [dbo].[AC_Actor] (
    AC_ID int IDENTITY(1,1) not null,
    Metadata_AC int not null,
    constraint pkAC_Actor primary key (
        AC_ID asc
    )
);
GO
-- Historized attribute table -----------------------------------------------------------------------------------------
-- AC_NAM_Actor_Name table (on AC_Actor)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('AC_NAM_Actor_Name', 'U') IS NULL
CREATE TABLE [dbo].[AC_NAM_Actor_Name] (
    AC_NAM_AC_ID int not null,
    AC_NAM_Actor_Name varchar(42) not null,
    AC_NAM_ChangedAt datetime not null,
    Metadata_AC_NAM int not null,
    constraint fkAC_NAM_Actor_Name foreign key (
        AC_NAM_AC_ID
    ) references [dbo].[AC_Actor](AC_ID),
    constraint pkAC_NAM_Actor_Name primary key (
        AC_NAM_AC_ID asc,
        AC_NAM_ChangedAt desc
    )
);
GO
-- Knotted static attribute table -------------------------------------------------------------------------------------
-- AC_GEN_Actor_Gender table (on AC_Actor)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('AC_GEN_Actor_Gender', 'U') IS NULL
CREATE TABLE [dbo].[AC_GEN_Actor_Gender] (
    AC_GEN_AC_ID int not null,
    AC_GEN_GEN_ID bit not null,
    Metadata_AC_GEN int not null,
    constraint fk_A_AC_GEN_Actor_Gender foreign key (
        AC_GEN_AC_ID
    ) references [dbo].[AC_Actor](AC_ID),
    constraint fk_K_AC_GEN_Actor_Gender foreign key (
        AC_GEN_GEN_ID
    ) references [dbo].[GEN_Gender](GEN_ID),
    constraint pkAC_GEN_Actor_Gender primary key (
        AC_GEN_AC_ID asc
    )
);
GO
-- Knotted historized attribute table ---------------------------------------------------------------------------------
-- AC_PLV_Actor_ProfessionalLevel table (on AC_Actor)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('AC_PLV_Actor_ProfessionalLevel', 'U') IS NULL
CREATE TABLE [dbo].[AC_PLV_Actor_ProfessionalLevel] (
    AC_PLV_AC_ID int not null,
    AC_PLV_PLV_ID tinyint not null,
    AC_PLV_ChangedAt datetime not null,
    Metadata_AC_PLV int not null,
    constraint fk_A_AC_PLV_Actor_ProfessionalLevel foreign key (
        AC_PLV_AC_ID
    ) references [dbo].[AC_Actor](AC_ID),
    constraint fk_K_AC_PLV_Actor_ProfessionalLevel foreign key (
        AC_PLV_PLV_ID
    ) references [dbo].[PLV_ProfessionalLevel](PLV_ID),
    constraint pkAC_PLV_Actor_ProfessionalLevel primary key (
        AC_PLV_AC_ID asc,
        AC_PLV_ChangedAt desc
    )
);
GO
-- TIES ---------------------------------------------------------------------------------------------------------------
--
-- Ties are used to represent relationships between entities.
-- They come in four flavors: static, historized, knotted static, and knotted historized.
-- Ties have cardinality, constraining how members may participate in the relationship.
-- Every entity that is a member in a tie has a specified role in the relationship.
-- Ties must have at least two anchor roles and zero or more knot roles.
--
-- Knotted static tie table -------------------------------------------------------------------------------------------
-- AC_parent_AC_child_PAT_having table (having 3 roles)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('AC_parent_AC_child_PAT_having', 'U') IS NULL
CREATE TABLE [dbo].[AC_parent_AC_child_PAT_having] (
    AC_ID_parent int not null,
    AC_ID_child int not null,
    PAT_ID_having tinyint not null,
    Metadata_AC_parent_AC_child_PAT_having int not null,
    constraint AC_parent_AC_child_PAT_having_fkAC_parent foreign key (
        AC_ID_parent
    ) references AC_Actor(AC_ID),
    constraint AC_parent_AC_child_PAT_having_fkAC_child foreign key (
        AC_ID_child
    ) references AC_Actor(AC_ID),
    constraint AC_parent_AC_child_PAT_having_fkPAT_having foreign key (
        PAT_ID_having
    ) references PAT_ParentalType(PAT_ID),
    constraint pkAC_parent_AC_child_PAT_having primary key (
        AC_ID_parent asc,
        AC_ID_child asc,
        PAT_ID_having asc
    )
);
GO
-- Historized tie table -----------------------------------------------------------------------------------------------
-- ST_atLocation_PR_isPlaying table (having 2 roles)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('ST_atLocation_PR_isPlaying', 'U') IS NULL
CREATE TABLE [dbo].[ST_atLocation_PR_isPlaying] (
    ST_ID_atLocation int not null,
    PR_ID_isPlaying int not null,
    ST_atLocation_PR_isPlaying_ChangedAt datetime not null,
    Metadata_ST_atLocation_PR_isPlaying int not null,
    constraint ST_atLocation_PR_isPlaying_fkST_atLocation foreign key (
        ST_ID_atLocation
    ) references ST_Stage(ST_ID),
    constraint ST_atLocation_PR_isPlaying_fkPR_isPlaying foreign key (
        PR_ID_isPlaying
    ) references PR_Program(PR_ID),
    constraint pkST_atLocation_PR_isPlaying primary key (
        ST_ID_atLocation asc,
        PR_ID_isPlaying asc,
        ST_atLocation_PR_isPlaying_ChangedAt desc
    )
);
GO
-- Static tie table ---------------------------------------------------------------------------------------------------
-- PE_at_PR_wasPlayed table (having 2 roles)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('PE_at_PR_wasPlayed', 'U') IS NULL
CREATE TABLE [dbo].[PE_at_PR_wasPlayed] (
    PE_ID_at int not null,
    PR_ID_wasPlayed int not null,
    Metadata_PE_at_PR_wasPlayed int not null,
    constraint PE_at_PR_wasPlayed_fkPE_at foreign key (
        PE_ID_at
    ) references PE_Performance(PE_ID),
    constraint PE_at_PR_wasPlayed_fkPR_wasPlayed foreign key (
        PR_ID_wasPlayed
    ) references PR_Program(PR_ID),
    constraint pkPE_at_PR_wasPlayed primary key (
        PE_ID_at asc
    )
);
GO
-- Static tie table ---------------------------------------------------------------------------------------------------
-- PE_wasHeld_ST_atLocation table (having 2 roles)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('PE_wasHeld_ST_atLocation', 'U') IS NULL
CREATE TABLE [dbo].[PE_wasHeld_ST_atLocation] (
    PE_ID_wasHeld int not null,
    ST_ID_atLocation int not null,
    Metadata_PE_wasHeld_ST_atLocation int not null,
    constraint PE_wasHeld_ST_atLocation_fkPE_wasHeld foreign key (
        PE_ID_wasHeld
    ) references PE_Performance(PE_ID),
    constraint PE_wasHeld_ST_atLocation_fkST_atLocation foreign key (
        ST_ID_atLocation
    ) references ST_Stage(ST_ID),
    constraint pkPE_wasHeld_ST_atLocation primary key (
        PE_ID_wasHeld asc
    )
);
GO
-- Historized tie table -----------------------------------------------------------------------------------------------
-- AC_exclusive_AC_with table (having 2 roles)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('AC_exclusive_AC_with', 'U') IS NULL
CREATE TABLE [dbo].[AC_exclusive_AC_with] (
    AC_ID_exclusive int not null,
    AC_ID_with int not null,
    AC_exclusive_AC_with_ChangedAt datetime not null,
    Metadata_AC_exclusive_AC_with int not null,
    constraint AC_exclusive_AC_with_fkAC_exclusive foreign key (
        AC_ID_exclusive
    ) references AC_Actor(AC_ID),
    constraint AC_exclusive_AC_with_fkAC_with foreign key (
        AC_ID_with
    ) references AC_Actor(AC_ID),
    constraint AC_exclusive_AC_with_uqAC_exclusive unique (
        AC_ID_exclusive,
        AC_exclusive_AC_with_ChangedAt
    ),
    constraint AC_exclusive_AC_with_uqAC_with unique (
        AC_ID_with,
        AC_exclusive_AC_with_ChangedAt
    ),
    constraint pkAC_exclusive_AC_with primary key (
        AC_ID_exclusive asc,
        AC_ID_with asc,
        AC_exclusive_AC_with_ChangedAt desc
    )
);
GO
-- Static tie table ---------------------------------------------------------------------------------------------------
-- PE_subset_EV_of table (having 2 roles)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('PE_subset_EV_of', 'U') IS NULL
CREATE TABLE [dbo].[PE_subset_EV_of] (
    PE_ID_subset int not null,
    EV_ID_of int not null,
    Metadata_PE_subset_EV_of int not null,
    constraint PE_subset_EV_of_fkPE_subset foreign key (
        PE_ID_subset
    ) references PE_Performance(PE_ID),
    constraint PE_subset_EV_of_fkEV_of foreign key (
        EV_ID_of
    ) references EV_Event(EV_ID),
    constraint PE_subset_EV_of_uqPE_subset unique (
        PE_ID_subset
    ),
    constraint PE_subset_EV_of_uqEV_of unique (
        EV_ID_of
    ),
    constraint pkPE_subset_EV_of primary key (
        PE_ID_subset asc,
        EV_ID_of asc
    )
);
GO
-- Static tie table ---------------------------------------------------------------------------------------------------
-- PE_in_AC_wasCast table (having 2 roles)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('PE_in_AC_wasCast', 'U') IS NULL
CREATE TABLE [dbo].[PE_in_AC_wasCast] (
    PE_ID_in int not null,
    AC_ID_wasCast int not null,
    Metadata_PE_in_AC_wasCast int not null,
    constraint PE_in_AC_wasCast_fkPE_in foreign key (
        PE_ID_in
    ) references PE_Performance(PE_ID),
    constraint PE_in_AC_wasCast_fkAC_wasCast foreign key (
        AC_ID_wasCast
    ) references AC_Actor(AC_ID),
    constraint pkPE_in_AC_wasCast primary key (
        PE_ID_in asc,
        AC_ID_wasCast asc
    )
);
GO
-- Knotted historized tie table ---------------------------------------------------------------------------------------
-- AC_part_PR_in_RAT_got table (having 3 roles)
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('AC_part_PR_in_RAT_got', 'U') IS NULL
CREATE TABLE [dbo].[AC_part_PR_in_RAT_got] (
    AC_ID_part int not null,
    PR_ID_in int not null,
    RAT_ID_got tinyint not null,
    AC_part_PR_in_RAT_got_ChangedAt datetime not null,
    Metadata_AC_part_PR_in_RAT_got int not null,
    constraint AC_part_PR_in_RAT_got_fkAC_part foreign key (
        AC_ID_part
    ) references AC_Actor(AC_ID),
    constraint AC_part_PR_in_RAT_got_fkPR_in foreign key (
        PR_ID_in
    ) references PR_Program(PR_ID),
    constraint AC_part_PR_in_RAT_got_fkRAT_got foreign key (
        RAT_ID_got
    ) references RAT_Rating(RAT_ID),
    constraint pkAC_part_PR_in_RAT_got primary key (
        AC_ID_part asc,
        PR_ID_in asc,
        AC_part_PR_in_RAT_got_ChangedAt desc
    )
);
GO
-- ATTRIBUTE RESTATEMENT CONSTRAINTS ----------------------------------------------------------------------------------
--
-- Attributes may be prevented from storing restatements.
-- A restatement is when the same value occurs for two adjacent points
-- in changing time.
--
-- returns 1 for at least one equal surrounding value, 0 for different surrounding values
--
-- @id the identity of the anchored entity
-- @value the value of the attribute
-- @changed the point in time from which this value shall represent a change
--
-- Restatement Finder Function and Constraint -------------------------------------------------------------------------
-- rfST_NAM_Stage_Name restatement finder, also used by the insert and update triggers for idempotent attributes
-- rcST_NAM_Stage_Name restatement constraint, with checking made by the finder function
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('rfST_NAM_Stage_Name', 'FN') IS NULL
BEGIN
    EXEC('
    CREATE FUNCTION [dbo].[rfST_NAM_Stage_Name] (
        @id int,
        @value varchar(42),
        @changed datetime
    )
    RETURNS tinyint AS
    BEGIN RETURN (
        CASE WHEN @value IN ((
            SELECT TOP 1
                pre.ST_NAM_Stage_Name
            FROM
                [dbo].[ST_NAM_Stage_Name] pre
            WHERE
                pre.ST_NAM_ST_ID = @id
            AND
                pre.ST_NAM_ChangedAt < @changed
            ORDER BY
                pre.ST_NAM_ChangedAt DESC
        ),(
            SELECT TOP 1
                fol.ST_NAM_Stage_Name
            FROM
                [dbo].[ST_NAM_Stage_Name] fol
            WHERE
                fol.ST_NAM_ST_ID = @id
            AND
                fol.ST_NAM_ChangedAt > @changed
            ORDER BY
                fol.ST_NAM_ChangedAt ASC
        ))
        THEN 1
        ELSE 0
        END
    );
    END
    ');
    ALTER TABLE [dbo].[ST_NAM_Stage_Name]
    ADD CONSTRAINT [rcST_NAM_Stage_Name] CHECK (
        [dbo].[rfST_NAM_Stage_Name] (
            ST_NAM_ST_ID,
            ST_NAM_Stage_Name,
            ST_NAM_ChangedAt
        ) = 0
    );
END
GO
-- Restatement Finder Function and Constraint -------------------------------------------------------------------------
-- rfAC_NAM_Actor_Name restatement finder, also used by the insert and update triggers for idempotent attributes
-- rcAC_NAM_Actor_Name restatement constraint, with checking made by the finder function
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('rfAC_NAM_Actor_Name', 'FN') IS NULL
BEGIN
    EXEC('
    CREATE FUNCTION [dbo].[rfAC_NAM_Actor_Name] (
        @id int,
        @value varchar(42),
        @changed datetime
    )
    RETURNS tinyint AS
    BEGIN RETURN (
        CASE WHEN @value IN ((
            SELECT TOP 1
                pre.AC_NAM_Actor_Name
            FROM
                [dbo].[AC_NAM_Actor_Name] pre
            WHERE
                pre.AC_NAM_AC_ID = @id
            AND
                pre.AC_NAM_ChangedAt < @changed
            ORDER BY
                pre.AC_NAM_ChangedAt DESC
        ),(
            SELECT TOP 1
                fol.AC_NAM_Actor_Name
            FROM
                [dbo].[AC_NAM_Actor_Name] fol
            WHERE
                fol.AC_NAM_AC_ID = @id
            AND
                fol.AC_NAM_ChangedAt > @changed
            ORDER BY
                fol.AC_NAM_ChangedAt ASC
        ))
        THEN 1
        ELSE 0
        END
    );
    END
    ');
    ALTER TABLE [dbo].[AC_NAM_Actor_Name]
    ADD CONSTRAINT [rcAC_NAM_Actor_Name] CHECK (
        [dbo].[rfAC_NAM_Actor_Name] (
            AC_NAM_AC_ID,
            AC_NAM_Actor_Name,
            AC_NAM_ChangedAt
        ) = 0
    );
END
GO
-- Restatement Finder Function and Constraint -------------------------------------------------------------------------
-- rfAC_PLV_Actor_ProfessionalLevel restatement finder, also used by the insert and update triggers for idempotent attributes
-- rcAC_PLV_Actor_ProfessionalLevel restatement constraint, with checking made by the finder function
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('rfAC_PLV_Actor_ProfessionalLevel', 'FN') IS NULL
BEGIN
    EXEC('
    CREATE FUNCTION [dbo].[rfAC_PLV_Actor_ProfessionalLevel] (
        @id int,
        @value tinyint,
        @changed datetime
    )
    RETURNS tinyint AS
    BEGIN RETURN (
        CASE WHEN @value IN ((
            SELECT TOP 1
                pre.AC_PLV_PLV_ID
            FROM
                [dbo].[AC_PLV_Actor_ProfessionalLevel] pre
            WHERE
                pre.AC_PLV_AC_ID = @id
            AND
                pre.AC_PLV_ChangedAt < @changed
            ORDER BY
                pre.AC_PLV_ChangedAt DESC
        ),(
            SELECT TOP 1
                fol.AC_PLV_PLV_ID
            FROM
                [dbo].[AC_PLV_Actor_ProfessionalLevel] fol
            WHERE
                fol.AC_PLV_AC_ID = @id
            AND
                fol.AC_PLV_ChangedAt > @changed
            ORDER BY
                fol.AC_PLV_ChangedAt ASC
        ))
        THEN 1
        ELSE 0
        END
    );
    END
    ');
    ALTER TABLE [dbo].[AC_PLV_Actor_ProfessionalLevel]
    ADD CONSTRAINT [rcAC_PLV_Actor_ProfessionalLevel] CHECK (
        [dbo].[rfAC_PLV_Actor_ProfessionalLevel] (
            AC_PLV_AC_ID,
            AC_PLV_PLV_ID,
            AC_PLV_ChangedAt
        ) = 0
    );
END
GO
-- KEY GENERATORS -----------------------------------------------------------------------------------------------------
--
-- These stored procedures can be used to generate identities of entities.
-- Corresponding anchors must have an incrementing identity column.
--
-- Key Generation Stored Procedure ------------------------------------------------------------------------------------
-- kEV_Event identity by surrogate key generation stored procedure
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('kEV_Event', 'P') IS NULL
BEGIN
    EXEC('
    CREATE PROCEDURE [dbo].[kEV_Event] (
        @requestedNumberOfIdentities bigint,
        @metadata int
    ) AS
    BEGIN
        SET NOCOUNT ON;
        IF @requestedNumberOfIdentities > 0
        BEGIN
            WITH idGenerator (idNumber) AS (
                SELECT
                    1
                UNION ALL
                SELECT
                    idNumber + 1
                FROM
                    idGenerator
                WHERE
                    idNumber < @requestedNumberOfIdentities
            )
            INSERT INTO [dbo].[EV_Event] (
                Metadata_EV
            )
            OUTPUT
                inserted.EV_ID
            SELECT
                @metadata
            FROM
                idGenerator
            OPTION (maxrecursion 0);
        END
    END
    ');
END
GO
-- Key Generation Stored Procedure ------------------------------------------------------------------------------------
-- kPR_Program identity by surrogate key generation stored procedure
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('kPR_Program', 'P') IS NULL
BEGIN
    EXEC('
    CREATE PROCEDURE [dbo].[kPR_Program] (
        @requestedNumberOfIdentities bigint,
        @metadata int
    ) AS
    BEGIN
        SET NOCOUNT ON;
        IF @requestedNumberOfIdentities > 0
        BEGIN
            WITH idGenerator (idNumber) AS (
                SELECT
                    1
                UNION ALL
                SELECT
                    idNumber + 1
                FROM
                    idGenerator
                WHERE
                    idNumber < @requestedNumberOfIdentities
            )
            INSERT INTO [dbo].[PR_Program] (
                Metadata_PR
            )
            OUTPUT
                inserted.PR_ID
            SELECT
                @metadata
            FROM
                idGenerator
            OPTION (maxrecursion 0);
        END
    END
    ');
END
GO
-- Key Generation Stored Procedure ------------------------------------------------------------------------------------
-- kST_Stage identity by surrogate key generation stored procedure
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('kST_Stage', 'P') IS NULL
BEGIN
    EXEC('
    CREATE PROCEDURE [dbo].[kST_Stage] (
        @requestedNumberOfIdentities bigint,
        @metadata int
    ) AS
    BEGIN
        SET NOCOUNT ON;
        IF @requestedNumberOfIdentities > 0
        BEGIN
            WITH idGenerator (idNumber) AS (
                SELECT
                    1
                UNION ALL
                SELECT
                    idNumber + 1
                FROM
                    idGenerator
                WHERE
                    idNumber < @requestedNumberOfIdentities
            )
            INSERT INTO [dbo].[ST_Stage] (
                Metadata_ST
            )
            OUTPUT
                inserted.ST_ID
            SELECT
                @metadata
            FROM
                idGenerator
            OPTION (maxrecursion 0);
        END
    END
    ');
END
GO
-- Key Generation Stored Procedure ------------------------------------------------------------------------------------
-- kPE_Performance identity by surrogate key generation stored procedure
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('kPE_Performance', 'P') IS NULL
BEGIN
    EXEC('
    CREATE PROCEDURE [dbo].[kPE_Performance] (
        @requestedNumberOfIdentities bigint,
        @metadata int
    ) AS
    BEGIN
        SET NOCOUNT ON;
        IF @requestedNumberOfIdentities > 0
        BEGIN
            WITH idGenerator (idNumber) AS (
                SELECT
                    1
                UNION ALL
                SELECT
                    idNumber + 1
                FROM
                    idGenerator
                WHERE
                    idNumber < @requestedNumberOfIdentities
            )
            INSERT INTO [dbo].[PE_Performance] (
                Metadata_PE
            )
            OUTPUT
                inserted.PE_ID
            SELECT
                @metadata
            FROM
                idGenerator
            OPTION (maxrecursion 0);
        END
    END
    ');
END
GO
-- Key Generation Stored Procedure ------------------------------------------------------------------------------------
-- kAC_Actor identity by surrogate key generation stored procedure
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('kAC_Actor', 'P') IS NULL
BEGIN
    EXEC('
    CREATE PROCEDURE [dbo].[kAC_Actor] (
        @requestedNumberOfIdentities bigint,
        @metadata int
    ) AS
    BEGIN
        SET NOCOUNT ON;
        IF @requestedNumberOfIdentities > 0
        BEGIN
            WITH idGenerator (idNumber) AS (
                SELECT
                    1
                UNION ALL
                SELECT
                    idNumber + 1
                FROM
                    idGenerator
                WHERE
                    idNumber < @requestedNumberOfIdentities
            )
            INSERT INTO [dbo].[AC_Actor] (
                Metadata_AC
            )
            OUTPUT
                inserted.AC_ID
            SELECT
                @metadata
            FROM
                idGenerator
            OPTION (maxrecursion 0);
        END
    END
    ');
END
GO
-- ATTRIBUTE REWINDERS ------------------------------------------------------------------------------------------------
--
-- These table valued functions rewind an attribute table to the given
-- point in changing time. It does not pick a temporal perspective and
-- instead shows all rows that have been in effect before that point
-- in time.
--
-- @changingTimepoint the point in changing time to rewind to
--
-- Attribute rewinder -------------------------------------------------------------------------------------------------
-- rST_NAM_Stage_Name rewinding over changing time function
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('rST_NAM_Stage_Name','IF') IS NULL
BEGIN
    EXEC('
    CREATE FUNCTION [dbo].[rST_NAM_Stage_Name] (
        @changingTimepoint datetime
    )
    RETURNS TABLE WITH SCHEMABINDING AS RETURN
    SELECT
        Metadata_ST_NAM,
        ST_NAM_ST_ID,
        ST_NAM_Stage_Name,
        ST_NAM_ChangedAt
    FROM
        [dbo].[ST_NAM_Stage_Name]
    WHERE
        ST_NAM_ChangedAt <= @changingTimepoint;
    ');
END
GO
-- Attribute rewinder -------------------------------------------------------------------------------------------------
-- rAC_NAM_Actor_Name rewinding over changing time function
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('rAC_NAM_Actor_Name','IF') IS NULL
BEGIN
    EXEC('
    CREATE FUNCTION [dbo].[rAC_NAM_Actor_Name] (
        @changingTimepoint datetime
    )
    RETURNS TABLE WITH SCHEMABINDING AS RETURN
    SELECT
        Metadata_AC_NAM,
        AC_NAM_AC_ID,
        AC_NAM_Actor_Name,
        AC_NAM_ChangedAt
    FROM
        [dbo].[AC_NAM_Actor_Name]
    WHERE
        AC_NAM_ChangedAt <= @changingTimepoint;
    ');
END
GO
-- Attribute rewinder -------------------------------------------------------------------------------------------------
-- rAC_PLV_Actor_ProfessionalLevel rewinding over changing time function
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('rAC_PLV_Actor_ProfessionalLevel','IF') IS NULL
BEGIN
    EXEC('
    CREATE FUNCTION [dbo].[rAC_PLV_Actor_ProfessionalLevel] (
        @changingTimepoint datetime
    )
    RETURNS TABLE WITH SCHEMABINDING AS RETURN
    SELECT
        Metadata_AC_PLV,
        AC_PLV_AC_ID,
        AC_PLV_PLV_ID,
        AC_PLV_ChangedAt
    FROM
        [dbo].[AC_PLV_Actor_ProfessionalLevel]
    WHERE
        AC_PLV_ChangedAt <= @changingTimepoint;
    ');
END
GO
-- ANCHOR TEMPORAL PERSPECTIVES ---------------------------------------------------------------------------------------
--
-- These table valued functions simplify temporal querying by providing a temporal
-- perspective of each anchor. There are four types of perspectives: latest,
-- point-in-time, difference, and now. They also denormalize the anchor, its attributes,
-- and referenced knots from sixth to third normal form.
--
-- The latest perspective shows the latest available information for each anchor.
-- The now perspective shows the information as it is right now.
-- The point-in-time perspective lets you travel through the information to the given timepoint.
--
-- @changingTimepoint the point in changing time to travel to
--
-- The difference perspective shows changes between the two given timepoints, and for
-- changes in all or a selection of attributes.
--
-- @intervalStart the start of the interval for finding changes
-- @intervalEnd the end of the interval for finding changes
-- @selection a list of mnemonics for tracked attributes, ie 'MNE MON ICS', or null for all
--
-- Drop perspectives --------------------------------------------------------------------------------------------------
IF Object_ID('dEV_Event', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[dEV_Event];
IF Object_ID('nEV_Event', 'V') IS NOT NULL
DROP VIEW [dbo].[nEV_Event];
IF Object_ID('pEV_Event', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[pEV_Event];
IF Object_ID('lEV_Event', 'V') IS NOT NULL
DROP VIEW [dbo].[lEV_Event];
GO
-- Drop perspectives --------------------------------------------------------------------------------------------------
IF Object_ID('dPR_Program', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[dPR_Program];
IF Object_ID('nPR_Program', 'V') IS NOT NULL
DROP VIEW [dbo].[nPR_Program];
IF Object_ID('pPR_Program', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[pPR_Program];
IF Object_ID('lPR_Program', 'V') IS NOT NULL
DROP VIEW [dbo].[lPR_Program];
GO
-- Latest perspective -------------------------------------------------------------------------------------------------
-- lPR_Program viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lPR_Program] WITH SCHEMABINDING AS
SELECT
    [PR].PR_ID,
    [PR].Metadata_PR,
    [NAM].PR_NAM_PR_ID,
    [NAM].Metadata_PR_NAM,
    [NAM].PR_NAM_Program_Name
FROM
    [dbo].[PR_Program] [PR]
LEFT JOIN
    [dbo].[PR_NAM_Program_Name] [NAM]
ON
    [NAM].PR_NAM_PR_ID = [PR].PR_ID;
GO
-- Point-in-time perspective ------------------------------------------------------------------------------------------
-- pPR_Program viewed as it was on the given timepoint
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pPR_Program] (
    @changingTimepoint datetime2(7)
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
    [PR].PR_ID,
    [PR].Metadata_PR,
    [NAM].PR_NAM_PR_ID,
    [NAM].Metadata_PR_NAM,
    [NAM].PR_NAM_Program_Name
FROM
    [dbo].[PR_Program] [PR]
LEFT JOIN
    [dbo].[PR_NAM_Program_Name] [NAM]
ON
    [NAM].PR_NAM_PR_ID = [PR].PR_ID;
GO
-- Now perspective ----------------------------------------------------------------------------------------------------
-- nPR_Program viewed as it currently is (cannot include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[nPR_Program]
AS
SELECT
    *
FROM
    [dbo].[pPR_Program](sysdatetime());
GO
-- Drop perspectives --------------------------------------------------------------------------------------------------
IF Object_ID('dST_Stage', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[dST_Stage];
IF Object_ID('nST_Stage', 'V') IS NOT NULL
DROP VIEW [dbo].[nST_Stage];
IF Object_ID('pST_Stage', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[pST_Stage];
IF Object_ID('lST_Stage', 'V') IS NOT NULL
DROP VIEW [dbo].[lST_Stage];
GO
-- Latest perspective -------------------------------------------------------------------------------------------------
-- lST_Stage viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lST_Stage] WITH SCHEMABINDING AS
SELECT
    [ST].ST_ID,
    [ST].Metadata_ST,
    [NAM].ST_NAM_ST_ID,
    [NAM].Metadata_ST_NAM,
    [NAM].ST_NAM_ChangedAt,
    [NAM].ST_NAM_Stage_Name,
    [LOC].ST_LOC_ST_ID,
    [LOC].Metadata_ST_LOC,
    [LOC].ST_LOC_Stage_Location
FROM
    [dbo].[ST_Stage] [ST]
LEFT JOIN
    [dbo].[ST_NAM_Stage_Name] [NAM]
ON
    [NAM].ST_NAM_ST_ID = [ST].ST_ID
AND
    [NAM].ST_NAM_ChangedAt = (
        SELECT
            max(sub.ST_NAM_ChangedAt)
        FROM
            [dbo].[ST_NAM_Stage_Name] sub
        WHERE
            sub.ST_NAM_ST_ID = [ST].ST_ID
   )
LEFT JOIN
    [dbo].[ST_LOC_Stage_Location] [LOC]
ON
    [LOC].ST_LOC_ST_ID = [ST].ST_ID;
GO
-- Point-in-time perspective ------------------------------------------------------------------------------------------
-- pST_Stage viewed as it was on the given timepoint
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pST_Stage] (
    @changingTimepoint datetime2(7)
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
    [ST].ST_ID,
    [ST].Metadata_ST,
    [NAM].ST_NAM_ST_ID,
    [NAM].Metadata_ST_NAM,
    [NAM].ST_NAM_ChangedAt,
    [NAM].ST_NAM_Stage_Name,
    [LOC].ST_LOC_ST_ID,
    [LOC].Metadata_ST_LOC,
    [LOC].ST_LOC_Stage_Location
FROM
    [dbo].[ST_Stage] [ST]
LEFT JOIN
    [dbo].[rST_NAM_Stage_Name](@changingTimepoint) [NAM]
ON
    [NAM].ST_NAM_ST_ID = [ST].ST_ID
AND
    [NAM].ST_NAM_ChangedAt = (
        SELECT
            max(sub.ST_NAM_ChangedAt)
        FROM
            [dbo].[rST_NAM_Stage_Name](@changingTimepoint) sub
        WHERE
            sub.ST_NAM_ST_ID = [ST].ST_ID
   )
LEFT JOIN
    [dbo].[ST_LOC_Stage_Location] [LOC]
ON
    [LOC].ST_LOC_ST_ID = [ST].ST_ID;
GO
-- Now perspective ----------------------------------------------------------------------------------------------------
-- nST_Stage viewed as it currently is (cannot include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[nST_Stage]
AS
SELECT
    *
FROM
    [dbo].[pST_Stage](sysdatetime());
GO
-- Difference perspective ---------------------------------------------------------------------------------------------
-- dST_Stage showing all differences between the given timepoints and optionally for a subset of attributes
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[dST_Stage] (
    @intervalStart datetime2(7),
    @intervalEnd datetime2(7),
    @selection varchar(max) = null
)
RETURNS TABLE AS RETURN
SELECT
    timepoints.inspectedTimepoint,
    [pST].*
FROM (
    SELECT DISTINCT
        ST_NAM_ChangedAt AS inspectedTimepoint
    FROM
        [dbo].[ST_NAM_Stage_Name]
    WHERE
        (@selection is null OR @selection like '%NAM%')
    AND
        ST_NAM_ChangedAt BETWEEN @intervalStart AND @intervalEnd
) timepoints
CROSS APPLY
    [dbo].[pST_Stage](timepoints.inspectedTimepoint) [pST];
GO
-- Drop perspectives --------------------------------------------------------------------------------------------------
IF Object_ID('dPE_Performance', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[dPE_Performance];
IF Object_ID('nPE_Performance', 'V') IS NOT NULL
DROP VIEW [dbo].[nPE_Performance];
IF Object_ID('pPE_Performance', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[pPE_Performance];
IF Object_ID('lPE_Performance', 'V') IS NOT NULL
DROP VIEW [dbo].[lPE_Performance];
GO
-- Latest perspective -------------------------------------------------------------------------------------------------
-- lPE_Performance viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lPE_Performance] WITH SCHEMABINDING AS
SELECT
    [PE].PE_ID,
    [PE].Metadata_PE,
    [DAT].PE_DAT_PE_ID,
    [DAT].Metadata_PE_DAT,
    [DAT].PE_DAT_Performance_Date,
    [AUD].PE_AUD_PE_ID,
    [AUD].Metadata_PE_AUD,
    [AUD].PE_AUD_Performance_Audience,
    [REV].PE_REV_PE_ID,
    [REV].Metadata_PE_REV,
    [REV].PE_REV_Performance_Revenue
FROM
    [dbo].[PE_Performance] [PE]
LEFT JOIN
    [dbo].[PE_DAT_Performance_Date] [DAT]
ON
    [DAT].PE_DAT_PE_ID = [PE].PE_ID
LEFT JOIN
    [dbo].[PE_AUD_Performance_Audience] [AUD]
ON
    [AUD].PE_AUD_PE_ID = [PE].PE_ID
LEFT JOIN
    [dbo].[PE_REV_Performance_Revenue] [REV]
ON
    [REV].PE_REV_PE_ID = [PE].PE_ID;
GO
-- Point-in-time perspective ------------------------------------------------------------------------------------------
-- pPE_Performance viewed as it was on the given timepoint
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pPE_Performance] (
    @changingTimepoint datetime2(7)
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
    [PE].PE_ID,
    [PE].Metadata_PE,
    [DAT].PE_DAT_PE_ID,
    [DAT].Metadata_PE_DAT,
    [DAT].PE_DAT_Performance_Date,
    [AUD].PE_AUD_PE_ID,
    [AUD].Metadata_PE_AUD,
    [AUD].PE_AUD_Performance_Audience,
    [REV].PE_REV_PE_ID,
    [REV].Metadata_PE_REV,
    [REV].PE_REV_Performance_Revenue
FROM
    [dbo].[PE_Performance] [PE]
LEFT JOIN
    [dbo].[PE_DAT_Performance_Date] [DAT]
ON
    [DAT].PE_DAT_PE_ID = [PE].PE_ID
LEFT JOIN
    [dbo].[PE_AUD_Performance_Audience] [AUD]
ON
    [AUD].PE_AUD_PE_ID = [PE].PE_ID
LEFT JOIN
    [dbo].[PE_REV_Performance_Revenue] [REV]
ON
    [REV].PE_REV_PE_ID = [PE].PE_ID;
GO
-- Now perspective ----------------------------------------------------------------------------------------------------
-- nPE_Performance viewed as it currently is (cannot include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[nPE_Performance]
AS
SELECT
    *
FROM
    [dbo].[pPE_Performance](sysdatetime());
GO
-- Drop perspectives --------------------------------------------------------------------------------------------------
IF Object_ID('dAC_Actor', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[dAC_Actor];
IF Object_ID('nAC_Actor', 'V') IS NOT NULL
DROP VIEW [dbo].[nAC_Actor];
IF Object_ID('pAC_Actor', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[pAC_Actor];
IF Object_ID('lAC_Actor', 'V') IS NOT NULL
DROP VIEW [dbo].[lAC_Actor];
GO
-- Latest perspective -------------------------------------------------------------------------------------------------
-- lAC_Actor viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lAC_Actor] WITH SCHEMABINDING AS
SELECT
    [AC].AC_ID,
    [AC].Metadata_AC,
    [NAM].AC_NAM_AC_ID,
    [NAM].Metadata_AC_NAM,
    [NAM].AC_NAM_ChangedAt,
    [NAM].AC_NAM_Actor_Name,
    [GEN].AC_GEN_AC_ID,
    [GEN].Metadata_AC_GEN,
    [kGEN].GEN_Gender AS AC_GEN_GEN_Gender,
    [kGEN].Metadata_GEN AS AC_GEN_Metadata_GEN,
    [GEN].AC_GEN_GEN_ID,
    [PLV].AC_PLV_AC_ID,
    [PLV].Metadata_AC_PLV,
    [PLV].AC_PLV_ChangedAt,
    [kPLV].PLV_ProfessionalLevel AS AC_PLV_PLV_ProfessionalLevel,
    [kPLV].Metadata_PLV AS AC_PLV_Metadata_PLV,
    [PLV].AC_PLV_PLV_ID
FROM
    [dbo].[AC_Actor] [AC]
LEFT JOIN
    [dbo].[AC_NAM_Actor_Name] [NAM]
ON
    [NAM].AC_NAM_AC_ID = [AC].AC_ID
AND
    [NAM].AC_NAM_ChangedAt = (
        SELECT
            max(sub.AC_NAM_ChangedAt)
        FROM
            [dbo].[AC_NAM_Actor_Name] sub
        WHERE
            sub.AC_NAM_AC_ID = [AC].AC_ID
   )
LEFT JOIN
    [dbo].[AC_GEN_Actor_Gender] [GEN]
ON
    [GEN].AC_GEN_AC_ID = [AC].AC_ID
LEFT JOIN
    [dbo].[GEN_Gender] [kGEN]
ON
    [kGEN].GEN_ID = [GEN].AC_GEN_GEN_ID
LEFT JOIN
    [dbo].[AC_PLV_Actor_ProfessionalLevel] [PLV]
ON
    [PLV].AC_PLV_AC_ID = [AC].AC_ID
AND
    [PLV].AC_PLV_ChangedAt = (
        SELECT
            max(sub.AC_PLV_ChangedAt)
        FROM
            [dbo].[AC_PLV_Actor_ProfessionalLevel] sub
        WHERE
            sub.AC_PLV_AC_ID = [AC].AC_ID
   )
LEFT JOIN
    [dbo].[PLV_ProfessionalLevel] [kPLV]
ON
    [kPLV].PLV_ID = [PLV].AC_PLV_PLV_ID;
GO
-- Point-in-time perspective ------------------------------------------------------------------------------------------
-- pAC_Actor viewed as it was on the given timepoint
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pAC_Actor] (
    @changingTimepoint datetime2(7)
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
    [AC].AC_ID,
    [AC].Metadata_AC,
    [NAM].AC_NAM_AC_ID,
    [NAM].Metadata_AC_NAM,
    [NAM].AC_NAM_ChangedAt,
    [NAM].AC_NAM_Actor_Name,
    [GEN].AC_GEN_AC_ID,
    [GEN].Metadata_AC_GEN,
    [kGEN].GEN_Gender AS AC_GEN_GEN_Gender,
    [kGEN].Metadata_GEN AS AC_GEN_Metadata_GEN,
    [GEN].AC_GEN_GEN_ID,
    [PLV].AC_PLV_AC_ID,
    [PLV].Metadata_AC_PLV,
    [PLV].AC_PLV_ChangedAt,
    [kPLV].PLV_ProfessionalLevel AS AC_PLV_PLV_ProfessionalLevel,
    [kPLV].Metadata_PLV AS AC_PLV_Metadata_PLV,
    [PLV].AC_PLV_PLV_ID
FROM
    [dbo].[AC_Actor] [AC]
LEFT JOIN
    [dbo].[rAC_NAM_Actor_Name](@changingTimepoint) [NAM]
ON
    [NAM].AC_NAM_AC_ID = [AC].AC_ID
AND
    [NAM].AC_NAM_ChangedAt = (
        SELECT
            max(sub.AC_NAM_ChangedAt)
        FROM
            [dbo].[rAC_NAM_Actor_Name](@changingTimepoint) sub
        WHERE
            sub.AC_NAM_AC_ID = [AC].AC_ID
   )
LEFT JOIN
    [dbo].[AC_GEN_Actor_Gender] [GEN]
ON
    [GEN].AC_GEN_AC_ID = [AC].AC_ID
LEFT JOIN
    [dbo].[GEN_Gender] [kGEN]
ON
    [kGEN].GEN_ID = [GEN].AC_GEN_GEN_ID
LEFT JOIN
    [dbo].[rAC_PLV_Actor_ProfessionalLevel](@changingTimepoint) [PLV]
ON
    [PLV].AC_PLV_AC_ID = [AC].AC_ID
AND
    [PLV].AC_PLV_ChangedAt = (
        SELECT
            max(sub.AC_PLV_ChangedAt)
        FROM
            [dbo].[rAC_PLV_Actor_ProfessionalLevel](@changingTimepoint) sub
        WHERE
            sub.AC_PLV_AC_ID = [AC].AC_ID
   )
LEFT JOIN
    [dbo].[PLV_ProfessionalLevel] [kPLV]
ON
    [kPLV].PLV_ID = [PLV].AC_PLV_PLV_ID;
GO
-- Now perspective ----------------------------------------------------------------------------------------------------
-- nAC_Actor viewed as it currently is (cannot include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[nAC_Actor]
AS
SELECT
    *
FROM
    [dbo].[pAC_Actor](sysdatetime());
GO
-- Difference perspective ---------------------------------------------------------------------------------------------
-- dAC_Actor showing all differences between the given timepoints and optionally for a subset of attributes
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[dAC_Actor] (
    @intervalStart datetime2(7),
    @intervalEnd datetime2(7),
    @selection varchar(max) = null
)
RETURNS TABLE AS RETURN
SELECT
    timepoints.inspectedTimepoint,
    [pAC].*
FROM (
    SELECT DISTINCT
        AC_NAM_ChangedAt AS inspectedTimepoint
    FROM
        [dbo].[AC_NAM_Actor_Name]
    WHERE
        (@selection is null OR @selection like '%NAM%')
    AND
        AC_NAM_ChangedAt BETWEEN @intervalStart AND @intervalEnd
    UNION
    SELECT DISTINCT
        AC_PLV_ChangedAt AS inspectedTimepoint
    FROM
        [dbo].[AC_PLV_Actor_ProfessionalLevel]
    WHERE
        (@selection is null OR @selection like '%PLV%')
    AND
        AC_PLV_ChangedAt BETWEEN @intervalStart AND @intervalEnd
) timepoints
CROSS APPLY
    [dbo].[pAC_Actor](timepoints.inspectedTimepoint) [pAC];
GO
-- ANCHOR TRIGGERS ---------------------------------------------------------------------------------------------------
--
-- The following triggers on the latest view make it behave like a table.
-- There are three different 'instead of' triggers: insert, update, and delete.
-- They will ensure that such operations are propagated to the underlying tables
-- in a consistent way. Default values are used for some columns if not provided
-- by the corresponding SQL statements.
--
-- For idempotent attributes, only changes that represent a value different from
-- the previous or following value are stored. Others are silently ignored in
-- order to avoid unnecessary temporal duplicates.
--
-- Insert trigger -----------------------------------------------------------------------------------------------------
-- itPR_Program instead of INSERT trigger on lPR_Program
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[itPR_Program] ON [dbo].[lPR_Program]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @now datetime2(7) = sysdatetime();
    DECLARE @maxVersion int;
    DECLARE @currentVersion int;
    DECLARE @PR TABLE (
        Row bigint IDENTITY(1,1) not null primary key,
        PR_ID int not null
    );
    INSERT INTO [dbo].[PR_Program] (
        Metadata_PR
    )
    OUTPUT
        inserted.PR_ID
    INTO
        @PR
    SELECT
        Metadata_PR
    FROM
        inserted
    WHERE
        inserted.PR_ID is null;
    DECLARE @inserted TABLE (
        PR_ID int not null,
        Metadata_PR int not null,
        PR_NAM_PR_ID int null,
        Metadata_PR_NAM int null,
        PR_NAM_Program_Name varchar(42) null
    );
    INSERT INTO @inserted
    SELECT
        ISNULL(i.PR_ID, a.PR_ID),
        i.Metadata_PR,
        ISNULL(ISNULL(i.PR_NAM_PR_ID, i.PR_ID), a.PR_ID),
        ISNULL(i.Metadata_PR_NAM, i.Metadata_PR),
        i.PR_NAM_Program_Name
    FROM (
        SELECT
            PR_ID,
            Metadata_PR,
            PR_NAM_PR_ID,
            Metadata_PR_NAM,
            PR_NAM_Program_Name,
            ROW_NUMBER() OVER (PARTITION BY PR_ID ORDER BY PR_ID) AS Row
        FROM
            inserted
    ) i
    LEFT JOIN
        @PR a
    ON
        a.Row = i.Row;
    INSERT INTO [dbo].[PR_NAM_Program_Name] (
        PR_NAM_PR_ID,
        Metadata_PR_NAM,
        PR_NAM_Program_Name
    )
    SELECT
        i.PR_NAM_PR_ID,
        i.Metadata_PR_NAM,
        i.PR_NAM_Program_Name
    FROM
        @inserted i
    LEFT JOIN
        [dbo].[PR_NAM_Program_Name] [NAM]
    ON
        [NAM].PR_NAM_PR_ID = i.PR_NAM_PR_ID
    WHERE
        [NAM].PR_NAM_PR_ID is null
    AND
        i.PR_NAM_Program_Name is not null;
END
GO
-- Insert trigger -----------------------------------------------------------------------------------------------------
-- itST_Stage instead of INSERT trigger on lST_Stage
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[itST_Stage] ON [dbo].[lST_Stage]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @now datetime2(7) = sysdatetime();
    DECLARE @maxVersion int;
    DECLARE @currentVersion int;
    DECLARE @ST TABLE (
        Row bigint IDENTITY(1,1) not null primary key,
        ST_ID int not null
    );
    INSERT INTO [dbo].[ST_Stage] (
        Metadata_ST
    )
    OUTPUT
        inserted.ST_ID
    INTO
        @ST
    SELECT
        Metadata_ST
    FROM
        inserted
    WHERE
        inserted.ST_ID is null;
    DECLARE @inserted TABLE (
        ST_ID int not null,
        Metadata_ST int not null,
        ST_NAM_ST_ID int null,
        Metadata_ST_NAM int null,
        ST_NAM_ChangedAt datetime null,
        ST_NAM_Stage_Name varchar(42) null,
        ST_LOC_ST_ID int null,
        Metadata_ST_LOC int null,
        ST_LOC_Stage_Location varchar(42) null
    );
    INSERT INTO @inserted
    SELECT
        ISNULL(i.ST_ID, a.ST_ID),
        i.Metadata_ST,
        ISNULL(ISNULL(i.ST_NAM_ST_ID, i.ST_ID), a.ST_ID),
        ISNULL(i.Metadata_ST_NAM, i.Metadata_ST),
        ISNULL(i.ST_NAM_ChangedAt, @now),
        i.ST_NAM_Stage_Name,
        ISNULL(ISNULL(i.ST_LOC_ST_ID, i.ST_ID), a.ST_ID),
        ISNULL(i.Metadata_ST_LOC, i.Metadata_ST),
        i.ST_LOC_Stage_Location
    FROM (
        SELECT
            ST_ID,
            Metadata_ST,
            ST_NAM_ST_ID,
            Metadata_ST_NAM,
            ST_NAM_ChangedAt,
            ST_NAM_Stage_Name,
            ST_LOC_ST_ID,
            Metadata_ST_LOC,
            ST_LOC_Stage_Location,
            ROW_NUMBER() OVER (PARTITION BY ST_ID ORDER BY ST_ID) AS Row
        FROM
            inserted
    ) i
    LEFT JOIN
        @ST a
    ON
        a.Row = i.Row;
    DECLARE @ST_NAM_Stage_Name TABLE (
        ST_NAM_ST_ID int not null,
        Metadata_ST_NAM int not null,
        ST_NAM_ChangedAt datetime not null,
        ST_NAM_Stage_Name varchar(42) not null,
        ST_NAM_Version bigint not null,
        ST_NAM_StatementType char(1) not null,
        primary key(
            ST_NAM_Version,
            ST_NAM_ST_ID
        )
    );
    INSERT INTO @ST_NAM_Stage_Name
    SELECT
        i.ST_NAM_ST_ID,
        i.Metadata_ST_NAM,
        i.ST_NAM_ChangedAt,
        i.ST_NAM_Stage_Name,
        DENSE_RANK() OVER (PARTITION BY i.ST_NAM_ST_ID ORDER BY i.ST_NAM_ChangedAt),
        'X'
    FROM
        @inserted i
    WHERE
        i.ST_NAM_Stage_Name is not null;
    SELECT
        @maxVersion = max(ST_NAM_Version),
        @currentVersion = 0
    FROM
        @ST_NAM_Stage_Name;
    WHILE (@currentVersion < @maxVersion)
    BEGIN
        SET @currentVersion = @currentVersion + 1;
        UPDATE v
        SET
            v.ST_NAM_StatementType =
                CASE
                    WHEN [NAM].ST_NAM_ST_ID is not null
                    THEN 'D' -- duplicate
                    WHEN [dbo].[rfST_NAM_Stage_Name](
                        v.ST_NAM_ST_ID,
                        v.ST_NAM_Stage_Name,
                        v.ST_NAM_ChangedAt
                    ) = 1
                    THEN 'R' -- restatement
                    ELSE 'N' -- new statement
                END
        FROM
            @ST_NAM_Stage_Name v
        LEFT JOIN
            [dbo].[ST_NAM_Stage_Name] [NAM]
        ON
            [NAM].ST_NAM_ST_ID = v.ST_NAM_ST_ID
        AND
            [NAM].ST_NAM_ChangedAt = v.ST_NAM_ChangedAt
        AND
            [NAM].ST_NAM_Stage_Name = v.ST_NAM_Stage_Name
        WHERE
            v.ST_NAM_Version = @currentVersion;
        INSERT INTO [dbo].[ST_NAM_Stage_Name] (
            ST_NAM_ST_ID,
            Metadata_ST_NAM,
            ST_NAM_ChangedAt,
            ST_NAM_Stage_Name
        )
        SELECT
            ST_NAM_ST_ID,
            Metadata_ST_NAM,
            ST_NAM_ChangedAt,
            ST_NAM_Stage_Name
        FROM
            @ST_NAM_Stage_Name
        WHERE
            ST_NAM_Version = @currentVersion
        AND
            ST_NAM_StatementType in ('N');
    END
    INSERT INTO [dbo].[ST_LOC_Stage_Location] (
        ST_LOC_ST_ID,
        Metadata_ST_LOC,
        ST_LOC_Stage_Location
    )
    SELECT
        i.ST_LOC_ST_ID,
        i.Metadata_ST_LOC,
        i.ST_LOC_Stage_Location
    FROM
        @inserted i
    LEFT JOIN
        [dbo].[ST_LOC_Stage_Location] [LOC]
    ON
        [LOC].ST_LOC_ST_ID = i.ST_LOC_ST_ID
    WHERE
        [LOC].ST_LOC_ST_ID is null
    AND
        i.ST_LOC_Stage_Location is not null;
END
GO
-- UPDATE trigger -----------------------------------------------------------------------------------------------------
-- utST_Stage instead of UPDATE trigger on lST_Stage
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[utST_Stage] ON [dbo].[lST_Stage]
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @now datetime2(7) = sysdatetime();
    IF(UPDATE(ST_ID))
        RAISERROR('The identity column ST_ID is not updatable.', 16, 1);
    IF(UPDATE(ST_NAM_Stage_Name))
    INSERT INTO [dbo].[ST_NAM_Stage_Name] (
        ST_NAM_ST_ID,
        Metadata_ST_NAM,
        ST_NAM_ChangedAt,
        ST_NAM_Stage_Name
    )
    SELECT
        i.ST_NAM_ST_ID,
        CASE WHEN UPDATE(Metadata_ST_NAM) THEN i.Metadata_ST_NAM ELSE 0 END,
        CASE WHEN UPDATE(ST_NAM_ChangedAt) THEN i.ST_NAM_ChangedAt ELSE @now END,
        i.ST_NAM_Stage_Name
    FROM
        inserted i
    LEFT JOIN
        [dbo].[ST_NAM_Stage_Name] b
    ON
        b.ST_NAM_ST_ID = i.ST_NAM_ST_ID
    AND
        b.ST_NAM_Stage_Name = i.ST_NAM_Stage_Name
    AND
        b.ST_NAM_ChangedAt = i.ST_NAM_ChangedAt
    WHERE
        b.ST_NAM_ST_ID is null
    AND
        [dbo].[rfST_NAM_Stage_Name](
            i.ST_NAM_ST_ID,
            i.ST_NAM_Stage_Name,
            i.ST_NAM_ChangedAt
        ) = 0;
END
GO
-- DELETE trigger -----------------------------------------------------------------------------------------------------
-- dtST_Stage instead of DELETE trigger on lST_Stage
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[dtST_Stage] ON [dbo].[lST_Stage]
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE [NAM]
    FROM
        [dbo].[ST_NAM_Stage_Name] [NAM]
    JOIN
        deleted d
    ON
        d.ST_NAM_ST_ID = [NAM].ST_NAM_ST_ID
    AND
        d.ST_NAM_ChangedAt = [NAM].ST_NAM_ChangedAt;
    DELETE [LOC]
    FROM
        [dbo].[ST_LOC_Stage_Location] [LOC]
    JOIN
        deleted d
    ON
        d.ST_LOC_ST_ID = [LOC].ST_LOC_ST_ID
    DELETE [ST]
    FROM
        [dbo].[ST_Stage] [ST]
    LEFT JOIN
        [dbo].[ST_NAM_Stage_Name] [NAM]
    ON
        [NAM].ST_NAM_ST_ID = [ST].ST_ID
    LEFT JOIN
        [dbo].[ST_LOC_Stage_Location] [LOC]
    ON
        [LOC].ST_LOC_ST_ID = [ST].ST_ID
    WHERE
        [NAM].ST_NAM_ST_ID is null
    AND
        [LOC].ST_LOC_ST_ID is null;
END
GO
-- Insert trigger -----------------------------------------------------------------------------------------------------
-- itPE_Performance instead of INSERT trigger on lPE_Performance
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[itPE_Performance] ON [dbo].[lPE_Performance]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @now datetime2(7) = sysdatetime();
    DECLARE @maxVersion int;
    DECLARE @currentVersion int;
    DECLARE @PE TABLE (
        Row bigint IDENTITY(1,1) not null primary key,
        PE_ID int not null
    );
    INSERT INTO [dbo].[PE_Performance] (
        Metadata_PE
    )
    OUTPUT
        inserted.PE_ID
    INTO
        @PE
    SELECT
        Metadata_PE
    FROM
        inserted
    WHERE
        inserted.PE_ID is null;
    DECLARE @inserted TABLE (
        PE_ID int not null,
        Metadata_PE int not null,
        PE_DAT_PE_ID int null,
        Metadata_PE_DAT int null,
        PE_DAT_Performance_Date datetime null,
        PE_AUD_PE_ID int null,
        Metadata_PE_AUD int null,
        PE_AUD_Performance_Audience int null,
        PE_REV_PE_ID int null,
        Metadata_PE_REV int null,
        PE_REV_Performance_Revenue money null
    );
    INSERT INTO @inserted
    SELECT
        ISNULL(i.PE_ID, a.PE_ID),
        i.Metadata_PE,
        ISNULL(ISNULL(i.PE_DAT_PE_ID, i.PE_ID), a.PE_ID),
        ISNULL(i.Metadata_PE_DAT, i.Metadata_PE),
        i.PE_DAT_Performance_Date,
        ISNULL(ISNULL(i.PE_AUD_PE_ID, i.PE_ID), a.PE_ID),
        ISNULL(i.Metadata_PE_AUD, i.Metadata_PE),
        i.PE_AUD_Performance_Audience,
        ISNULL(ISNULL(i.PE_REV_PE_ID, i.PE_ID), a.PE_ID),
        ISNULL(i.Metadata_PE_REV, i.Metadata_PE),
        i.PE_REV_Performance_Revenue
    FROM (
        SELECT
            PE_ID,
            Metadata_PE,
            PE_DAT_PE_ID,
            Metadata_PE_DAT,
            PE_DAT_Performance_Date,
            PE_AUD_PE_ID,
            Metadata_PE_AUD,
            PE_AUD_Performance_Audience,
            PE_REV_PE_ID,
            Metadata_PE_REV,
            PE_REV_Performance_Revenue,
            ROW_NUMBER() OVER (PARTITION BY PE_ID ORDER BY PE_ID) AS Row
        FROM
            inserted
    ) i
    LEFT JOIN
        @PE a
    ON
        a.Row = i.Row;
    INSERT INTO [dbo].[PE_DAT_Performance_Date] (
        PE_DAT_PE_ID,
        Metadata_PE_DAT,
        PE_DAT_Performance_Date
    )
    SELECT
        i.PE_DAT_PE_ID,
        i.Metadata_PE_DAT,
        i.PE_DAT_Performance_Date
    FROM
        @inserted i
    LEFT JOIN
        [dbo].[PE_DAT_Performance_Date] [DAT]
    ON
        [DAT].PE_DAT_PE_ID = i.PE_DAT_PE_ID
    WHERE
        [DAT].PE_DAT_PE_ID is null
    AND
        i.PE_DAT_Performance_Date is not null;
    INSERT INTO [dbo].[PE_AUD_Performance_Audience] (
        PE_AUD_PE_ID,
        Metadata_PE_AUD,
        PE_AUD_Performance_Audience
    )
    SELECT
        i.PE_AUD_PE_ID,
        i.Metadata_PE_AUD,
        i.PE_AUD_Performance_Audience
    FROM
        @inserted i
    LEFT JOIN
        [dbo].[PE_AUD_Performance_Audience] [AUD]
    ON
        [AUD].PE_AUD_PE_ID = i.PE_AUD_PE_ID
    WHERE
        [AUD].PE_AUD_PE_ID is null
    AND
        i.PE_AUD_Performance_Audience is not null;
    INSERT INTO [dbo].[PE_REV_Performance_Revenue] (
        PE_REV_PE_ID,
        Metadata_PE_REV,
        PE_REV_Performance_Revenue
    )
    SELECT
        i.PE_REV_PE_ID,
        i.Metadata_PE_REV,
        i.PE_REV_Performance_Revenue
    FROM
        @inserted i
    LEFT JOIN
        [dbo].[PE_REV_Performance_Revenue] [REV]
    ON
        [REV].PE_REV_PE_ID = i.PE_REV_PE_ID
    WHERE
        [REV].PE_REV_PE_ID is null
    AND
        i.PE_REV_Performance_Revenue is not null;
END
GO
-- Insert trigger -----------------------------------------------------------------------------------------------------
-- itAC_Actor instead of INSERT trigger on lAC_Actor
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[itAC_Actor] ON [dbo].[lAC_Actor]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @now datetime2(7) = sysdatetime();
    DECLARE @maxVersion int;
    DECLARE @currentVersion int;
    DECLARE @AC TABLE (
        Row bigint IDENTITY(1,1) not null primary key,
        AC_ID int not null
    );
    INSERT INTO [dbo].[AC_Actor] (
        Metadata_AC
    )
    OUTPUT
        inserted.AC_ID
    INTO
        @AC
    SELECT
        Metadata_AC
    FROM
        inserted
    WHERE
        inserted.AC_ID is null;
    DECLARE @inserted TABLE (
        AC_ID int not null,
        Metadata_AC int not null,
        AC_NAM_AC_ID int null,
        Metadata_AC_NAM int null,
        AC_NAM_ChangedAt datetime null,
        AC_NAM_Actor_Name varchar(42) null,
        AC_GEN_AC_ID int null,
        Metadata_AC_GEN int null,
        AC_GEN_GEN_Gender varchar(42) null,
        AC_GEN_Metadata_GEN int null,
        AC_GEN_GEN_ID bit null,
        AC_PLV_AC_ID int null,
        Metadata_AC_PLV int null,
        AC_PLV_ChangedAt datetime null,
        AC_PLV_PLV_ProfessionalLevel varchar(42) null,
        AC_PLV_Metadata_PLV int null,
        AC_PLV_PLV_ID tinyint null
    );
    INSERT INTO @inserted
    SELECT
        ISNULL(i.AC_ID, a.AC_ID),
        i.Metadata_AC,
        ISNULL(ISNULL(i.AC_NAM_AC_ID, i.AC_ID), a.AC_ID),
        ISNULL(i.Metadata_AC_NAM, i.Metadata_AC),
        ISNULL(i.AC_NAM_ChangedAt, @now),
        i.AC_NAM_Actor_Name,
        ISNULL(ISNULL(i.AC_GEN_AC_ID, i.AC_ID), a.AC_ID),
        ISNULL(i.Metadata_AC_GEN, i.Metadata_AC),
        i.AC_GEN_GEN_Gender,
        ISNULL(i.AC_GEN_Metadata_GEN, i.Metadata_AC),
        i.AC_GEN_GEN_ID,
        ISNULL(ISNULL(i.AC_PLV_AC_ID, i.AC_ID), a.AC_ID),
        ISNULL(i.Metadata_AC_PLV, i.Metadata_AC),
        ISNULL(i.AC_PLV_ChangedAt, @now),
        i.AC_PLV_PLV_ProfessionalLevel,
        ISNULL(i.AC_PLV_Metadata_PLV, i.Metadata_AC),
        i.AC_PLV_PLV_ID
    FROM (
        SELECT
            AC_ID,
            Metadata_AC,
            AC_NAM_AC_ID,
            Metadata_AC_NAM,
            AC_NAM_ChangedAt,
            AC_NAM_Actor_Name,
            AC_GEN_AC_ID,
            Metadata_AC_GEN,
            AC_GEN_GEN_Gender,
            AC_GEN_Metadata_GEN,
            AC_GEN_GEN_ID,
            AC_PLV_AC_ID,
            Metadata_AC_PLV,
            AC_PLV_ChangedAt,
            AC_PLV_PLV_ProfessionalLevel,
            AC_PLV_Metadata_PLV,
            AC_PLV_PLV_ID,
            ROW_NUMBER() OVER (PARTITION BY AC_ID ORDER BY AC_ID) AS Row
        FROM
            inserted
    ) i
    LEFT JOIN
        @AC a
    ON
        a.Row = i.Row;
    DECLARE @AC_NAM_Actor_Name TABLE (
        AC_NAM_AC_ID int not null,
        Metadata_AC_NAM int not null,
        AC_NAM_ChangedAt datetime not null,
        AC_NAM_Actor_Name varchar(42) not null,
        AC_NAM_Version bigint not null,
        AC_NAM_StatementType char(1) not null,
        primary key(
            AC_NAM_Version,
            AC_NAM_AC_ID
        )
    );
    INSERT INTO @AC_NAM_Actor_Name
    SELECT
        i.AC_NAM_AC_ID,
        i.Metadata_AC_NAM,
        i.AC_NAM_ChangedAt,
        i.AC_NAM_Actor_Name,
        DENSE_RANK() OVER (PARTITION BY i.AC_NAM_AC_ID ORDER BY i.AC_NAM_ChangedAt),
        'X'
    FROM
        @inserted i
    WHERE
        i.AC_NAM_Actor_Name is not null;
    SELECT
        @maxVersion = max(AC_NAM_Version),
        @currentVersion = 0
    FROM
        @AC_NAM_Actor_Name;
    WHILE (@currentVersion < @maxVersion)
    BEGIN
        SET @currentVersion = @currentVersion + 1;
        UPDATE v
        SET
            v.AC_NAM_StatementType =
                CASE
                    WHEN [NAM].AC_NAM_AC_ID is not null
                    THEN 'D' -- duplicate
                    WHEN [dbo].[rfAC_NAM_Actor_Name](
                        v.AC_NAM_AC_ID,
                        v.AC_NAM_Actor_Name,
                        v.AC_NAM_ChangedAt
                    ) = 1
                    THEN 'R' -- restatement
                    ELSE 'N' -- new statement
                END
        FROM
            @AC_NAM_Actor_Name v
        LEFT JOIN
            [dbo].[AC_NAM_Actor_Name] [NAM]
        ON
            [NAM].AC_NAM_AC_ID = v.AC_NAM_AC_ID
        AND
            [NAM].AC_NAM_ChangedAt = v.AC_NAM_ChangedAt
        AND
            [NAM].AC_NAM_Actor_Name = v.AC_NAM_Actor_Name
        WHERE
            v.AC_NAM_Version = @currentVersion;
        INSERT INTO [dbo].[AC_NAM_Actor_Name] (
            AC_NAM_AC_ID,
            Metadata_AC_NAM,
            AC_NAM_ChangedAt,
            AC_NAM_Actor_Name
        )
        SELECT
            AC_NAM_AC_ID,
            Metadata_AC_NAM,
            AC_NAM_ChangedAt,
            AC_NAM_Actor_Name
        FROM
            @AC_NAM_Actor_Name
        WHERE
            AC_NAM_Version = @currentVersion
        AND
            AC_NAM_StatementType in ('N');
    END
    INSERT INTO [dbo].[AC_GEN_Actor_Gender] (
        AC_GEN_AC_ID,
        Metadata_AC_GEN,
        AC_GEN_GEN_ID
    )
    SELECT
        i.AC_GEN_AC_ID,
        i.Metadata_AC_GEN,
        ISNULL(i.AC_GEN_GEN_ID, [kGEN].GEN_ID)
    FROM
        @inserted i
    LEFT JOIN
        [dbo].[AC_GEN_Actor_Gender] [GEN]
    ON
        [GEN].AC_GEN_AC_ID = i.AC_GEN_AC_ID
    LEFT JOIN
        [dbo].[GEN_Gender] [kGEN]
    ON
        [kGEN].GEN_Gender = i.AC_GEN_GEN_Gender
    WHERE
        ISNULL(i.AC_GEN_GEN_ID, [kGEN].GEN_ID) is not null
    AND
        [GEN].AC_GEN_AC_ID is null
    AND
        ISNULL(i.AC_GEN_GEN_ID, [kGEN].GEN_ID) is not null;
    DECLARE @AC_PLV_Actor_ProfessionalLevel TABLE (
        AC_PLV_AC_ID int not null,
        Metadata_AC_PLV int not null,
        AC_PLV_ChangedAt datetime not null,
        AC_PLV_PLV_ID tinyint not null,
        AC_PLV_Version bigint not null,
        AC_PLV_StatementType char(1) not null,
        primary key(
            AC_PLV_Version,
            AC_PLV_AC_ID
        )
    );
    INSERT INTO @AC_PLV_Actor_ProfessionalLevel
    SELECT
        i.AC_PLV_AC_ID,
        i.Metadata_AC_PLV,
        i.AC_PLV_ChangedAt,
        ISNULL(i.AC_PLV_PLV_ID, [kPLV].PLV_ID),
        DENSE_RANK() OVER (PARTITION BY i.AC_PLV_AC_ID ORDER BY i.AC_PLV_ChangedAt),
        'X'
    FROM
        @inserted i
    LEFT JOIN
        [dbo].[PLV_ProfessionalLevel] [kPLV]
    ON
        [kPLV].PLV_ProfessionalLevel = i.AC_PLV_PLV_ProfessionalLevel
    WHERE
        ISNULL(i.AC_PLV_PLV_ID, [kPLV].PLV_ID) is not null;
    SELECT
        @maxVersion = max(AC_PLV_Version),
        @currentVersion = 0
    FROM
        @AC_PLV_Actor_ProfessionalLevel;
    WHILE (@currentVersion < @maxVersion)
    BEGIN
        SET @currentVersion = @currentVersion + 1;
        UPDATE v
        SET
            v.AC_PLV_StatementType =
                CASE
                    WHEN [PLV].AC_PLV_AC_ID is not null
                    THEN 'D' -- duplicate
                    WHEN [dbo].[rfAC_PLV_Actor_ProfessionalLevel](
                        v.AC_PLV_AC_ID,
                        v.AC_PLV_PLV_ID,
                        v.AC_PLV_ChangedAt
                    ) = 1
                    THEN 'R' -- restatement
                    ELSE 'N' -- new statement
                END
        FROM
            @AC_PLV_Actor_ProfessionalLevel v
        LEFT JOIN
            [dbo].[AC_PLV_Actor_ProfessionalLevel] [PLV]
        ON
            [PLV].AC_PLV_AC_ID = v.AC_PLV_AC_ID
        AND
            [PLV].AC_PLV_ChangedAt = v.AC_PLV_ChangedAt
        AND
            [PLV].AC_PLV_PLV_ID = v.AC_PLV_PLV_ID
        WHERE
            v.AC_PLV_Version = @currentVersion;
        INSERT INTO [dbo].[AC_PLV_Actor_ProfessionalLevel] (
            AC_PLV_AC_ID,
            Metadata_AC_PLV,
            AC_PLV_ChangedAt,
            AC_PLV_PLV_ID
        )
        SELECT
            AC_PLV_AC_ID,
            Metadata_AC_PLV,
            AC_PLV_ChangedAt,
            AC_PLV_PLV_ID
        FROM
            @AC_PLV_Actor_ProfessionalLevel
        WHERE
            AC_PLV_Version = @currentVersion
        AND
            AC_PLV_StatementType in ('N');
    END
END
GO
-- UPDATE trigger -----------------------------------------------------------------------------------------------------
-- utAC_Actor instead of UPDATE trigger on lAC_Actor
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[utAC_Actor] ON [dbo].[lAC_Actor]
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @now datetime2(7) = sysdatetime();
    IF(UPDATE(AC_ID))
        RAISERROR('The identity column AC_ID is not updatable.', 16, 1);
    IF(UPDATE(AC_NAM_Actor_Name))
    INSERT INTO [dbo].[AC_NAM_Actor_Name] (
        AC_NAM_AC_ID,
        Metadata_AC_NAM,
        AC_NAM_ChangedAt,
        AC_NAM_Actor_Name
    )
    SELECT
        i.AC_NAM_AC_ID,
        CASE WHEN UPDATE(Metadata_AC_NAM) THEN i.Metadata_AC_NAM ELSE 0 END,
        CASE WHEN UPDATE(AC_NAM_ChangedAt) THEN i.AC_NAM_ChangedAt ELSE @now END,
        i.AC_NAM_Actor_Name
    FROM
        inserted i
    LEFT JOIN
        [dbo].[AC_NAM_Actor_Name] b
    ON
        b.AC_NAM_AC_ID = i.AC_NAM_AC_ID
    AND
        b.AC_NAM_Actor_Name = i.AC_NAM_Actor_Name
    AND
        b.AC_NAM_ChangedAt = i.AC_NAM_ChangedAt
    WHERE
        b.AC_NAM_AC_ID is null
    AND
        [dbo].[rfAC_NAM_Actor_Name](
            i.AC_NAM_AC_ID,
            i.AC_NAM_Actor_Name,
            i.AC_NAM_ChangedAt
        ) = 0;
    IF(UPDATE(AC_PLV_PLV_ID) OR UPDATE(AC_PLV_PLV_ProfessionalLevel))
    INSERT INTO [dbo].[AC_PLV_Actor_ProfessionalLevel] (
        AC_PLV_AC_ID,
        Metadata_AC_PLV,
        AC_PLV_ChangedAt,
        AC_PLV_PLV_ID
    )
    SELECT
        i.AC_PLV_AC_ID,
        CASE WHEN UPDATE(Metadata_AC_PLV) THEN i.Metadata_AC_PLV ELSE 0 END,
        CASE WHEN UPDATE(AC_PLV_ChangedAt) THEN i.AC_PLV_ChangedAt ELSE @now END,
        CASE WHEN UPDATE(AC_PLV_PLV_ID) THEN i.AC_PLV_PLV_ID ELSE [kPLV].PLV_ID END
    FROM
        inserted i
    LEFT JOIN
        [dbo].[PLV_ProfessionalLevel] [kPLV]
    ON
        [kPLV].PLV_ProfessionalLevel = i.AC_PLV_PLV_ProfessionalLevel
    LEFT JOIN
        [dbo].[AC_PLV_Actor_ProfessionalLevel] b
    ON
        b.AC_PLV_AC_ID = i.AC_PLV_AC_ID
    AND
        b.AC_PLV_PLV_ID = CASE WHEN UPDATE(AC_PLV_PLV_ID) THEN i.AC_PLV_PLV_ID ELSE [kPLV].PLV_ID END
    AND
        b.AC_PLV_ChangedAt = i.AC_PLV_ChangedAt
    WHERE
        b.AC_PLV_AC_ID is null
    AND
        [dbo].[rfAC_PLV_Actor_ProfessionalLevel](
            i.AC_PLV_AC_ID,
            i.AC_PLV_PLV_ID,
            i.AC_PLV_ChangedAt
        ) = 0;
END
GO
-- DELETE trigger -----------------------------------------------------------------------------------------------------
-- dtAC_Actor instead of DELETE trigger on lAC_Actor
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[dtAC_Actor] ON [dbo].[lAC_Actor]
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE [NAM]
    FROM
        [dbo].[AC_NAM_Actor_Name] [NAM]
    JOIN
        deleted d
    ON
        d.AC_NAM_AC_ID = [NAM].AC_NAM_AC_ID
    AND
        d.AC_NAM_ChangedAt = [NAM].AC_NAM_ChangedAt;
    DELETE [GEN]
    FROM
        [dbo].[AC_GEN_Actor_Gender] [GEN]
    JOIN
        deleted d
    ON
        d.AC_GEN_AC_ID = [GEN].AC_GEN_AC_ID
    DELETE [PLV]
    FROM
        [dbo].[AC_PLV_Actor_ProfessionalLevel] [PLV]
    JOIN
        deleted d
    ON
        d.AC_PLV_AC_ID = [PLV].AC_PLV_AC_ID
    AND
        d.AC_PLV_ChangedAt = [PLV].AC_PLV_ChangedAt;
    DELETE [AC]
    FROM
        [dbo].[AC_Actor] [AC]
    LEFT JOIN
        [dbo].[AC_NAM_Actor_Name] [NAM]
    ON
        [NAM].AC_NAM_AC_ID = [AC].AC_ID
    LEFT JOIN
        [dbo].[AC_GEN_Actor_Gender] [GEN]
    ON
        [GEN].AC_GEN_AC_ID = [AC].AC_ID
    LEFT JOIN
        [dbo].[AC_PLV_Actor_ProfessionalLevel] [PLV]
    ON
        [PLV].AC_PLV_AC_ID = [AC].AC_ID
    WHERE
        [NAM].AC_NAM_AC_ID is null
    AND
        [GEN].AC_GEN_AC_ID is null
    AND
        [PLV].AC_PLV_AC_ID is null;
END
GO
-- TIE RESTATEMENT CONSTRAINTS ----------------------------------------------------------------------------------------
--
-- Ties may be prevented from storing restatements.
-- A restatement is when the same (non-key) values occurs for two adjacent points
-- in changing time.
--
-- returns 1 for one or two equal surrounding values, 0 for different surrounding values
--
-- Restatement Finder Function and Constraint -------------------------------------------------------------------------
-- rfAC_exclusive_AC_with restatement finder
--
-- @AC_ID_exclusive non-key value
-- @AC_ID_with non-key value
-- @changed the point in time from which this value shall represent a change
--
-- rcAC_exclusive_AC_with restatement constraint
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('rfAC_exclusive_AC_with', 'FN') IS NULL
BEGIN
    EXEC('
    CREATE FUNCTION [dbo].[rfAC_exclusive_AC_with] (
        @AC_ID_exclusive int,
        @AC_ID_with int,
        @changed datetime
    )
    RETURNS tinyint AS
    BEGIN RETURN (
        SELECT
            COUNT(*)
        FROM (
            SELECT TOP 1
                pre.AC_ID_exclusive,
                pre.AC_ID_with
            FROM
                [dbo].[AC_exclusive_AC_with] pre
            WHERE
            (
                    pre.AC_ID_exclusive = @AC_ID_exclusive
                OR
                    pre.AC_ID_with = @AC_ID_with
            )
            AND
                pre.AC_exclusive_AC_with_ChangedAt < @changed
            ORDER BY
                pre.AC_exclusive_AC_with_ChangedAt DESC
            UNION
            SELECT TOP 1
                fol.AC_ID_exclusive,
                fol.AC_ID_with
            FROM
                [dbo].[AC_exclusive_AC_with] fol
            WHERE
            (
                    fol.AC_ID_exclusive = @AC_ID_exclusive
                OR
                    fol.AC_ID_with = @AC_ID_with
            )
            AND
                fol.AC_exclusive_AC_with_ChangedAt > @changed
            ORDER BY
                fol.AC_exclusive_AC_with_ChangedAt ASC
        ) s
        WHERE
            s.AC_ID_exclusive = @AC_ID_exclusive
        AND
            s.AC_ID_with = @AC_ID_with
    );
    END
    ');
    ALTER TABLE [dbo].[AC_exclusive_AC_with]
    ADD CONSTRAINT [rcAC_exclusive_AC_with] CHECK (
        [dbo].[rfAC_exclusive_AC_with] (
            AC_ID_exclusive,
            AC_ID_with,
            AC_exclusive_AC_with_ChangedAt
        ) = 0
    );
END
GO
-- Restatement Finder Function and Constraint -------------------------------------------------------------------------
-- rfAC_part_PR_in_RAT_got restatement finder
--
-- @AC_ID_part primary key component
-- @PR_ID_in primary key component
-- @RAT_ID_got non-key value
-- @changed the point in time from which this value shall represent a change
--
-- rcAC_part_PR_in_RAT_got restatement constraint
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('rfAC_part_PR_in_RAT_got', 'FN') IS NULL
BEGIN
    EXEC('
    CREATE FUNCTION [dbo].[rfAC_part_PR_in_RAT_got] (
        @AC_ID_part int,
        @PR_ID_in int,
        @RAT_ID_got tinyint,
        @changed datetime
    )
    RETURNS tinyint AS
    BEGIN RETURN (
        SELECT
            COUNT(*)
        FROM (
            SELECT TOP 1
                pre.RAT_ID_got
            FROM
                [dbo].[AC_part_PR_in_RAT_got] pre
            WHERE
                pre.AC_ID_part = @AC_ID_part
            AND
                pre.PR_ID_in = @PR_ID_in
            AND
                pre.AC_part_PR_in_RAT_got_ChangedAt < @changed
            ORDER BY
                pre.AC_part_PR_in_RAT_got_ChangedAt DESC
            UNION
            SELECT TOP 1
                fol.RAT_ID_got
            FROM
                [dbo].[AC_part_PR_in_RAT_got] fol
            WHERE
                fol.AC_ID_part = @AC_ID_part
            AND
                fol.PR_ID_in = @PR_ID_in
            AND
                fol.AC_part_PR_in_RAT_got_ChangedAt > @changed
            ORDER BY
                fol.AC_part_PR_in_RAT_got_ChangedAt ASC
        ) s
        WHERE
            s.RAT_ID_got = @RAT_ID_got
    );
    END
    ');
    ALTER TABLE [dbo].[AC_part_PR_in_RAT_got]
    ADD CONSTRAINT [rcAC_part_PR_in_RAT_got] CHECK (
        [dbo].[rfAC_part_PR_in_RAT_got] (
            AC_ID_part,
            PR_ID_in,
            RAT_ID_got,
            AC_part_PR_in_RAT_got_ChangedAt
        ) = 0
    );
END
GO
-- TIE TEMPORAL PERSPECTIVES ------------------------------------------------------------------------------------------
--
-- These table valued functions simplify temporal querying by providing a temporal
-- perspective of each tie. There are four types of perspectives: latest,
-- point-in-time, difference, and now.
--
-- The latest perspective shows the latest available information for each tie.
-- The now perspective shows the information as it is right now.
-- The point-in-time perspective lets you travel through the information to the given timepoint.
--
-- @changingTimepoint the point in changing time to travel to
--
-- The difference perspective shows changes between the two given timepoints.
--
-- @intervalStart the start of the interval for finding changes
-- @intervalEnd the end of the interval for finding changes
--
-- Drop perspectives --------------------------------------------------------------------------------------------------
IF Object_ID('dAC_parent_AC_child_PAT_having', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[dAC_parent_AC_child_PAT_having];
IF Object_ID('nAC_parent_AC_child_PAT_having', 'V') IS NOT NULL
DROP VIEW [dbo].[nAC_parent_AC_child_PAT_having];
IF Object_ID('pAC_parent_AC_child_PAT_having', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[pAC_parent_AC_child_PAT_having];
IF Object_ID('lAC_parent_AC_child_PAT_having', 'V') IS NOT NULL
DROP VIEW [dbo].[lAC_parent_AC_child_PAT_having];
GO
-- Latest perspective -------------------------------------------------------------------------------------------------
-- lAC_parent_AC_child_PAT_having viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lAC_parent_AC_child_PAT_having] WITH SCHEMABINDING AS
SELECT
    tie.Metadata_AC_parent_AC_child_PAT_having,
    tie.AC_ID_parent,
    tie.AC_ID_child,
    [PAT_having].PAT_ParentalType AS having_PAT_ParentalType,
    [PAT_having].Metadata_PAT AS having_Metadata_PAT,
    tie.PAT_ID_having
FROM
    [dbo].[AC_parent_AC_child_PAT_having] tie
LEFT JOIN
    [dbo].[PAT_ParentalType] [PAT_having]
ON
    [PAT_having].PAT_ID = tie.PAT_ID_having;
GO
-- Point-in-time perspective ------------------------------------------------------------------------------------------
-- pAC_parent_AC_child_PAT_having viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pAC_parent_AC_child_PAT_having] (
    @changingTimepoint datetime2(7)
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
    tie.Metadata_AC_parent_AC_child_PAT_having,
    tie.AC_ID_parent,
    tie.AC_ID_child,
    [PAT_having].PAT_ParentalType AS having_PAT_ParentalType,
    [PAT_having].Metadata_PAT AS having_Metadata_PAT,
    tie.PAT_ID_having
FROM
    [dbo].[AC_parent_AC_child_PAT_having] tie
LEFT JOIN
    [dbo].[PAT_ParentalType] [PAT_having]
ON
    [PAT_having].PAT_ID = tie.PAT_ID_having;
GO
-- Now perspective ----------------------------------------------------------------------------------------------------
-- nAC_parent_AC_child_PAT_having viewed as it currently is (cannot include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[nAC_parent_AC_child_PAT_having]
AS
SELECT
    *
FROM
    [dbo].[pAC_parent_AC_child_PAT_having](sysdatetime());
GO
-- Drop perspectives --------------------------------------------------------------------------------------------------
IF Object_ID('dST_atLocation_PR_isPlaying', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[dST_atLocation_PR_isPlaying];
IF Object_ID('nST_atLocation_PR_isPlaying', 'V') IS NOT NULL
DROP VIEW [dbo].[nST_atLocation_PR_isPlaying];
IF Object_ID('pST_atLocation_PR_isPlaying', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[pST_atLocation_PR_isPlaying];
IF Object_ID('lST_atLocation_PR_isPlaying', 'V') IS NOT NULL
DROP VIEW [dbo].[lST_atLocation_PR_isPlaying];
GO
-- Latest perspective -------------------------------------------------------------------------------------------------
-- lST_atLocation_PR_isPlaying viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lST_atLocation_PR_isPlaying] WITH SCHEMABINDING AS
SELECT
    tie.Metadata_ST_atLocation_PR_isPlaying,
    tie.ST_atLocation_PR_isPlaying_ChangedAt,
    tie.ST_ID_atLocation,
    tie.PR_ID_isPlaying
FROM
    [dbo].[ST_atLocation_PR_isPlaying] tie
WHERE
    tie.ST_atLocation_PR_isPlaying_ChangedAt = (
        SELECT
            max(sub.ST_atLocation_PR_isPlaying_ChangedAt)
        FROM
            [dbo].[ST_atLocation_PR_isPlaying] sub
        WHERE
            sub.ST_ID_atLocation = tie.ST_ID_atLocation
        AND
            sub.PR_ID_isPlaying = tie.PR_ID_isPlaying
   );
GO
-- Point-in-time perspective ------------------------------------------------------------------------------------------
-- pST_atLocation_PR_isPlaying viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pST_atLocation_PR_isPlaying] (
    @changingTimepoint datetime2(7)
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
    tie.Metadata_ST_atLocation_PR_isPlaying,
    tie.ST_atLocation_PR_isPlaying_ChangedAt,
    tie.ST_ID_atLocation,
    tie.PR_ID_isPlaying
FROM
    [dbo].[ST_atLocation_PR_isPlaying] tie
WHERE
    tie.ST_atLocation_PR_isPlaying_ChangedAt = (
        SELECT
            max(sub.ST_atLocation_PR_isPlaying_ChangedAt)
        FROM
            [dbo].[ST_atLocation_PR_isPlaying] sub
        WHERE
            sub.ST_ID_atLocation = tie.ST_ID_atLocation
        AND
            sub.PR_ID_isPlaying = tie.PR_ID_isPlaying
        AND
            tie.ST_atLocation_PR_isPlaying_ChangedAt <= @changingTimepoint
   );
GO
-- Now perspective ----------------------------------------------------------------------------------------------------
-- nST_atLocation_PR_isPlaying viewed as it currently is (cannot include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[nST_atLocation_PR_isPlaying]
AS
SELECT
    *
FROM
    [dbo].[pST_atLocation_PR_isPlaying](sysdatetime());
GO
-- Difference perspective ---------------------------------------------------------------------------------------------
-- dST_atLocation_PR_isPlaying showing all differences between the given timepoints
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[dST_atLocation_PR_isPlaying] (
    @intervalStart datetime2(7),
    @intervalEnd datetime2(7)
)
RETURNS TABLE AS RETURN
SELECT
    tie.Metadata_ST_atLocation_PR_isPlaying,
    tie.ST_atLocation_PR_isPlaying_ChangedAt,
    tie.ST_ID_atLocation,
    tie.PR_ID_isPlaying
FROM
    [dbo].[ST_atLocation_PR_isPlaying] tie
WHERE
    tie.ST_atLocation_PR_isPlaying_ChangedAt BETWEEN @intervalStart AND @intervalEnd;
GO
-- Drop perspectives --------------------------------------------------------------------------------------------------
IF Object_ID('dPE_at_PR_wasPlayed', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[dPE_at_PR_wasPlayed];
IF Object_ID('nPE_at_PR_wasPlayed', 'V') IS NOT NULL
DROP VIEW [dbo].[nPE_at_PR_wasPlayed];
IF Object_ID('pPE_at_PR_wasPlayed', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[pPE_at_PR_wasPlayed];
IF Object_ID('lPE_at_PR_wasPlayed', 'V') IS NOT NULL
DROP VIEW [dbo].[lPE_at_PR_wasPlayed];
GO
-- Latest perspective -------------------------------------------------------------------------------------------------
-- lPE_at_PR_wasPlayed viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lPE_at_PR_wasPlayed] WITH SCHEMABINDING AS
SELECT
    tie.Metadata_PE_at_PR_wasPlayed,
    tie.PE_ID_at,
    tie.PR_ID_wasPlayed
FROM
    [dbo].[PE_at_PR_wasPlayed] tie;
GO
-- Point-in-time perspective ------------------------------------------------------------------------------------------
-- pPE_at_PR_wasPlayed viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pPE_at_PR_wasPlayed] (
    @changingTimepoint datetime2(7)
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
    tie.Metadata_PE_at_PR_wasPlayed,
    tie.PE_ID_at,
    tie.PR_ID_wasPlayed
FROM
    [dbo].[PE_at_PR_wasPlayed] tie;
GO
-- Now perspective ----------------------------------------------------------------------------------------------------
-- nPE_at_PR_wasPlayed viewed as it currently is (cannot include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[nPE_at_PR_wasPlayed]
AS
SELECT
    *
FROM
    [dbo].[pPE_at_PR_wasPlayed](sysdatetime());
GO
-- Drop perspectives --------------------------------------------------------------------------------------------------
IF Object_ID('dPE_wasHeld_ST_atLocation', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[dPE_wasHeld_ST_atLocation];
IF Object_ID('nPE_wasHeld_ST_atLocation', 'V') IS NOT NULL
DROP VIEW [dbo].[nPE_wasHeld_ST_atLocation];
IF Object_ID('pPE_wasHeld_ST_atLocation', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[pPE_wasHeld_ST_atLocation];
IF Object_ID('lPE_wasHeld_ST_atLocation', 'V') IS NOT NULL
DROP VIEW [dbo].[lPE_wasHeld_ST_atLocation];
GO
-- Latest perspective -------------------------------------------------------------------------------------------------
-- lPE_wasHeld_ST_atLocation viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lPE_wasHeld_ST_atLocation] WITH SCHEMABINDING AS
SELECT
    tie.Metadata_PE_wasHeld_ST_atLocation,
    tie.PE_ID_wasHeld,
    tie.ST_ID_atLocation
FROM
    [dbo].[PE_wasHeld_ST_atLocation] tie;
GO
-- Point-in-time perspective ------------------------------------------------------------------------------------------
-- pPE_wasHeld_ST_atLocation viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pPE_wasHeld_ST_atLocation] (
    @changingTimepoint datetime2(7)
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
    tie.Metadata_PE_wasHeld_ST_atLocation,
    tie.PE_ID_wasHeld,
    tie.ST_ID_atLocation
FROM
    [dbo].[PE_wasHeld_ST_atLocation] tie;
GO
-- Now perspective ----------------------------------------------------------------------------------------------------
-- nPE_wasHeld_ST_atLocation viewed as it currently is (cannot include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[nPE_wasHeld_ST_atLocation]
AS
SELECT
    *
FROM
    [dbo].[pPE_wasHeld_ST_atLocation](sysdatetime());
GO
-- Drop perspectives --------------------------------------------------------------------------------------------------
IF Object_ID('dAC_exclusive_AC_with', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[dAC_exclusive_AC_with];
IF Object_ID('nAC_exclusive_AC_with', 'V') IS NOT NULL
DROP VIEW [dbo].[nAC_exclusive_AC_with];
IF Object_ID('pAC_exclusive_AC_with', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[pAC_exclusive_AC_with];
IF Object_ID('lAC_exclusive_AC_with', 'V') IS NOT NULL
DROP VIEW [dbo].[lAC_exclusive_AC_with];
GO
-- Latest perspective -------------------------------------------------------------------------------------------------
-- lAC_exclusive_AC_with viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lAC_exclusive_AC_with] WITH SCHEMABINDING AS
SELECT
    tie.Metadata_AC_exclusive_AC_with,
    tie.AC_exclusive_AC_with_ChangedAt,
    tie.AC_ID_exclusive,
    tie.AC_ID_with
FROM
    [dbo].[AC_exclusive_AC_with] tie
WHERE
    tie.AC_exclusive_AC_with_ChangedAt = (
        SELECT
            max(sub.AC_exclusive_AC_with_ChangedAt)
        FROM
            [dbo].[AC_exclusive_AC_with] sub
        WHERE
            sub.AC_ID_exclusive = tie.AC_ID_exclusive
        OR
            sub.AC_ID_with = tie.AC_ID_with
   );
GO
-- Point-in-time perspective ------------------------------------------------------------------------------------------
-- pAC_exclusive_AC_with viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pAC_exclusive_AC_with] (
    @changingTimepoint datetime2(7)
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
    tie.Metadata_AC_exclusive_AC_with,
    tie.AC_exclusive_AC_with_ChangedAt,
    tie.AC_ID_exclusive,
    tie.AC_ID_with
FROM
    [dbo].[AC_exclusive_AC_with] tie
WHERE
    tie.AC_exclusive_AC_with_ChangedAt = (
        SELECT
            max(sub.AC_exclusive_AC_with_ChangedAt)
        FROM
            [dbo].[AC_exclusive_AC_with] sub
        WHERE
        (
                sub.AC_ID_exclusive = tie.AC_ID_exclusive
            OR
                sub.AC_ID_with = tie.AC_ID_with
        )
        AND
            tie.AC_exclusive_AC_with_ChangedAt <= @changingTimepoint
   );
GO
-- Now perspective ----------------------------------------------------------------------------------------------------
-- nAC_exclusive_AC_with viewed as it currently is (cannot include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[nAC_exclusive_AC_with]
AS
SELECT
    *
FROM
    [dbo].[pAC_exclusive_AC_with](sysdatetime());
GO
-- Difference perspective ---------------------------------------------------------------------------------------------
-- dAC_exclusive_AC_with showing all differences between the given timepoints
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[dAC_exclusive_AC_with] (
    @intervalStart datetime2(7),
    @intervalEnd datetime2(7)
)
RETURNS TABLE AS RETURN
SELECT
    tie.Metadata_AC_exclusive_AC_with,
    tie.AC_exclusive_AC_with_ChangedAt,
    tie.AC_ID_exclusive,
    tie.AC_ID_with
FROM
    [dbo].[AC_exclusive_AC_with] tie
WHERE
    tie.AC_exclusive_AC_with_ChangedAt BETWEEN @intervalStart AND @intervalEnd;
GO
-- Drop perspectives --------------------------------------------------------------------------------------------------
IF Object_ID('dPE_subset_EV_of', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[dPE_subset_EV_of];
IF Object_ID('nPE_subset_EV_of', 'V') IS NOT NULL
DROP VIEW [dbo].[nPE_subset_EV_of];
IF Object_ID('pPE_subset_EV_of', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[pPE_subset_EV_of];
IF Object_ID('lPE_subset_EV_of', 'V') IS NOT NULL
DROP VIEW [dbo].[lPE_subset_EV_of];
GO
-- Latest perspective -------------------------------------------------------------------------------------------------
-- lPE_subset_EV_of viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lPE_subset_EV_of] WITH SCHEMABINDING AS
SELECT
    tie.Metadata_PE_subset_EV_of,
    tie.PE_ID_subset,
    tie.EV_ID_of
FROM
    [dbo].[PE_subset_EV_of] tie;
GO
-- Point-in-time perspective ------------------------------------------------------------------------------------------
-- pPE_subset_EV_of viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pPE_subset_EV_of] (
    @changingTimepoint datetime2(7)
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
    tie.Metadata_PE_subset_EV_of,
    tie.PE_ID_subset,
    tie.EV_ID_of
FROM
    [dbo].[PE_subset_EV_of] tie;
GO
-- Now perspective ----------------------------------------------------------------------------------------------------
-- nPE_subset_EV_of viewed as it currently is (cannot include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[nPE_subset_EV_of]
AS
SELECT
    *
FROM
    [dbo].[pPE_subset_EV_of](sysdatetime());
GO
-- Drop perspectives --------------------------------------------------------------------------------------------------
IF Object_ID('dPE_in_AC_wasCast', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[dPE_in_AC_wasCast];
IF Object_ID('nPE_in_AC_wasCast', 'V') IS NOT NULL
DROP VIEW [dbo].[nPE_in_AC_wasCast];
IF Object_ID('pPE_in_AC_wasCast', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[pPE_in_AC_wasCast];
IF Object_ID('lPE_in_AC_wasCast', 'V') IS NOT NULL
DROP VIEW [dbo].[lPE_in_AC_wasCast];
GO
-- Latest perspective -------------------------------------------------------------------------------------------------
-- lPE_in_AC_wasCast viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lPE_in_AC_wasCast] WITH SCHEMABINDING AS
SELECT
    tie.Metadata_PE_in_AC_wasCast,
    tie.PE_ID_in,
    tie.AC_ID_wasCast
FROM
    [dbo].[PE_in_AC_wasCast] tie;
GO
-- Point-in-time perspective ------------------------------------------------------------------------------------------
-- pPE_in_AC_wasCast viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pPE_in_AC_wasCast] (
    @changingTimepoint datetime2(7)
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
    tie.Metadata_PE_in_AC_wasCast,
    tie.PE_ID_in,
    tie.AC_ID_wasCast
FROM
    [dbo].[PE_in_AC_wasCast] tie;
GO
-- Now perspective ----------------------------------------------------------------------------------------------------
-- nPE_in_AC_wasCast viewed as it currently is (cannot include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[nPE_in_AC_wasCast]
AS
SELECT
    *
FROM
    [dbo].[pPE_in_AC_wasCast](sysdatetime());
GO
-- Drop perspectives --------------------------------------------------------------------------------------------------
IF Object_ID('dAC_part_PR_in_RAT_got', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[dAC_part_PR_in_RAT_got];
IF Object_ID('nAC_part_PR_in_RAT_got', 'V') IS NOT NULL
DROP VIEW [dbo].[nAC_part_PR_in_RAT_got];
IF Object_ID('pAC_part_PR_in_RAT_got', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[pAC_part_PR_in_RAT_got];
IF Object_ID('lAC_part_PR_in_RAT_got', 'V') IS NOT NULL
DROP VIEW [dbo].[lAC_part_PR_in_RAT_got];
GO
-- Latest perspective -------------------------------------------------------------------------------------------------
-- lAC_part_PR_in_RAT_got viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lAC_part_PR_in_RAT_got] WITH SCHEMABINDING AS
SELECT
    tie.Metadata_AC_part_PR_in_RAT_got,
    tie.AC_part_PR_in_RAT_got_ChangedAt,
    tie.AC_ID_part,
    tie.PR_ID_in,
    [RAT_got].RAT_Rating AS got_RAT_Rating,
    [RAT_got].Metadata_RAT AS got_Metadata_RAT,
    tie.RAT_ID_got
FROM
    [dbo].[AC_part_PR_in_RAT_got] tie
LEFT JOIN
    [dbo].[RAT_Rating] [RAT_got]
ON
    [RAT_got].RAT_ID = tie.RAT_ID_got
WHERE
    tie.AC_part_PR_in_RAT_got_ChangedAt = (
        SELECT
            max(sub.AC_part_PR_in_RAT_got_ChangedAt)
        FROM
            [dbo].[AC_part_PR_in_RAT_got] sub
        WHERE
            sub.AC_ID_part = tie.AC_ID_part
        AND
            sub.PR_ID_in = tie.PR_ID_in
   );
GO
-- Point-in-time perspective ------------------------------------------------------------------------------------------
-- pAC_part_PR_in_RAT_got viewed by the latest available information (may include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pAC_part_PR_in_RAT_got] (
    @changingTimepoint datetime2(7)
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
    tie.Metadata_AC_part_PR_in_RAT_got,
    tie.AC_part_PR_in_RAT_got_ChangedAt,
    tie.AC_ID_part,
    tie.PR_ID_in,
    [RAT_got].RAT_Rating AS got_RAT_Rating,
    [RAT_got].Metadata_RAT AS got_Metadata_RAT,
    tie.RAT_ID_got
FROM
    [dbo].[AC_part_PR_in_RAT_got] tie
LEFT JOIN
    [dbo].[RAT_Rating] [RAT_got]
ON
    [RAT_got].RAT_ID = tie.RAT_ID_got
WHERE
    tie.AC_part_PR_in_RAT_got_ChangedAt = (
        SELECT
            max(sub.AC_part_PR_in_RAT_got_ChangedAt)
        FROM
            [dbo].[AC_part_PR_in_RAT_got] sub
        WHERE
            sub.AC_ID_part = tie.AC_ID_part
        AND
            sub.PR_ID_in = tie.PR_ID_in
        AND
            tie.AC_part_PR_in_RAT_got_ChangedAt <= @changingTimepoint
   );
GO
-- Now perspective ----------------------------------------------------------------------------------------------------
-- nAC_part_PR_in_RAT_got viewed as it currently is (cannot include future versions)
-----------------------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[nAC_part_PR_in_RAT_got]
AS
SELECT
    *
FROM
    [dbo].[pAC_part_PR_in_RAT_got](sysdatetime());
GO
-- Difference perspective ---------------------------------------------------------------------------------------------
-- dAC_part_PR_in_RAT_got showing all differences between the given timepoints
-----------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[dAC_part_PR_in_RAT_got] (
    @intervalStart datetime2(7),
    @intervalEnd datetime2(7)
)
RETURNS TABLE AS RETURN
SELECT
    tie.Metadata_AC_part_PR_in_RAT_got,
    tie.AC_part_PR_in_RAT_got_ChangedAt,
    tie.AC_ID_part,
    tie.PR_ID_in,
    [RAT_got].RAT_Rating AS got_RAT_Rating,
    [RAT_got].Metadata_RAT AS got_Metadata_RAT,
    tie.RAT_ID_got
FROM
    [dbo].[AC_part_PR_in_RAT_got] tie
LEFT JOIN
    [dbo].[RAT_Rating] [RAT_got]
ON
    [RAT_got].RAT_ID = tie.RAT_ID_got
WHERE
    tie.AC_part_PR_in_RAT_got_ChangedAt BETWEEN @intervalStart AND @intervalEnd;
GO
-- TIE TRIGGERS -------------------------------------------------------------------------------------------------------
--
-- The following triggers on the latest view make it behave like a table.
-- There are three different 'instead of' triggers: insert, update, and delete.
-- They will ensure that such operations are propagated to the underlying tables
-- in a consistent way. Default values are used for some columns if not provided
-- by the corresponding SQL statements.
--
-- For idempotent ties, only changes that represent values different from
-- the previous or following value are stored. Others are silently ignored in
-- order to avoid unnecessary temporal duplicates.
--
-- Insert trigger -----------------------------------------------------------------------------------------------------
-- itAC_parent_AC_child_PAT_having instead of INSERT trigger on lAC_parent_AC_child_PAT_having
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[itAC_parent_AC_child_PAT_having] ON [dbo].[lAC_parent_AC_child_PAT_having]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @now datetime2(7) = sysdatetime();
    DECLARE @maxVersion int;
    DECLARE @currentVersion int;
    DECLARE @inserted TABLE (
        Metadata_AC_parent_AC_child_PAT_having int not null,
        AC_ID_parent int not null,
        AC_ID_child int not null,
        PAT_ID_having tinyint not null,
        primary key (
            AC_ID_parent,
            AC_ID_child,
            PAT_ID_having
        )
    );
    INSERT INTO @inserted
    SELECT
        ISNULL(i.Metadata_AC_parent_AC_child_PAT_having, 0),
        i.AC_ID_parent,
        i.AC_ID_child,
        ISNULL(i.PAT_ID_having, [PAT_having].PAT_ID)
    FROM
        inserted i
    LEFT JOIN
        [dbo].[PAT_ParentalType] [PAT_having]
    ON
        [PAT_having].PAT_ParentalType = i.having_PAT_ParentalType
    WHERE
        i.AC_ID_parent is not null
    AND
        i.AC_ID_child is not null
    AND
        i.PAT_ID_having is not null;
    INSERT INTO [dbo].[AC_parent_AC_child_PAT_having] (
        Metadata_AC_parent_AC_child_PAT_having,
        AC_ID_parent,
        AC_ID_child,
        PAT_ID_having
    )
    SELECT
        i.Metadata_AC_parent_AC_child_PAT_having,
        i.AC_ID_parent,
        i.AC_ID_child,
        i.PAT_ID_having
    FROM
        @inserted i
    LEFT JOIN
        [dbo].[AC_parent_AC_child_PAT_having] tie
    ON
        tie.AC_ID_parent = i.AC_ID_parent
    AND
        tie.AC_ID_child = i.AC_ID_child
    AND
        tie.PAT_ID_having = i.PAT_ID_having
    WHERE
        tie.PAT_ID_having is null;
END
GO
-- DELETE trigger -----------------------------------------------------------------------------------------------------
-- dtAC_parent_AC_child_PAT_having instead of DELETE trigger on lAC_parent_AC_child_PAT_having
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[dtAC_parent_AC_child_PAT_having] ON [dbo].[lAC_parent_AC_child_PAT_having]
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE tie
    FROM
        [dbo].[AC_parent_AC_child_PAT_having] tie
    JOIN
        deleted d
    ON
        d.AC_ID_parent = tie.AC_ID_parent
    AND
        d.AC_ID_child = tie.AC_ID_child
    AND
        d.PAT_ID_having = tie.PAT_ID_having;
END
GO
-- Insert trigger -----------------------------------------------------------------------------------------------------
-- itST_atLocation_PR_isPlaying instead of INSERT trigger on lST_atLocation_PR_isPlaying
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[itST_atLocation_PR_isPlaying] ON [dbo].[lST_atLocation_PR_isPlaying]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @now datetime2(7) = sysdatetime();
    DECLARE @maxVersion int;
    DECLARE @currentVersion int;
    DECLARE @inserted TABLE (
        Metadata_ST_atLocation_PR_isPlaying int not null,
        ST_atLocation_PR_isPlaying_ChangedAt datetime not null,
        ST_atLocation_PR_isPlaying_Version bigint not null,
        ST_atLocation_PR_isPlaying_StatementType char(1) not null,
        ST_ID_atLocation int not null,
        PR_ID_isPlaying int not null,
        primary key (
            ST_atLocation_PR_isPlaying_Version,
            ST_ID_atLocation,
            PR_ID_isPlaying
        )
    );
    INSERT INTO @inserted
    SELECT
        ISNULL(i.Metadata_ST_atLocation_PR_isPlaying, 0),
        ISNULL(i.ST_atLocation_PR_isPlaying_ChangedAt, @now),
        DENSE_RANK() OVER (
            PARTITION BY
                ST_ID_atLocation,
                PR_ID_isPlaying
            ORDER BY
                ISNULL(i.ST_atLocation_PR_isPlaying_ChangedAt, @now)
        ),
        'X',
        i.ST_ID_atLocation,
        i.PR_ID_isPlaying
    FROM
        inserted i
    WHERE
        i.ST_ID_atLocation is not null
    AND
        i.PR_ID_isPlaying is not null;
    INSERT INTO [dbo].[ST_atLocation_PR_isPlaying] (
        Metadata_ST_atLocation_PR_isPlaying,
        ST_ID_atLocation,
        PR_ID_isPlaying
    )
    SELECT
        i.Metadata_ST_atLocation_PR_isPlaying,
        i.ST_ID_atLocation,
        i.PR_ID_isPlaying
    FROM
        @inserted i
    LEFT JOIN
        [dbo].[ST_atLocation_PR_isPlaying] tie
    ON
        tie.ST_ID_atLocation = i.ST_ID_atLocation
    AND
        tie.PR_ID_isPlaying = i.PR_ID_isPlaying
    WHERE
        tie.PR_ID_isPlaying is null;
END
GO
-- DELETE trigger -----------------------------------------------------------------------------------------------------
-- dtST_atLocation_PR_isPlaying instead of DELETE trigger on lST_atLocation_PR_isPlaying
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[dtST_atLocation_PR_isPlaying] ON [dbo].[lST_atLocation_PR_isPlaying]
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE tie
    FROM
        [dbo].[ST_atLocation_PR_isPlaying] tie
    JOIN
        deleted d
    ON
        d.ST_atLocation_PR_isPlaying_ChangedAt = tie.ST_atLocation_PR_isPlaying_ChangedAt
    AND
        d.ST_ID_atLocation = tie.ST_ID_atLocation
    AND
        d.PR_ID_isPlaying = tie.PR_ID_isPlaying;
END
GO
-- Insert trigger -----------------------------------------------------------------------------------------------------
-- itPE_at_PR_wasPlayed instead of INSERT trigger on lPE_at_PR_wasPlayed
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[itPE_at_PR_wasPlayed] ON [dbo].[lPE_at_PR_wasPlayed]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @now datetime2(7) = sysdatetime();
    DECLARE @maxVersion int;
    DECLARE @currentVersion int;
    DECLARE @inserted TABLE (
        Metadata_PE_at_PR_wasPlayed int not null,
        PE_ID_at int not null,
        PR_ID_wasPlayed int not null,
        primary key (
            PE_ID_at
        )
    );
    INSERT INTO @inserted
    SELECT
        ISNULL(i.Metadata_PE_at_PR_wasPlayed, 0),
        i.PE_ID_at,
        i.PR_ID_wasPlayed
    FROM
        inserted i
    WHERE
        i.PE_ID_at is not null;
    INSERT INTO [dbo].[PE_at_PR_wasPlayed] (
        Metadata_PE_at_PR_wasPlayed,
        PE_ID_at,
        PR_ID_wasPlayed
    )
    SELECT
        i.Metadata_PE_at_PR_wasPlayed,
        i.PE_ID_at,
        i.PR_ID_wasPlayed
    FROM
        @inserted i
    LEFT JOIN
        [dbo].[PE_at_PR_wasPlayed] tie
    ON
        tie.PE_ID_at = i.PE_ID_at
    WHERE
        tie.PE_ID_at is null;
END
GO
-- DELETE trigger -----------------------------------------------------------------------------------------------------
-- dtPE_at_PR_wasPlayed instead of DELETE trigger on lPE_at_PR_wasPlayed
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[dtPE_at_PR_wasPlayed] ON [dbo].[lPE_at_PR_wasPlayed]
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE tie
    FROM
        [dbo].[PE_at_PR_wasPlayed] tie
    JOIN
        deleted d
    ON
        d.PE_ID_at = tie.PE_ID_at;
END
GO
-- Insert trigger -----------------------------------------------------------------------------------------------------
-- itPE_wasHeld_ST_atLocation instead of INSERT trigger on lPE_wasHeld_ST_atLocation
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[itPE_wasHeld_ST_atLocation] ON [dbo].[lPE_wasHeld_ST_atLocation]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @now datetime2(7) = sysdatetime();
    DECLARE @maxVersion int;
    DECLARE @currentVersion int;
    DECLARE @inserted TABLE (
        Metadata_PE_wasHeld_ST_atLocation int not null,
        PE_ID_wasHeld int not null,
        ST_ID_atLocation int not null,
        primary key (
            PE_ID_wasHeld
        )
    );
    INSERT INTO @inserted
    SELECT
        ISNULL(i.Metadata_PE_wasHeld_ST_atLocation, 0),
        i.PE_ID_wasHeld,
        i.ST_ID_atLocation
    FROM
        inserted i
    WHERE
        i.PE_ID_wasHeld is not null;
    INSERT INTO [dbo].[PE_wasHeld_ST_atLocation] (
        Metadata_PE_wasHeld_ST_atLocation,
        PE_ID_wasHeld,
        ST_ID_atLocation
    )
    SELECT
        i.Metadata_PE_wasHeld_ST_atLocation,
        i.PE_ID_wasHeld,
        i.ST_ID_atLocation
    FROM
        @inserted i
    LEFT JOIN
        [dbo].[PE_wasHeld_ST_atLocation] tie
    ON
        tie.PE_ID_wasHeld = i.PE_ID_wasHeld
    WHERE
        tie.PE_ID_wasHeld is null;
END
GO
-- DELETE trigger -----------------------------------------------------------------------------------------------------
-- dtPE_wasHeld_ST_atLocation instead of DELETE trigger on lPE_wasHeld_ST_atLocation
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[dtPE_wasHeld_ST_atLocation] ON [dbo].[lPE_wasHeld_ST_atLocation]
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE tie
    FROM
        [dbo].[PE_wasHeld_ST_atLocation] tie
    JOIN
        deleted d
    ON
        d.PE_ID_wasHeld = tie.PE_ID_wasHeld;
END
GO
-- Insert trigger -----------------------------------------------------------------------------------------------------
-- itAC_exclusive_AC_with instead of INSERT trigger on lAC_exclusive_AC_with
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[itAC_exclusive_AC_with] ON [dbo].[lAC_exclusive_AC_with]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @now datetime2(7) = sysdatetime();
    DECLARE @maxVersion int;
    DECLARE @currentVersion int;
    DECLARE @inserted TABLE (
        Metadata_AC_exclusive_AC_with int not null,
        AC_exclusive_AC_with_ChangedAt datetime not null,
        AC_exclusive_AC_with_Version bigint not null,
        AC_exclusive_AC_with_StatementType char(1) not null,
        AC_ID_exclusive int not null,
        AC_ID_with int not null,
        primary key (
            AC_exclusive_AC_with_Version,
            AC_ID_exclusive,
            AC_ID_with
        )
    );
    INSERT INTO @inserted
    SELECT
        ISNULL(i.Metadata_AC_exclusive_AC_with, 0),
        ISNULL(i.AC_exclusive_AC_with_ChangedAt, @now),
        DENSE_RANK() OVER (
            PARTITION BY
                AC_ID_exclusive,
                AC_ID_with
            ORDER BY
                ISNULL(i.AC_exclusive_AC_with_ChangedAt, @now)
        ),
        'X',
        i.AC_ID_exclusive,
        i.AC_ID_with
    FROM
        inserted i
    WHERE
        i.AC_ID_exclusive is not null
    AND
        i.AC_ID_with is not null;
    SELECT
        @maxVersion = max(AC_exclusive_AC_with_Version),
        @currentVersion = 0
    FROM
        @inserted;
    WHILE (@currentVersion < @maxVersion)
    BEGIN
        SET @currentVersion = @currentVersion + 1;
        UPDATE v
        SET
            v.AC_exclusive_AC_with_StatementType =
                CASE
                    WHEN tie.AC_exclusive_AC_with_ChangedAt is not null
                    THEN 'D' -- duplicate
                    WHEN [dbo].[rfAC_exclusive_AC_with] (
                        v.AC_ID_exclusive,
                        v.AC_ID_with,
                        v.AC_exclusive_AC_with_ChangedAt
                    ) > 0
                    THEN 'R' -- restatement
                    ELSE 'N' -- new statement
                END
        FROM
            @inserted v
        LEFT JOIN
            [dbo].[AC_exclusive_AC_with] tie
        ON
            tie.AC_exclusive_AC_with_ChangedAt = v.AC_exclusive_AC_with_ChangedAt
        AND
            tie.AC_ID_exclusive = v.AC_ID_exclusive
        AND
            tie.AC_ID_with = v.AC_ID_with
        WHERE
            v.AC_exclusive_AC_with_Version = @currentVersion;
        INSERT INTO [dbo].[AC_exclusive_AC_with] (
            Metadata_AC_exclusive_AC_with,
            AC_ID_exclusive,
            AC_ID_with
        )
        SELECT
            Metadata_AC_exclusive_AC_with,
            AC_ID_exclusive,
            AC_ID_with
        FROM
            @inserted
        WHERE
            AC_exclusive_AC_with_Version = @currentVersion
        AND
            AC_exclusive_AC_with_StatementType in ('N');
    END
END
GO
-- UPDATE trigger -----------------------------------------------------------------------------------------------------
-- utAC_exclusive_AC_with instead of UPDATE trigger on lAC_exclusive_AC_with
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[utAC_exclusive_AC_with] ON [dbo].[lAC_exclusive_AC_with]
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @now datetime2(7) = sysdatetime();
    INSERT INTO [dbo].[AC_exclusive_AC_with] (
        Metadata_AC_exclusive_AC_with,
        AC_ID_exclusive,
        AC_ID_with,
        AC_exclusive_AC_with_ChangedAt
    )
    SELECT
        i.Metadata_AC_exclusive_AC_with,
        i.AC_ID_exclusive,
        i.AC_ID_with,
        CASE WHEN UPDATE(AC_exclusive_AC_with_ChangedAt) THEN i.AC_exclusive_AC_with_ChangedAt ELSE @now END
    FROM
        inserted i
    LEFT JOIN
        [dbo].[AC_exclusive_AC_with] tie
    ON
        tie.AC_ID_exclusive = i.AC_ID_exclusive
    OR
        tie.AC_ID_with = i.AC_ID_with
    WHERE
        tie.AC_ID_with is null
    AND
        [dbo].[rfAC_exclusive_AC_with](
            i.AC_ID_exclusive,
            i.AC_ID_with,
            i.AC_exclusive_AC_with_ChangedAt
        ) = 0;
END
GO
-- DELETE trigger -----------------------------------------------------------------------------------------------------
-- dtAC_exclusive_AC_with instead of DELETE trigger on lAC_exclusive_AC_with
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[dtAC_exclusive_AC_with] ON [dbo].[lAC_exclusive_AC_with]
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE tie
    FROM
        [dbo].[AC_exclusive_AC_with] tie
    JOIN
        deleted d
    ON
        d.AC_exclusive_AC_with_ChangedAt = tie.AC_exclusive_AC_with_ChangedAt
    AND
       (
            d.AC_ID_exclusive = tie.AC_ID_exclusive
        OR
            d.AC_ID_with = tie.AC_ID_with
       );
END
GO
-- Insert trigger -----------------------------------------------------------------------------------------------------
-- itPE_subset_EV_of instead of INSERT trigger on lPE_subset_EV_of
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[itPE_subset_EV_of] ON [dbo].[lPE_subset_EV_of]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @now datetime2(7) = sysdatetime();
    DECLARE @maxVersion int;
    DECLARE @currentVersion int;
    DECLARE @inserted TABLE (
        Metadata_PE_subset_EV_of int not null,
        PE_ID_subset int not null,
        EV_ID_of int not null,
        primary key (
            PE_ID_subset,
            EV_ID_of
        )
    );
    INSERT INTO @inserted
    SELECT
        ISNULL(i.Metadata_PE_subset_EV_of, 0),
        i.PE_ID_subset,
        i.EV_ID_of
    FROM
        inserted i
    WHERE
        i.PE_ID_subset is not null
    AND
        i.EV_ID_of is not null;
    INSERT INTO [dbo].[PE_subset_EV_of] (
        Metadata_PE_subset_EV_of,
        PE_ID_subset,
        EV_ID_of
    )
    SELECT
        i.Metadata_PE_subset_EV_of,
        i.PE_ID_subset,
        i.EV_ID_of
    FROM
        @inserted i
    LEFT JOIN
        [dbo].[PE_subset_EV_of] tie
    ON
        tie.PE_ID_subset = i.PE_ID_subset
    OR
        tie.EV_ID_of = i.EV_ID_of
    WHERE
        tie.EV_ID_of is null;
END
GO
-- DELETE trigger -----------------------------------------------------------------------------------------------------
-- dtPE_subset_EV_of instead of DELETE trigger on lPE_subset_EV_of
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[dtPE_subset_EV_of] ON [dbo].[lPE_subset_EV_of]
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE tie
    FROM
        [dbo].[PE_subset_EV_of] tie
    JOIN
        deleted d
    ON
       (
            d.PE_ID_subset = tie.PE_ID_subset
        OR
            d.EV_ID_of = tie.EV_ID_of
       );
END
GO
-- Insert trigger -----------------------------------------------------------------------------------------------------
-- itPE_in_AC_wasCast instead of INSERT trigger on lPE_in_AC_wasCast
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[itPE_in_AC_wasCast] ON [dbo].[lPE_in_AC_wasCast]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @now datetime2(7) = sysdatetime();
    DECLARE @maxVersion int;
    DECLARE @currentVersion int;
    DECLARE @inserted TABLE (
        Metadata_PE_in_AC_wasCast int not null,
        PE_ID_in int not null,
        AC_ID_wasCast int not null,
        primary key (
            PE_ID_in,
            AC_ID_wasCast
        )
    );
    INSERT INTO @inserted
    SELECT
        ISNULL(i.Metadata_PE_in_AC_wasCast, 0),
        i.PE_ID_in,
        i.AC_ID_wasCast
    FROM
        inserted i
    WHERE
        i.PE_ID_in is not null
    AND
        i.AC_ID_wasCast is not null;
    INSERT INTO [dbo].[PE_in_AC_wasCast] (
        Metadata_PE_in_AC_wasCast,
        PE_ID_in,
        AC_ID_wasCast
    )
    SELECT
        i.Metadata_PE_in_AC_wasCast,
        i.PE_ID_in,
        i.AC_ID_wasCast
    FROM
        @inserted i
    LEFT JOIN
        [dbo].[PE_in_AC_wasCast] tie
    ON
        tie.PE_ID_in = i.PE_ID_in
    AND
        tie.AC_ID_wasCast = i.AC_ID_wasCast
    WHERE
        tie.AC_ID_wasCast is null;
END
GO
-- DELETE trigger -----------------------------------------------------------------------------------------------------
-- dtPE_in_AC_wasCast instead of DELETE trigger on lPE_in_AC_wasCast
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[dtPE_in_AC_wasCast] ON [dbo].[lPE_in_AC_wasCast]
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE tie
    FROM
        [dbo].[PE_in_AC_wasCast] tie
    JOIN
        deleted d
    ON
        d.PE_ID_in = tie.PE_ID_in
    AND
        d.AC_ID_wasCast = tie.AC_ID_wasCast;
END
GO
-- Insert trigger -----------------------------------------------------------------------------------------------------
-- itAC_part_PR_in_RAT_got instead of INSERT trigger on lAC_part_PR_in_RAT_got
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[itAC_part_PR_in_RAT_got] ON [dbo].[lAC_part_PR_in_RAT_got]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @now datetime2(7) = sysdatetime();
    DECLARE @maxVersion int;
    DECLARE @currentVersion int;
    DECLARE @inserted TABLE (
        Metadata_AC_part_PR_in_RAT_got int not null,
        AC_part_PR_in_RAT_got_ChangedAt datetime not null,
        AC_part_PR_in_RAT_got_Version bigint not null,
        AC_part_PR_in_RAT_got_StatementType char(1) not null,
        AC_ID_part int not null,
        PR_ID_in int not null,
        RAT_ID_got tinyint not null,
        primary key (
            AC_part_PR_in_RAT_got_Version,
            AC_ID_part,
            PR_ID_in
        )
    );
    INSERT INTO @inserted
    SELECT
        ISNULL(i.Metadata_AC_part_PR_in_RAT_got, 0),
        ISNULL(i.AC_part_PR_in_RAT_got_ChangedAt, @now),
        DENSE_RANK() OVER (
            PARTITION BY
                AC_ID_part,
                PR_ID_in
            ORDER BY
                ISNULL(i.AC_part_PR_in_RAT_got_ChangedAt, @now)
        ),
        'X',
        i.AC_ID_part,
        i.PR_ID_in,
        ISNULL(i.RAT_ID_got, [RAT_got].RAT_ID)
    FROM
        inserted i
    LEFT JOIN
        [dbo].[RAT_Rating] [RAT_got]
    ON
        [RAT_got].RAT_Rating = i.got_RAT_Rating
    WHERE
        i.AC_ID_part is not null
    AND
        i.PR_ID_in is not null;
    SELECT
        @maxVersion = max(AC_part_PR_in_RAT_got_Version),
        @currentVersion = 0
    FROM
        @inserted;
    WHILE (@currentVersion < @maxVersion)
    BEGIN
        SET @currentVersion = @currentVersion + 1;
        UPDATE v
        SET
            v.AC_part_PR_in_RAT_got_StatementType =
                CASE
                    WHEN tie.AC_part_PR_in_RAT_got_ChangedAt is not null
                    THEN 'D' -- duplicate
                    WHEN [dbo].[rfAC_part_PR_in_RAT_got] (
                        v.AC_ID_part,
                        v.PR_ID_in,
                        v.RAT_ID_got,
                        v.AC_part_PR_in_RAT_got_ChangedAt
                    ) > 0
                    THEN 'R' -- restatement
                    ELSE 'N' -- new statement
                END
        FROM
            @inserted v
        LEFT JOIN
            [dbo].[AC_part_PR_in_RAT_got] tie
        ON
            tie.AC_part_PR_in_RAT_got_ChangedAt = v.AC_part_PR_in_RAT_got_ChangedAt
        AND
            tie.AC_ID_part = v.AC_ID_part
        AND
            tie.PR_ID_in = v.PR_ID_in
        AND
            tie.RAT_ID_got = v.RAT_ID_got
        WHERE
            v.AC_part_PR_in_RAT_got_Version = @currentVersion;
        INSERT INTO [dbo].[AC_part_PR_in_RAT_got] (
            Metadata_AC_part_PR_in_RAT_got,
            AC_ID_part,
            PR_ID_in,
            RAT_ID_got
        )
        SELECT
            Metadata_AC_part_PR_in_RAT_got,
            AC_ID_part,
            PR_ID_in,
            RAT_ID_got
        FROM
            @inserted
        WHERE
            AC_part_PR_in_RAT_got_Version = @currentVersion
        AND
            AC_part_PR_in_RAT_got_StatementType in ('N');
    END
END
GO
-- UPDATE trigger -----------------------------------------------------------------------------------------------------
-- utAC_part_PR_in_RAT_got instead of UPDATE trigger on lAC_part_PR_in_RAT_got
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[utAC_part_PR_in_RAT_got] ON [dbo].[lAC_part_PR_in_RAT_got]
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @now datetime2(7) = sysdatetime();
    IF(UPDATE(AC_ID_part))
        RAISERROR('The identity column AC_ID_part is not updatable.', 16, 1);
    IF(UPDATE(PR_ID_in))
        RAISERROR('The identity column PR_ID_in is not updatable.', 16, 1);
    INSERT INTO [dbo].[AC_part_PR_in_RAT_got] (
        Metadata_AC_part_PR_in_RAT_got,
        AC_ID_part,
        PR_ID_in,
        RAT_ID_got,
        AC_part_PR_in_RAT_got_ChangedAt
    )
    SELECT
        i.Metadata_AC_part_PR_in_RAT_got,
        i.AC_ID_part,
        i.PR_ID_in,
        i.RAT_ID_got,
        CASE WHEN UPDATE(AC_part_PR_in_RAT_got_ChangedAt) THEN i.AC_part_PR_in_RAT_got_ChangedAt ELSE @now END
    FROM
        inserted i
    LEFT JOIN
        [dbo].[AC_part_PR_in_RAT_got] tie
    ON
        tie.AC_ID_part = i.AC_ID_part
    AND
        tie.PR_ID_in = i.PR_ID_in
    WHERE
        tie.PR_ID_in is null
    AND
        [dbo].[rfAC_part_PR_in_RAT_got](
            i.AC_ID_part,
            i.PR_ID_in,
            i.RAT_ID_got,
            i.AC_part_PR_in_RAT_got_ChangedAt
        ) = 0;
END
GO
-- DELETE trigger -----------------------------------------------------------------------------------------------------
-- dtAC_part_PR_in_RAT_got instead of DELETE trigger on lAC_part_PR_in_RAT_got
-----------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER [dbo].[dtAC_part_PR_in_RAT_got] ON [dbo].[lAC_part_PR_in_RAT_got]
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE tie
    FROM
        [dbo].[AC_part_PR_in_RAT_got] tie
    JOIN
        deleted d
    ON
        d.AC_part_PR_in_RAT_got_ChangedAt = tie.AC_part_PR_in_RAT_got_ChangedAt
    AND
        d.AC_ID_part = tie.AC_ID_part
    AND
        d.PR_ID_in = tie.PR_ID_in;
END
GO
-- SCHEMA EVOLUTION ---------------------------------------------------------------------------------------------------
--
-- The following tables, views, and functions are used to track schema changes
-- over time, as well as providing every XML that has been 'executed' against
-- the database.
--
-- Schema table -------------------------------------------------------------------------------------------------------
-- The schema table holds every xml that has been executed against the database
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('_Schema', 'U') IS NULL
   CREATE TABLE [dbo].[_Schema] (
      [version] int identity(1, 1) not null primary key,
      [activation] datetime2(7) not null,
      [schema] xml not null
   );
GO
-- Insert the XML schema (as of now)
INSERT INTO [dbo].[_Schema] (
   [activation],
   [schema]
)
SELECT
   current_timestamp,
   N'<schema><metadata format="0.97" temporalization="uni"/><knot mnemonic="PAT" descriptor="ParentalType" identity="tinyint" dataRange="varchar(42)"><metadata capsule="dbo" generator="false"/><layout x="616.06" y="383.39" fixed="false"/></knot><knot mnemonic="PLV" descriptor="ProfessionalLevel" identity="tinyint" dataRange="varchar(42)"><metadata capsule="dbo" generator="false"/><layout x="547.64" y="327.96" fixed="false"/></knot><knot mnemonic="RAT" descriptor="Rating" identity="tinyint" dataRange="varchar(42)"><metadata capsule="dbo" generator="false"/><layout x="867.37" y="247.09" fixed="false"/></knot><knot mnemonic="GEN" descriptor="Gender" identity="bit" dataRange="varchar(42)"><metadata capsule="dbo" generator="false"/><layout x="580.04" y="194.93" fixed="false"/></knot><anchor mnemonic="EV" descriptor="Event" identity="int"><metadata capsule="dbo" generator="true"/><layout x="1112.63" y="240.76" fixed="false"/></anchor><anchor mnemonic="PR" descriptor="Program" identity="int"><metadata capsule="dbo" generator="true"/><attribute mnemonic="NAM" descriptor="Name" dataRange="varchar(42)"><metadata capsule="dbo"/><layout x="993.36" y="419.62" fixed="false"/></attribute><layout x="999.17" y="337.14" fixed="false"/></anchor><anchor mnemonic="ST" descriptor="Stage" identity="int"><metadata capsule="dbo" generator="true"/><attribute mnemonic="NAM" descriptor="Name" timeRange="datetime" dataRange="varchar(42)"><metadata capsule="dbo" restatable="false" idempotent="true"/><layout x="1249.50" y="364.75" fixed="false"/></attribute><attribute mnemonic="LOC" descriptor="Location" dataRange="varchar(42)"><metadata capsule="dbo"/><layout x="1292.59" y="308.91" fixed="false"/></attribute><layout x="1200.71" y="316.61" fixed="false"/></anchor><anchor mnemonic="PE" descriptor="Performance" identity="int"><metadata capsule="dbo" generator="true"/><attribute mnemonic="DAT" descriptor="Date" dataRange="datetime"><metadata capsule="dbo"/><layout x="1087.90" y="16.92" fixed="false"/></attribute><attribute mnemonic="AUD" descriptor="Audience" dataRange="int"><metadata capsule="dbo"/><layout x="1055.59" y="36.48" fixed="false"/></attribute><attribute mnemonic="REV" descriptor="Revenue" dataRange="money"><metadata capsule="dbo"/><layout x="1136.77" y="59.82" fixed="false"/></attribute><layout x="1078.79" y="111.16" fixed="false"/></anchor><anchor mnemonic="AC" descriptor="Actor" identity="int"><metadata capsule="dbo" generator="true"/><attribute mnemonic="NAM" descriptor="Name" timeRange="datetime" dataRange="varchar(42)"><metadata capsule="dbo" restatable="false" idempotent="true"/><layout x="771.78" y="342.28" fixed="false"/></attribute><attribute mnemonic="GEN" descriptor="Gender" knotRange="GEN"><metadata capsule="dbo"/><layout x="642.32" y="227.10" fixed="false"/></attribute><attribute mnemonic="PLV" descriptor="ProfessionalLevel" timeRange="datetime" knotRange="PLV"><metadata capsule="dbo" restatable="false" idempotent="true"/><layout x="634.20" y="311.32" fixed="false"/></attribute><layout x="738.14" y="263.82" fixed="false"/></anchor><tie><anchorRole role="parent" type="AC" identifier="true"/><anchorRole role="child" type="AC" identifier="true"/><knotRole role="having" type="PAT" identifier="true"/><metadata capsule="dbo"/><layout x="679.70" y="337.17" fixed="false"/></tie><tie timeRange="datetime"><anchorRole role="atLocation" type="ST" identifier="true"/><anchorRole role="isPlaying" type="PR" identifier="true"/><metadata capsule="dbo" restatable="false" idempotent="true"/><layout x="1093.51" y="335.53" fixed="false"/></tie><tie><anchorRole role="at" type="PE" identifier="true"/><anchorRole role="wasPlayed" type="PR" identifier="false"/><metadata capsule="dbo"/><layout x="1034.95" y="219.71" fixed="false"/></tie><tie><anchorRole role="wasHeld" type="PE" identifier="true"/><anchorRole role="atLocation" type="ST" identifier="false"/><metadata capsule="dbo"/><layout x="1173.89" y="210.84" fixed="false"/></tie><tie timeRange="datetime"><anchorRole role="exclusive" type="AC" identifier="false"/><anchorRole role="with" type="AC" identifier="false"/><metadata capsule="dbo" restatable="false" idempotent="true"/><layout x="720.47" y="193.69" fixed="false"/></tie><tie><anchorRole role="subset" type="PE" identifier="false"/><anchorRole role="of" type="EV" identifier="false"/><metadata capsule="dbo"/><layout x="1073.97" y="201.16" fixed="false"/></tie><tie><anchorRole role="in" type="PE" identifier="true"/><anchorRole role="wasCast" type="AC" identifier="true"/><metadata capsule="dbo"/><layout x="896.09" y="183.39" fixed="false"/></tie><tie timeRange="datetime"><anchorRole role="part" type="AC" identifier="true"/><anchorRole role="in" type="PR" identifier="true"/><knotRole role="got" type="RAT" identifier="false"/><metadata capsule="dbo" restatable="false" idempotent="true"/><layout x="864.02" y="322.19" fixed="false"/></tie></schema>';
GO
-- Anchor view --------------------------------------------------------------------------------------------------------
-- The anchor view shows information about all the anchors in a schema
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('_Anchor', 'V') IS NOT NULL
DROP VIEW [dbo].[_Anchor]
GO
CREATE VIEW [dbo].[_Anchor]
AS
SELECT
   S.version,
   S.activation,
   Nodeset.anchor.value('concat(@mnemonic, "_", @descriptor)', 'nvarchar(max)') as [name],
   Nodeset.anchor.value('metadata[1]/@capsule', 'nvarchar(max)') as [capsule],
   Nodeset.anchor.value('@mnemonic', 'nvarchar(max)') as [mnemonic],
   Nodeset.anchor.value('@descriptor', 'nvarchar(max)') as [descriptor],
   Nodeset.anchor.value('@identity', 'nvarchar(max)') as [identity],
   Nodeset.anchor.value('metadata[1]/@generator', 'nvarchar(max)') as [generator],
   Nodeset.anchor.value('count(attribute)', 'int') as [numberOfAttributes]
FROM
   [dbo].[_Schema] S
CROSS APPLY
   S.[schema].nodes('/schema/anchor') as Nodeset(anchor);
GO
-- Knot view ----------------------------------------------------------------------------------------------------------
-- The knot view shows information about all the knots in a schema
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('_Knot', 'V') IS NOT NULL
DROP VIEW [dbo].[_Knot]
GO
CREATE VIEW [dbo].[_Knot]
AS
SELECT
   S.version,
   S.activation,
   Nodeset.knot.value('concat(@mnemonic, "_", @descriptor)', 'nvarchar(max)') as [name],
   Nodeset.knot.value('metadata[1]/@capsule', 'nvarchar(max)') as [capsule],
   Nodeset.knot.value('@mnemonic', 'nvarchar(max)') as [mnemonic],
   Nodeset.knot.value('@descriptor', 'nvarchar(max)') as [descriptor],
   Nodeset.knot.value('@identity', 'nvarchar(max)') as [identity],
   Nodeset.knot.value('metadata[1]/@generator', 'nvarchar(max)') as [generator],
   Nodeset.knot.value('@dataRange', 'nvarchar(max)') as [dataRange]
FROM
   [dbo].[_Schema] S
CROSS APPLY
   S.[schema].nodes('/schema/knot') as Nodeset(knot);
GO
-- Attribute view -----------------------------------------------------------------------------------------------------
-- The attribute view shows information about all the attributes in a schema
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('_Attribute', 'V') IS NOT NULL
DROP VIEW [dbo].[_Attribute]
GO
CREATE VIEW [dbo].[_Attribute]
AS
SELECT
   S.version,
   S.activation,
   ParentNodeset.anchor.value('concat(@mnemonic, "_")', 'nvarchar(max)') +
   Nodeset.attribute.value('concat(@mnemonic, "_")', 'nvarchar(max)') +
   ParentNodeset.anchor.value('concat(@descriptor, "_")', 'nvarchar(max)') +
   Nodeset.attribute.value('@descriptor', 'nvarchar(max)') as [name],
   Nodeset.attribute.value('metadata[1]/@capsule', 'nvarchar(max)') as [capsule],
   Nodeset.attribute.value('@mnemonic', 'nvarchar(max)') as [mnemonic],
   Nodeset.attribute.value('@descriptor', 'nvarchar(max)') as [descriptor],
   ParentNodeset.anchor.value('@mnemonic', 'nvarchar(max)') as [anchorMnemonic],
   ParentNodeset.anchor.value('@descriptor', 'nvarchar(max)') as [anchorDescriptor],
   ParentNodeset.anchor.value('@identity', 'nvarchar(max)') as [anchorIdentity],
   Nodeset.attribute.value('@dataRange', 'nvarchar(max)') as [dataRange],
   Nodeset.attribute.value('@knotRange', 'nvarchar(max)') as [knotRange],
   Nodeset.attribute.value('@timeRange', 'nvarchar(max)') as [timeRange]
FROM
   [dbo].[_Schema] S
CROSS APPLY
   S.[schema].nodes('/schema/anchor') as ParentNodeset(anchor)
OUTER APPLY
   ParentNodeset.anchor.nodes('attribute') as Nodeset(attribute);
GO
-- Tie view -----------------------------------------------------------------------------------------------------------
-- The tie view shows information about all the ties in a schema
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('_Tie', 'V') IS NOT NULL
DROP VIEW [dbo].[_Tie]
GO
CREATE VIEW [dbo].[_Tie]
AS
SELECT
   S.version,
   S.activation,
   REPLACE(Nodeset.tie.query('
      for $role in *[local-name() = "anchorRole" or local-name() = "knotRole"]
      return concat($role/@type, "_", $role/@role)
   ').value('.', 'nvarchar(max)'), ' ', '_') as [name],
   Nodeset.tie.value('metadata[1]/@capsule', 'nvarchar(max)') as [capsule],
   Nodeset.tie.value('count(anchorRole) + count(knotRole)', 'int') as [numberOfRoles],
   Nodeset.tie.query('
      for $role in *[local-name() = "anchorRole" or local-name() = "knotRole"]
      return string($role/@role)
   ').value('.', 'nvarchar(max)') as [roles],
   Nodeset.tie.value('count(anchorRole)', 'int') as [numberOfAnchors],
   Nodeset.tie.query('
      for $role in anchorRole
      return string($role/@type)
   ').value('.', 'nvarchar(max)') as [anchors],
   Nodeset.tie.value('count(knotRole)', 'int') as [numberOfKnots],
   Nodeset.tie.query('
      for $role in knotRole
      return string($role/@type)
   ').value('.', 'nvarchar(max)') as [knots],
   Nodeset.tie.value('count(*[local-name() = "anchorRole" or local-name() = "knotRole"][@identifier = "true"])', 'int') as [numberOfIdentifiers],
   Nodeset.tie.query('
      for $role in *[local-name() = "anchorRole" or local-name() = "knotRole"][@identifier = "true"]
      return string($role/@type)
   ').value('.', 'nvarchar(max)') as [identifiers],
   Nodeset.tie.value('@timeRange', 'nvarchar(max)') as [timeRange]
FROM
   [dbo].[_Schema] S
CROSS APPLY
   S.[schema].nodes('/schema/tie') as Nodeset(tie);
GO
-- Evolution function -------------------------------------------------------------------------------------------------
-- The evolution function shows what the schema looked like at the given
-- point in time with additional information about missing or added
-- modeling components since that time.
--
-- @timepoint The point in time to which you would like to travel.
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('_Evolution', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[_Evolution];
GO
CREATE FUNCTION [dbo].[_Evolution] (
    @timepoint AS DATETIME2(7)
)
RETURNS TABLE
RETURN
SELECT
   V.[version],
   ISNULL(S.[name], T.[name]) AS [name],
   ISNULL(V.[activation], T.[create_date]) AS [activation],
   CASE
      WHEN S.[name] is null THEN
         CASE
            WHEN T.[create_date] > (
               SELECT
                  ISNULL(MAX([activation]), @timepoint)
               FROM
                  [dbo].[_Schema]
               WHERE
                  [activation] <= @timepoint
            ) THEN 'Future'
            ELSE 'Past'
         END
      WHEN T.[name] is null THEN 'Missing'
      ELSE 'Present'
   END AS Existence
FROM (
   SELECT
      MAX([version]) as [version],
      MAX([activation]) as [activation]
   FROM
      [dbo].[_Schema]
   WHERE
      [activation] <= @timepoint
) V
JOIN (
   SELECT
      [name],
      [version]
   FROM
      [dbo].[_Anchor] a
   UNION ALL
   SELECT
      [name],
      [version]
   FROM
      [dbo].[_Knot] k
   UNION ALL
   SELECT
      [name],
      [version]
   FROM
      [dbo].[_Attribute] b
   UNION ALL
   SELECT
      [name],
      [version]
   FROM
      [dbo].[_Tie] t
) S
ON
   S.[version] = V.[version]
FULL OUTER JOIN (
   SELECT
      [name],
      [create_date]
   FROM
      sys.tables
   WHERE
      [type] like '%U%'
   AND
      LEFT([name], 1) <> '_'
) T
ON
   S.[name] = T.[name];
GO
-- Drop Script Generator ----------------------------------------------------------------------------------------------
-- generates a drop script, that must be run separately, dropping everything in an Anchor Modeled database
-----------------------------------------------------------------------------------------------------------------------
IF Object_ID('_GenerateDropScript', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[_GenerateDropScript];
GO
CREATE PROCEDURE [dbo]._GenerateDropScript (
   @exclusionPattern varchar(42) = '[_]%' -- exclude Metadata by default
)
AS
BEGIN
   WITH objects AS (
      SELECT
         'DROP ' + ft.[type] + ' ' + fn.[name] + '; -- ' + fn.[description] as [statement],
         row_number() OVER (
            ORDER BY
               -- order based on type
               CASE ft.[type]
                  WHEN 'PROCEDURE' THEN 1
                  WHEN 'FUNCTION' THEN 2
                  WHEN 'VIEW' THEN 3
                  WHEN 'TABLE' THEN 4
                  ELSE 5
               END ASC,
               -- order within type
               CASE dc.[description]
                  WHEN 'key generator' THEN 1
                  WHEN 'latest perspective' THEN 2
                  WHEN 'current perspective' THEN 3
                  WHEN 'difference perspective' THEN 4
                  WHEN 'point-in-time perspective' THEN 5
                  WHEN 'time traveler' THEN 6
                  WHEN 'rewinder' THEN 7
                  WHEN 'assembled view' THEN 8
                  WHEN 'annex table' THEN 9
                  WHEN 'posit table' THEN 10
                  WHEN 'table' THEN 11
                  WHEN 'restatement finder' THEN 12
                  ELSE 13
               END,
               -- order within description
               CASE ft.[type]
                  WHEN 'TABLE' THEN
                     CASE cl.[class]
                        WHEN 'Attribute' THEN 1
                        WHEN 'Attribute Annex' THEN 2
                        WHEN 'Attribute Posit' THEN 3
                        WHEN 'Tie' THEN 4
                        WHEN 'Anchor' THEN 5
                        WHEN 'Knot' THEN 6
                        ELSE 7
                     END
                  ELSE
                     CASE cl.[class]
                        WHEN 'Anchor' THEN 1
                        WHEN 'Attribute' THEN 2
                        WHEN 'Attribute Annex' THEN 3
                        WHEN 'Attribute Posit' THEN 4
                        WHEN 'Tie' THEN 5
                        WHEN 'Knot' THEN 6
                        ELSE 7
                     END
               END,
               -- finally alphabetically
               o.[name] ASC
         ) AS [ordinal]
      FROM
         sys.objects o
      JOIN
         sys.schemas s
      ON
         s.[schema_id] = o.[schema_id]
      CROSS APPLY (
         SELECT
            CASE
               WHEN o.[name] LIKE '[_]%'
               COLLATE Latin1_General_BIN THEN 'Metadata'
               WHEN o.[name] LIKE '%[A-Z][A-Z][_][a-z]%[A-Z][A-Z][_][a-z]%'
               COLLATE Latin1_General_BIN THEN 'Tie'
               WHEN o.[name] LIKE '%[A-Z][A-Z][_][A-Z][A-Z][A-Z][_][A-Z]%[_]%'
               COLLATE Latin1_General_BIN THEN 'Attribute'
               WHEN o.[name] LIKE '%[A-Z][A-Z][A-Z][_][A-Z]%'
               COLLATE Latin1_General_BIN THEN 'Knot'
               WHEN o.[name] LIKE '%[A-Z][A-Z][_][A-Z]%'
               COLLATE Latin1_General_BIN THEN 'Anchor'
               ELSE 'Other'
            END
      ) cl ([class])
      CROSS APPLY (
         SELECT
            CASE o.[type]
               WHEN 'P' THEN 'PROCEDURE'
               WHEN 'IF' THEN 'FUNCTION'
               WHEN 'FN' THEN 'FUNCTION'
               WHEN 'V' THEN 'VIEW'
               WHEN 'U' THEN 'TABLE'
            END
      ) ft ([type])
      CROSS APPLY (
         SELECT
            CASE
               WHEN ft.[type] = 'PROCEDURE' AND cl.[class] = 'Anchor' AND o.[name] LIKE 'k%'
               COLLATE Latin1_General_BIN THEN 'key generator'
               WHEN ft.[type] = 'FUNCTION' AND o.[name] LIKE 't%'
               COLLATE Latin1_General_BIN THEN 'time traveler'
               WHEN ft.[type] = 'FUNCTION' AND o.[name] LIKE 'rf%'
               COLLATE Latin1_General_BIN THEN 'restatement finder'
               WHEN ft.[type] = 'FUNCTION' AND o.[name] LIKE 'r%'
               COLLATE Latin1_General_BIN THEN 'rewinder'
               WHEN ft.[type] = 'VIEW' AND o.[name] LIKE 'l%'
               COLLATE Latin1_General_BIN THEN 'latest perspective'
               WHEN ft.[type] = 'FUNCTION' AND o.[name] LIKE 'p%'
               COLLATE Latin1_General_BIN THEN 'point-in-time perspective'
               WHEN ft.[type] = 'VIEW' AND o.[name] LIKE 'n%'
               COLLATE Latin1_General_BIN THEN 'current perspective'
               WHEN ft.[type] = 'FUNCTION' AND o.[name] LIKE 'd%'
               COLLATE Latin1_General_BIN THEN 'difference perspective'
               WHEN ft.[type] = 'VIEW' AND cl.[class] = 'Attribute'
               COLLATE Latin1_General_BIN THEN 'assembled view'
               WHEN ft.[type] = 'TABLE' AND o.[name] LIKE '%Annex'
               COLLATE Latin1_General_BIN THEN 'annex table'
               WHEN ft.[type] = 'TABLE' AND o.[name] LIKE '%Posit'
               COLLATE Latin1_General_BIN THEN 'posit table'
               WHEN ft.[type] = 'TABLE'
               COLLATE Latin1_General_BIN THEN 'table'
               ELSE 'other'
            END
      ) dc ([description])
      CROSS APPLY (
         SELECT
            s.[name] + '.' + o.[name],
            cl.[class] + ' ' + dc.[description]
      ) fn ([name], [description])
      WHERE
         o.[type] IN ('P', 'IF', 'FN', 'V', 'U')
      AND
         o.[name] NOT LIKE ISNULL(@exclusionPattern, '')
   )
   SELECT
      [statement] + CHAR(13) as [text()]
   FROM
      objects
   ORDER BY
      [ordinal]
   FOR XML PATH('');
END