IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[IES].[T_PLAN_SECTION_AfterInsert]'))
DROP TRIGGER [IES].[T_PLAN_SECTION_AfterInsert]
GO

--Добавление записей:
CREATE TRIGGER [IES].[T_PLAN_SECTION_AfterInsert] ON [IES].[T_PLAN_SECTION]
AFTER INSERT 
AS
BEGIN
    declare @fieldName nvarchar(50)
           ,@primaryKey uniqueidentifier
           ,@keySchema nvarchar(255)
           ,@keyTable nvarchar(255)
           ,@keyColumn nvarchar(100)
           ,@keyType nvarchar(100)
           ,@keySize nvarchar(11)
           ,@baseEntityID int
           ,@sqltext nvarchar(max)
           ,@isUnique bit
           ,@isRequired bit

    select top 1
           @baseEntityID = e.BaseEntity
          ,@fieldName = ps.SectionAlias
          ,@primaryKey = ea.EntitiesAttributesId
          ,@keyType = ea.SystemType
          ,@keySize = cast(ISNULL(ea.MaxLength, 255) as nvarchar(max))
          ,@isUnique = cast(case when ps.ClassificationType = 0 then 1 else 0 end as bit)
          ,@isRequired = ps.IsRequired
    from inserted ps
        join VCLib.T_ENTITIES e on e.EntityID = ps.Entities
        left join VCLib.T_ENTITIES_ATTRIBUTES ea on ea.EntityID = e.EntityID and ea.IsPrimaryKey = 1

    WHILE(@baseEntityID is not null)
            select @baseEntityID = e.BaseEntity
                  ,@primaryKey = ea.EntitiesAttributesId
                  ,@keyType = ea.SystemType
                  ,@keySize = cast(ISNULL(ea.MaxLength, 255) as nvarchar(max))
            from VCLib.T_ENTITIES e
                join VCLib.T_ENTITIES_ATTRIBUTES ea on ea.EntityID = e.EntityID and ea.IsPrimaryKey = 1
    where e.EntityID = @baseEntityID

    select @keyType = case
                          when @keyType = 'guid' then N'uniqueidentifier'
                          when @keyType = 'int' then N'int'
                          when @keyType = 'string' then N'varchar(' + @keySize + N')'
                          when @keyType = 'long' then N'bigint'
                      end
    if (@keyType is null)
        raiserror('Идентификатор справочника не поддерживается', 16, 1)

    select top 1 
            @keyTable = ea.MappingTable
           ,@keyColumn = ea.MappingName
           ,@keySchema = e.[Schema]
    from VCLib.T_ENTITIES_ATTRIBUTES ea
        join VCLib.T_ENTITIES e on ea.EntityID = e.EntityID
    where ea.EntitiesAttributesId = @primaryKey

    set @sqltext = 
    (
        select N'ALTER TABLE IES.' + ts.TableName + N' ADD ' + @fieldName + N' ' + @keyType + ts.IsRequired + N';'
             + case when ts.IsUnique = 0 or @isRequired = 1 then N'ALTER TABLE IES.' + ts.TableName + N' ADD CONSTRAINT FK_' + ts.TableName + N'_' + @fieldName 
                                                               + N' FOREIGN KEY (' + @fieldName + N') REFERENCES ' + @keySchema + N'.' + @keyTable + N' (' + @keyColumn + N');'
                    else N'' end
        from (
                      select N'T_PLAN_CLASSIFICATION' as TableName, N' null' IsRequired, cast(1 as bit) as IsUnique, cast(0 as bit) as ParamOnly
                union select N'T_SCHET_ITEM_FAKT', N' null', 0, 0
                union select N'T_ITEM_PLAN_MODIFICATION', N' null', 0, 0
             ) ts
        where (@isUnique = 1 and ts.ParamOnly = 0) or (@isUnique = 0 and ts.IsUnique = 0)
        for xml path('')
    )
    exec sp_executesql @sqltext
    TRUNCATE TABLE IES.T_PLAN_CLASSIFICATION
END
GO
