TR_COURSERESTRICTIONSTARTTERM_INSERTDELETE_DELETECOURSEGRADINGMARKINGCOLUMN

--similar trigger is on MARKINGCOLUMNTERM, COURSERESTRICTION tables

CREATE trigger TR_COURSERESTRICTIONSTARTTERM_INSERTDELETE_DELETECOURSEGRADINGMARKINGCOLUMN 
on dbo.COURSERESTRICTIONSTARTTERM                    
after insert, delete
not for replication        
as begin

    declare @ChangeAgentID uniqueidentifier
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENTFROMCONTEXT @ChangeAgentID output

    --Cache current context information 

    declare @CONTEXTCACHE varbinary(128); 
    set @CONTEXTCACHE = CONTEXT_INFO(); 

    --Set CONTEXT_INFO to @CHANGEAGENTID 

    if @CHANGEAGENTID is not null 
      set CONTEXT_INFO @CHANGEAGENTID; 

    delete dbo.COURSEGRADINGMARKINGCOLUMN 
    from dbo.COURSEGRADINGMARKINGCOLUMN
    inner join dbo.COURSEGRADING 
        on COURSEGRADINGMARKINGCOLUMN.COURSEGRADINGID = COURSEGRADING.ID                            
    inner join dbo.COURSE 
        on COURSEGRADING.COURSEID = COURSE.ID
    inner join dbo.COURSERESTRICTION 
        on COURSEGRADING.STARTDATE = COURSERESTRICTION.STARTDATE and COURSEGRADING.ENDDATE = COURSERESTRICTION.ENDDATE and COURSEGRADING.COURSEID = COURSERESTRICTION.COURSEID
    inner join dbo.[SESSION] 
        on [SESSION].ID = dbo.UFN_SESSION_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, COURSEGRADING.STARTDATE, COURSEGRADING.ENDDATE)
    inner join dbo.MARKINGCOLUMN 
        on COURSEGRADINGMARKINGCOLUMN.MARKINGCOLUMNID = MARKINGCOLUMN.ID
    left outer join inserted I 
        on I.COURSERESTRICTIONID = COURSERESTRICTION.ID
    left outer join deleted D 
        on D.COURSERESTRICTIONID = COURSERESTRICTION.ID
    where (I.COURSERESTRICTIONID is not null or D.COURSERESTRICTIONID is not null) 
        and MARKINGCOLUMN.ALLTERMS = 0 --specific terms

        and not exists 
            (select 1 
                from dbo.COURSERESTRICTIONSTARTTERM
                inner join dbo.COURSERESTRICTION 
                    on COURSERESTRICTIONSTARTTERM.COURSERESTRICTIONID = COURSERESTRICTION.ID
                inner join dbo.TERM 
                    on TERM.STARTDATE >= COURSERESTRICTIONSTARTTERM.STARTDATE 
                        and TERM.ENDDATE <= 
                            (select max(CRTERMS.ENDDATE) 
                             from (select top (COURSERESTRICTION.LENGTHINTERMS) ENDDATE 
                                   from dbo.TERM
                                   where  
                                       TERM.SESSIONID = [SESSION].ID and 
                                       TERM.ENDDATE >= COURSERESTRICTIONSTARTTERM.ENDDATE
                                   order by TERM.ENDDATE asc) CRTERMS)
                        and TERM.SESSIONID = [SESSION].ID
                inner join dbo.MARKINGCOLUMNTERM on TERM.TERMNAMECODEID = MARKINGCOLUMNTERM.TERMNAMECODEID
                where COURSERESTRICTION.ID = IsNull(I.COURSERESTRICTIONID,D.COURSERESTRICTIONID)
                    and MARKINGCOLUMNTERM.MARKINGCOLUMNID = MARKINGCOLUMN.ID)

    --Reset CONTEXT_INFO to previous value 

    if not @CONTEXTCACHE is null 
      set CONTEXT_INFO @CONTEXTCACHE;                              
end