-- Создаем таблицу для версии коэффициентов
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Table_K_Correction_FLDictionariesVersion')
BEGIN
CREATE TABLE [Table_K_Correction_FLDictionariesVersion] (
    [Id] int NOT NULL CONSTRAINT[PK_Table_K_Correction_FLDictionariesVersion] PRIMARY KEY CLUSTERED([Id] ASC),
    [DateFrom] DATETIME NULL,
    [DateTo] DATETIME NULL,
    [IsDeleted] BIT NOT NULL,
    [DeleteDate] DATETIME NULL,
    [State] INT NULL
);

-- Создаем индекс для версии коэффициентов
CREATE UNIQUE NONCLUSTERED INDEX [IX_Table_K_Correction_FLDictionariesVersion_UNIQUE_DRAFT_STATE] 
    ON [dbo].[Table_K_Correction_FLDictionariesVersion]([State] ASC)
    WHERE ([State] = 3)
    WITH (
		PAD_INDEX = OFF, 
		STATISTICS_NORECOMPUTE = OFF, 
		SORT_IN_TEMPDB = OFF, 
		IGNORE_DUP_KEY = OFF,
		DROP_EXISTING = OFF,
		ONLINE = OFF,
		ALLOW_ROW_LOCKS = ON,
		ALLOW_PAGE_LOCKS = ON
	) 
    ON [PRIMARY];
END


-- Создаем таблицу с коэфф
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Table_K_Correction_FL')
BEGIN
    CREATE TABLE [dbo].[Table_K_Correction_FL](
        [Id] INT NOT NULL IDENTITY(1,1),
		[CashbookIDPartner] INT,
		[BranchCode] VARCHAR(255),
		[IsProlongation] BIT,
		[ChannelGroup] VARCHAR(255),
		[Channel] VARCHAR(255),
		[NewTS] BIT,
		[Brand] VARCHAR(255),
		[Model] VARCHAR(255),
		[K_Correction_FL] DECIMAL(10, 4),
        [VersionId] INT NULL,
        CONSTRAINT [PK_Table_K_Correction_FL]
        PRIMARY KEY CLUSTERED([Id] ASC)
        WITH (
            PAD_INDEX = OFF,
            STATISTICS_NORECOMPUTE = OFF,
            IGNORE_DUP_KEY = OFF,
            ALLOW_ROW_LOCKS = ON,
            ALLOW_PAGE_LOCKS = ON
        )
    )
    ON [PRIMARY];
    
	-- Создаем вторичный ключ на таблицу с версиями коэфф
    ALTER TABLE [dbo].[Table_K_Correction_FL]
    WITH CHECK ADD CONSTRAINT [FK_Table_K_Correction_FL_DictVer_VId]
    FOREIGN KEY([VersionId])
    REFERENCES [dbo].[Table_K_Correction_FLDictionariesVersion] ([Id]);

    ALTER TABLE [dbo].[Table_K_Correction_FL]
    CHECK CONSTRAINT [FK_Table_K_Correction_FL_DictVer_VId];
END


-- Создаем индекс на поля для поиска
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = N'IDX_CashbookIDPartner' AND object_id = OBJECT_ID(N'Table_K_Correction_FL'))
BEGIN
    CREATE NONCLUSTERED INDEX IDX_CashbookIDPartner ON Table_K_Correction_FL(CashbookIDPartner);
END;

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = N'IDX_BranchCode' AND object_id = OBJECT_ID(N'Table_K_Correction_FL'))
BEGIN
    CREATE NONCLUSTERED INDEX IDX_BranchCode ON Table_K_Correction_FL(BranchCode);
END;

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = N'IDX_IsProlongation' AND object_id = OBJECT_ID(N'Table_K_Correction_FL'))
BEGIN
    CREATE NONCLUSTERED INDEX IDX_IsProlongation ON Table_K_Correction_FL(IsProlongation);
END;

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = N'IDX_ChannelGroup' AND object_id = OBJECT_ID(N'Table_K_Correction_FL'))
BEGIN
    CREATE NONCLUSTERED INDEX IDX_ChannelGroup ON Table_K_Correction_FL(ChannelGroup);
END;

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = N'IDX_Channel' AND object_id = OBJECT_ID(N'Table_K_Correction_FL'))
BEGIN
    CREATE NONCLUSTERED INDEX IDX_Channel ON Table_K_Correction_FL(Channel);
END;

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = N'IDX_NewTS' AND object_id = OBJECT_ID(N'Table_K_Correction_FL'))
BEGIN
    CREATE NONCLUSTERED INDEX IDX_NewTS ON Table_K_Correction_FL(NewTS);
END;

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = N'IDX_Brand' AND object_id = OBJECT_ID(N'Table_K_Correction_FL'))
BEGIN
    CREATE NONCLUSTERED INDEX IDX_Brand ON Table_K_Correction_FL(Brand);
END;

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = N'IDX_Model' AND object_id = OBJECT_ID(N'Table_K_Correction_FL'))
BEGIN
    CREATE NONCLUSTERED INDEX IDX_Model ON Table_K_Correction_FL(Model);
END;

GO 

-- Создаем триггер на запрет случайной(ручной) вставки в таблицу с версиями коэффициентов
CREATE OR ALTER TRIGGER [dbo].[Table_K_Correction_FLDictionariesVersion_ON_SYNC_INSERT] 
                ON [dbo].[Table_K_Correction_FLDictionariesVersion] 
                AFTER INSERT AS
                BEGIN
                    IF NOT EXISTS (
                       SELECT 1 FROM dbo.[EnvironmentSyncSettings] 
                       WHERE [Name] = 'Configurator' 
                       AND [IsDeleted] = 0 
                       AND [IsEditable] = 1
                    )
                        IF EXISTS (SELECT ID FROM inserted)
                        BEGIN
                            RAISERROR ('Cannot insert record into [dbo].Table_K_Correction_FLDictionariesVersion] in readonly environment.', 16, 1)
                        END
                END
GO 

-- Вставляем данные в табличку с версиями коэффициентов
IF NOT EXISTS (SELECT  * FROM Table_K_Correction_FLDictionariesVersion)
BEGIN
	-- Отключаем триггер
	IF EXISTS (SELECT 1 FROM sys.triggers WHERE Name = 'Table_K_Correction_FLDictionariesVersion_ON_SYNC_INSERT') 
		DISABLE TRIGGER [dbo].[Table_K_Correction_FLDictionariesVersion_ON_SYNC_INSERT] 
		ON [dbo].Table_K_Correction_FLDictionariesVersion;

	-- Создаем первую версию коэффициентов
    INSERT INTO Table_K_Correction_FLDictionariesVersion(
        Id,
        DateFrom,
        IsDeleted,
        State
    )
    VALUES(
        1,
        getdate(),
        0,
        2
    )

	-- Включаем обратно триггер
    IF EXISTS (SELECT 1 FROM sys.triggers WHERE Name = 'Table_K_Correction_FLDictionariesVersion_ON_SYNC_INSERT')
        ENABLE TRIGGER [dbo].[Table_K_Correction_FLDictionariesVersion_ON_SYNC_INSERT]
        ON [dbo].Table_K_Correction_FLDictionariesVersion;
END;

Как удалить созданные таблицы?