USP_CLASS_MEETING_TEMPLATE_DELETE

CREATE procedure dbo.USP_CLASS_MEETING_TEMPLATE_DELETE
(
    @ID uniqueidentifier,    -- Note this is a representative record, apply to the entire class.

    @CHANGEAGENTID uniqueidentifier
)
as begin
    declare @CLASSID uniqueidentifier
    select @CLASSID = CLASSMEETINGGROUP.CLASSID
    from dbo.CLASSMEETINGGROUP
        inner join dbo.CLASSMEETINGTEMPLATE on CLASSMEETINGGROUP.ID = CLASSMEETINGTEMPLATE.CLASSMEETINGGROUPID
    where CLASSMEETINGTEMPLATE.ID = @ID

    -- use the system generated delete routine to allow proper recording of the deleting agent

    declare @contextCache varbinary(128);
    declare @e int;

    set @contextCache = CONTEXT_INFO();

    /* set CONTEXT_INFO to @CHANGEAGENTID */
    if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID;

    update [PEER_GROUP]
        set [PEER_GROUP].PATTERNBLOCKID = null
    from CLASSMEETINGTEMPLATE [PRIMARY]
        inner join CLASSMEETINGGROUP [PRIMARY_GROUP] on [PRIMARY].CLASSMEETINGGROUPID = [PRIMARY_GROUP].ID
        inner join CLASSMEETINGGROUP [PEER_GROUP] on [PRIMARY_GROUP].CLASSID = [PEER_GROUP].CLASSID
        inner join CLASSMEETINGTEMPLATE [PEER] on [PEER_GROUP].ID = [PEER].CLASSMEETINGGROUPID and
            [PRIMARY].CYCLEDAYID = [PEER].CYCLEDAYID
    where [PRIMARY].ID = @ID
        and [PEER_GROUP].PATTERNBLOCKID is not null

    delete from [PEER]
    from CLASSMEETINGTEMPLATE [PRIMARY]
        inner join CLASSMEETINGGROUP [PRIMARY_GROUP] on [PRIMARY].CLASSMEETINGGROUPID = [PRIMARY_GROUP].ID
        inner join CLASSMEETINGGROUP [PEER_GROUP] on [PRIMARY_GROUP].CLASSID = [PEER_GROUP].CLASSID
        inner join CLASSMEETINGTEMPLATE [PEER] on [PEER_GROUP].ID = [PEER].CLASSMEETINGGROUPID and
            [PRIMARY].CYCLEDAYID = [PEER].CYCLEDAYID
    where [PRIMARY].ID = @ID

    select @e=@@error;

    -- reset CONTEXT_INFO to previous value 

    if not @contextCache is null
        set CONTEXT_INFO @contextCache;

    if @e <> 0
        return 2;

    -- Refresh the class meeting records

    exec dbo.USP_CLASSMEETING_CREATEFROMTEMPLATES @CLASSID, @CHANGEAGENTID

    return 0;    
end