TR_PATTERNBLOCKMEETING_INSERTUPDATE_STARTENDTIMEUPDATE

                    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