TR_MARKINGCOLUMNTERM_INSERTUPDATEDELETE_DELETECOURSEGRADINGMARKINGCOLUMN

                    --similar trigger is on COURSERESTRICTION, COURSERESTRICTIONSTARTTERM tables

                    CREATE trigger TR_MARKINGCOLUMNTERM_INSERTUPDATEDELETE_DELETECOURSEGRADINGMARKINGCOLUMN 
                    on dbo.MARKINGCOLUMNTERM                    
                    after insert, update, 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.MARKINGCOLUMN 
                            on COURSEGRADINGMARKINGCOLUMN.MARKINGCOLUMNID = MARKINGCOLUMN.ID
                        left outer join deleted D
                            on MARKINGCOLUMN.ID = D.MARKINGCOLUMNID
                        left outer join inserted I
                            on MARKINGCOLUMN.ID = I.MARKINGCOLUMNID
                        where (D.MARKINGCOLUMNID is not null or I.MARKINGCOLUMNID is not null) and 
                            MARKINGCOLUMN.ALLTERMS = 0 and
                            not exists 
                                (select * from dbo.UFN_COURSERESTRICTION_VALIDDATESBYLENGTHINTERMS(COURSEGRADING.STARTDATE, COURSEGRADING.ENDDATE, COURSEGRADING.COURSEID) CRTERMS
                                inner join dbo.TERM on TERM.STARTDATE >= CRTERMS.STARTDATE and TERM.ENDDATE <= CRTERMS.ENDDATE
                                inner join dbo.SESSION on TERM.SESSIONID = SESSION.ID and SESSION.ID = dbo.UFN_SESSION_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, COURSEGRADING.STARTDATE, COURSEGRADING.ENDDATE)
                                inner join dbo.MARKINGCOLUMNTERM on TERM.TERMNAMECODEID = MARKINGCOLUMNTERM.TERMNAMECODEID
                                where MARKINGCOLUMNTERM.MARKINGCOLUMNID = MARKINGCOLUMN.ID)

                        --Reset CONTEXT_INFO to previous value 

                        if not @CONTEXTCACHE is null 
                          set CONTEXT_INFO @CONTEXTCACHE;

                    end