CREATE trigger [dbo].[TR_PATTERNBLOCKMEETING_INSERTUPDATE_STARTENDTIMEUPDATE]
on [dbo].[PATTERNBLOCKMEETING]
for update, insert
as begin
set nocount on
if update(TIMETABLEDAYPERIODID)
begin
declare @CURRENTDATE datetime
set @CURRENTDATE = GetDate()
begin try
update PATTERNBLOCKMEETING
set STARTTIME = TDP.STARTTIME,
ENDTIME =
(select ENDTIME from
(
select ENDTIME, ROW_NUMBER() OVER (ORDER BY TIMETABLEDAYPERIOD.STARTTIME) as 'ROW_NUMBER'
from
TIMETABLEDAYPERIOD
where
TIMETABLEDAYPERIOD.TIMETABLEDAYID = PATTERNBLOCKMEETING.TIMETABLEDAYID
and TIMETABLEDAYPERIOD.STARTTIME >= TDP.STARTTIME
) as endrow
where
endrow.ROW_NUMBER = inserted.LENGTHINPERIODS),
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = inserted.CHANGEDBYID
from
PATTERNBLOCKMEETING
inner join
inserted on inserted.ID = PATTERNBLOCKMEETING.ID
inner join
TIMETABLEDAYPERIOD TDP on TDP.ID = PATTERNBLOCKMEETING.TIMETABLEDAYPERIODID
end try
begin catch
exec dbo.USP_RAISE_ERROR
end catch
end
if (exists (select PATTERNBLOCKMEETING.ID
from
PATTERNBLOCKMEETING
inner join
inserted on inserted.ID = PATTERNBLOCKMEETING.ID
where
PATTERNBLOCKMEETING.STARTTIME IS NULL OR PATTERNBLOCKMEETING.ENDTIME IS NULL))
raiserror('BBERR_PATTERNBLOCKMEETING_TIME_CANNOTBENULL', 13, 1)
if (exists (select PATTERNBLOCKMEETING.ID
from
PATTERNBLOCKMEETING
inner join
inserted on inserted.ID = PATTERNBLOCKMEETING.ID
where
PATTERNBLOCKMEETING.STARTTIME >= PATTERNBLOCKMEETING.ENDTIME))
raiserror('BBERR_PATTERNBLOCKMEETING_ENDTIMEBEFORESTARTTIME', 13, 1)
if (exists (select A.ID
from
PATTERNBLOCKMEETING A
inner join
PATTERNBLOCKMEETING B on A.PATTERNBLOCKID = B.PATTERNBLOCKID
where
A.ID <> B.ID and A.TIMETABLEDAYID = B.TIMETABLEDAYID and
((A.STARTTIME > B.STARTTIME and A.STARTTIME < B.ENDTIME)
or (A.ENDTIME > B.STARTTIME and A.ENDTIME < B.ENDTIME))))
raiserror('BBERR_PATTERNBLOCKMEETING_TIMES_OVERLAP', 13, 1)
if (exists (select A.ID
from
PATTERNBLOCKMEETING A
inner join
PATTERNBLOCKMEETING B on A.PATTERNBLOCKID = B.PATTERNBLOCKID
where
A.ID <> B.ID and A.TIMETABLEDAYID = B.TIMETABLEDAYID and
A.STARTTIME = B.STARTTIME and A.ENDTIME = B.ENDTIME))
raiserror('BBERR_PATTERNBLOCKMEETING_UNIQUE', 13, 1)
end
|
Trigger Definition
