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

CREATE TRIGGER [IES].[T_PLAN_SECTION_BeforeDelete] ON [IES].[T_PLAN_SECTION]
AFTER DELETE
AS
BEGIN
    declare @sqltext nvarchar(max)

    select @sqltext = 
    (
        select case when ts.IsUnique = 0 or d.IsRequired = 1 then N'if exists(select * from sys.foreign_keys fk join sys.tables t on t.object_id = fk.parent_object_id where t.name = ''' + ts.Tablename + N''')'
																+ N'ALTER TABLE IES.' + ts.TableName + N' DROP CONSTRAINT FK_' + ts.TableName + N'_' + d.SectionAlias + N';'
               else N'' end
               + N'ALTER TABLE IES.' + ts.TableName + N' DROP COLUMN ' + d.SectionAlias + N';'
        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, deleted d
        where (d.ClassificationType = 0 and ts.ParamOnly = 0) or (d.ClassificationType = 1 and ts.IsUnique = 0)
        for xml path('')
    )
    exec sp_executesql @sqltext;
    TRUNCATE TABLE IES.T_PLAN_CLASSIFICATION
END
