USP_SCHEMA_TRIGGER_SETISSYTEMFLAG

CREATE procedure [dbo].[USP_SCHEMA_TRIGGER_SETISSYTEMFLAG]
    @TABLENAME nvarchar(128),
    @TRIGGERNAME nvarchar(128),
    @PARENTISVIEW bit = 0
as
    declare @value bit;

    select @value = cast(value as bit) from 
    sys.extended_properties as ep 
    inner join sys.triggers as tt on ep.major_id=tt.object_id
    where ep.class=1 
    and ep.minor_id=0 
    and ep.name=N'BB_IsSystem'
    and tt.parent_id=OBJECT_ID(@TABLENAME)
      and tt.name=@TRIGGERNAME;

    declare @parentObjectType nvarchar(max);
    if @PARENTISVIEW = 1 
        set @parentObjectType = N'view';
    else
        set @parentObjectType = N'table';

    if @value is null
        -- property doesn't exist, so add it
        exec sys.sp_addextendedproperty N'BB_IsSystem', '1', N'SCHEMA', N'dbo', @parentObjectType, @TABLENAME, N'trigger', @TRIGGERNAME ;
    else if @value <> 1
        -- property already exists, so update it
        exec sys.sp_updateextendedproperty N'BB_IsSystem', '1', N'SCHEMA', N'dbo', @parentObjectType, @TABLENAME, N'trigger', @TRIGGERNAME ;