SQL触发器与数据约束实例

SQL Server 实现数据约束的检查——使用触发器或约束

今天又看到一个问题：

SQL语句，约束不重叠。
电影院订票，
一行记录是‘陈’，12，19。
（陈的人订了12到19号座位）
那么下一个记录不能重叠，
即下一条记录不能出现12到19中任何数（13到18都不能再出现），
这个约束怎么写啊？

表结构
Create table reservations(
  reserver    char(10) not null primary key,
  start_seat  integer  not null,
  Finish_seat integer  not null
);
go

 

首先确定检查的条件。
IF EXISTS (
  SELECT
    1
  FROM
    reservations r
  WHERE
    r.start_seat <= 新插入的数据
    and r.finish_seat >= 新插入的数据

)
这里的 新插入的数据 ，就是 开始位置 与 结束位置。都要检查
例如
一行记录是‘陈’，12，19
那么新插入一条 ‘李’，1，9 的时候， 1要检查，9也要检查。

 

假如用触发器来做检查：
CREATE TRIGGER tr_iu_reservation ON reservations
FOR INSERT, UPDATE AS
BEGIN
  IF EXISTS (
    SELECT
      1
    FROM
      -- 数据库的表 与 本次插入/更新的数据作关联.
      -- 但是排除掉 本次自己更新的这些数据.
      reservations r
        JOIN inserted i ON (r.reserver != i.reserver)
    WHERE
      ( -- 检查 开始.
        r.start_seat <= i.start_seat
        and r.finish_seat >= i.start_seat )
      OR
      ( -- 检查 结束.
        r.start_seat <= i.finish_seat
        and r.finish_seat >= i.finish_seat )
  )
    -- 如果存在重复的数据，那么回滚掉.
    ROLLBACK;
END
go

插入测试数据.
INSERT INTO reservations VALUES ('张三', 1, 2);
INSERT INTO reservations VALUES ('陈', 12, 19);
GO

-- 开始插入 检查不通过的数据.
1> INSERT INTO reservations VALUES ('李四', 11, 12);
2> GO
消息 3609，级别 16，状态 1，服务器 HOME-BED592453C\SQLEXPRESS，第 1 行
事务在触发器中结束。批处理已中止。
1> INSERT INTO reservations VALUES ('李四', 19, 20);
2> GO
消息 3609，级别 16，状态 1，服务器 HOME-BED592453C\SQLEXPRESS，第 1 行
事务在触发器中结束。批处理已中止。
1> INSERT INTO reservations
2>   SELECT '李四', 19, 20  UNION ALL
3>   SELECT '王五', 21, 22
4> GO
消息 3609，级别 16，状态 1，服务器 HOME-BED592453C\SQLEXPRESS，第 1 行
事务在触发器中结束。批处理已中止。

 

假如要使用约束来处理

那么需要创建一个 FUNCTION 来做数据检查
然后通过创建约束的方式来实现。
先删除掉前面那个触发器
DROP TRIGGER tr_iu_reservation;

创建函数.
CREATE FUNCTION reserExistsCheck(
  @reserver     char(10),
  @start_seat   INT,
  @finish_seat  INT
)
RETURNS INT
AS
BEGIN
  -- 检查的结果.
  DECLARE @CheckResult INT;
  -- 开始检查:
  IF EXISTS (
    SELECT
      1
    FROM
      reservations r
    WHERE
      r.reserver != @reserver
      AND
      (
        ( -- 检查 开始.
          r.start_seat <= @start_seat
          and r.finish_seat >= @start_seat )
        OR
        ( -- 检查 结束.
          r.start_seat <= @finish_seat
          and r.finish_seat >= @finish_seat )
      )
    )
    BEGIN
      -- 存在有重复的数据.
      SET @CheckResult = 1;
    END
  ELSE
    BEGIN
      -- 不存在有重复的数据.
      SET @CheckResult = 0;
    END
  -- 处理完毕. 返回.
  RETURN @CheckResult;
END;
GO

-- 为表新增加约束.
ALTER TABLE reservations
  ADD CONSTRAINT reservations_check
    CHECK (dbo.reserExistsCheck(reserver, start_seat, finish_seat) = 0);
GO

-- 开始插入 检查不通过的数据.
1> INSERT INTO reservations VALUES ('李四', 11, 12);
2> GO
消息 547，级别 16，状态 1，服务器 HOME-BED592453C\SQLEXPRESS，第 1 行
INSERT 语句与 CHECK 约束"reservations_check"冲突。该冲突发生于数据库"test"，表"dbo.reservations"。
语句已终止。
1> INSERT INTO reservations VALUES ('李四', 19, 20);
2> GO
消息 547，级别 16，状态 1，服务器 HOME-BED592453C\SQLEXPRESS，第 1 行
INSERT 语句与 CHECK 约束"reservations_check"冲突。该冲突发生于数据库"test"，表"dbo.reservations"。
语句已终止。
1> INSERT INTO reservations
2>   SELECT '李四', 19, 20  UNION ALL
3>   SELECT '王五', 21, 22
4> GO
消息 547，级别 16，状态 1，服务器 HOME-BED592453C\SQLEXPRESS，第 1 行
INSERT 语句与 CHECK 约束"reservations_check"冲突。该冲突发生于数据库"test"，表"dbo.reservations"。
语句已终止。

 

需要注意的地方：
触发器 与 约束，都要增加一个 排除自己这条记录的条件。
因为 SQL Server 的触发器，是 AFTER 的，也就是数据先记录到表里面以后，再触发的。
如果不排除掉自己这一行记录，那么重复的检查，就肯定能检查到结果。
约束也是一样。数据先到表里面，然后再检查。