--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
|
Trigger Definition
