トリガーで時間帯の重なりを防ぐ （独自の制約のようなものを作る）

gistfile1.cs

using System;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Dapper;

namespace UnitTest {
	[TestClass]
	public class Test {
		private readonly string _connectionString =
			new SqlConnectionStringBuilder {
				DataSource = ".",
				InitialCatalog = "Test",
				IntegratedSecurity = true,
			}.ConnectionString;

		private SqlConnection _connection;


		[TestInitialize]
		public void Prepare() {
			_connection = new SqlConnection(_connectionString);
			_connection.Open();

			_connection.Execute(@"
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));");

			_connection.Execute(@"
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;");

			// 初期データ
			_connection.Execute(@"
insert into Reservation
values
	('2013/04/07 10:00', '2013/04/07 12:00'),
	('2013/04/08 10:00', '2013/04/08 12:00');");
		}

		[TestCleanup]
		public void Clear() {
			_connection.Execute(@"
drop table Reservation;");
		}

		// insert
		[TestMethod]
		public void TestInsert1() {
			var actual = _connection.Execute(@"
insert into Reservation values('2013/04/07 8:00', '2013/04/07 9:00');");
			Assert.AreEqual(1, actual);
		}

		[TestMethod]
		public void TestInsert2() {
			var actual = _connection.Execute(@"
insert into Reservation values('2013/04/07 9:00', '2013/04/07 10:00');");
			Assert.AreEqual(1, actual);
		}

		[TestMethod]
		public void TestInsert3() {
			var actual = _connection.Execute(@"
insert into Reservation values('2013/04/07 12:00', '2013/04/07 13:00');");
			Assert.AreEqual(1, actual);
		}

		[TestMethod]
		public void TestInsert4() {
			var actual = _connection.Execute(@"
insert into Reservation values('2013/04/07 13:00', '2013/04/07 14:00');");
			Assert.AreEqual(1, actual);
		}

		// insert失敗 -> rollback
		[TestMethod]
		public void TestInsertFailedRollback() {
			var failed = false;
			try {
				_connection.Execute(@"
insert into Reservation values('2013/04/07 9:00', '2013/04/07 11:00');");
			} catch(SqlException) {
				failed = true;
			}
			Assert.IsTrue(failed);

			var count = _connection
				.Query<int>(@"select count(*) from Reservation;")
				.First();
			Assert.AreEqual(2, count);
		}

		// insert失敗 * 4
		[TestMethod]
		[ExpectedException(typeof(SqlException))]
		public void TestInsertFailed1() {
			_connection.Execute(@"
insert into Reservation values('2013/04/07 9:00', '2013/04/07 11:00');");
		}

		[TestMethod]
		[ExpectedException(typeof(SqlException))]
		public void TestInsertFailed2() {
			_connection.Execute(@"
insert into Reservation values('2013/04/07 10:30', '2013/04/07 11:30');");
		}

		[TestMethod]
		[ExpectedException(typeof(SqlException))]
		public void TestInsertFailed3() {
			_connection.Execute(@"
insert into Reservation values('2013/04/07 11:00', '2013/04/07 13:00');");
		}

		[TestMethod]
		[ExpectedException(typeof(SqlException))]
		public void TestInsertFailed4() {
			_connection.Execute(@"
insert into Reservation values('2013/04/07 9:00', '2013/04/07 13:00');");
		}

		// update
		[TestMethod]
		public void TestUpdate() {
			var actual = _connection.Execute(@"
update Reservation set StartAt = '2013/04/07 13:00' where Id = 2;");
			Assert.AreEqual(1, actual);
		}

		// udpate失敗
		[TestMethod]
		[ExpectedException(typeof(SqlException))]
		public void TestUpdateFailed() {
			_connection.Execute(@"
update Reservation set StartAt = '2013/04/07 11:00' where Id = 2;");
		}

		// 複数insert
		[TestMethod]
		public void TestInsertMany() {
			var actual = _connection.Execute(@"
insert into Reservation
values
	('2013/04/07 13:00', '2013/04/07 15:00'),
	('2013/04/08 13:00', '2013/04/08 15:00');");
			Assert.AreEqual(2, actual);
		}
	}
}