読者です 読者をやめる 読者になる 読者になる

T-SQLのトリガーで時間帯(期間)の重なりを防ぐ

タイトルが微妙で後から自分が見ても、ん?なんだっけ?ってなりそうな気も・・・。

それはさておき、会議室とかを予約するようなシステムで「誰かがある会議室のある時間帯を予約したら、他の人はその時間帯を予約できない」といった制御をSQLだけでどうするのかなーと気になって、トリガーを使うとできるのかなー?ってことで試してみました。
(トリガーを使ったことがなかったので、試してみたかったってのもあります。)

テーブルとトリガーの作成

まずは予約テーブル。
とりあえずは開始日時と終了日時だけを持つ単純なものにしておきます。

create table Reservation(
    Id int identity,
    StartAt datetime2(0) not null,
    EndAt datetime2(0) not null,
    constraint PK_Reservation primary key(Id),
    constraint CK_Reservation check(StartAt < EndAt));

そして、今回のメインのトリガーはこんな感じになります。

create trigger ReservationTrigger
on Reservation
for insert, update
as
    -- 時間帯が重なるレコートがあればロールバック
    if exists(
        select *
        from inserted
            inner join Reservation on inserted.Id != Reservation.Id
        where
            inserted.StartAt < Reservation.EndAt and
            inserted.EndAt > Reservation.StartAt)
    begin
        rollback;
    end

insertedは、insert文やupdate文で影響を受けた行のコピーが格納される一時的なテーブルです。 このinsertedの各行の時間帯と重なりがあるReservationテーブルの各行を探して、存在していればロールバックするって感じです。

トリガーについてはこのあたりを参考にしました。

追加と更新を試す

さきほどのトリガーを作成しておくと、Reservationテーブルにこういったデータがある場合に、

Id          StartAt                     EndAt
----------- --------------------------- ---------------------------
1           2013-04-07 10:00:00         2013-04-07 12:00:00
2           2013-04-08 10:00:00         2013-04-08 12:00:00

次のようなinsert文やupdate文を実行しても、時間帯が重なる行が存在するため、追加・更新に失敗するようになります。

insert into Reservation
values('2013/04/07 9:00', '2013/04/07 11:00');

update Reservation
set StartAt = '2013/04/07 11:00'
where Id = 2;

私は実務でこういった要件にあたったことがないのですが、実際のところはどうしてるんですかね。