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;
私は実務でこういった要件にあたったことがないのですが、実際のところはどうしてるんですかね。