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