时间戳

首页 > 解决方案 > SQL：触发器以防止根据条件将行插入表中

sql - SQL：触发器以防止根据条件将行插入表中

问题描述

我有以下表格：

CREATE TABLE review 
(
    review_id NUMBER(2) NOT NULL,
    review_date DATE NOT NULL,
    review_rating NUMBER(1) NOT NULL,
    driver_no NUMBER(2) NOT NULL,
    vehicle_id NUMBER(3) NOT NULL
);

CREATE TABLE testing 
(
    testing_id NUMBER(2) NOT NULL,
    testing_start DATE NOT NULL,
    testing_end DATE NOT NULL
    driver_no NUMBER(2) NOT NULL,
    vehicle_id NUMBER(3) NOT NULL
);

基本上车辆在两个日期之间由司机进行测试。测试完成后，驾驶员检查车辆。

我想创建一个触发器，以防止添加无效评论。如果驾驶员在测试结束日期之前对车辆进行了审查，则该审查无效。如果驾驶员对他没有驾驶过的车辆进行评论，则该评论也无效。

例如，驾驶员 1 从 2019 年 2 月 1 日到 2019 年 2 月 7 日测试车辆 7。如果添加了 2019 年 2 月 5 日的评论，我希望触发器防止插入此内容。此外，如果添加了对车辆 5 的评论（当车辆 7 是正在测试的车辆时），我希望触发器防止插入。

这是我到目前为止所拥有的：

CREATE OR REPLACE TRIGGER review_check_validity
AFTER INSERT ON review
FOR EACH ROW
BEGIN
    SELECT testing_start
    FROM testing
    WHERE driver_no = :new.driver_no;

    SELECT vehicle_id
    FROM testing
    WHERE driver_no = :new.driver_no;

    IF :new.review_date < testing_end THEN
    raise_application_error(-20000, 'Review date cannot be before 
    testing end date');

    END IF;

    IF :new.vehicle_id != vehicle_id THEN
    raise_application_error(-20000, 'Driver has never driven this 
    vehicle');

    END IF;
END;
/

触发器编译时没有任何错误 - 但是当我尝试通过在 REVIEW 表中插入无效行来测试它时，我收到一条错误消息，说明

精确提取返回超过请求的行数

有人可以指出我需要对代码进行哪些更改才能达到预期的结果吗？

标签： sqloracledatabase-trigger

解决方案

我会在这里重新安排逻辑，然后：

  1. 检查司机是否对车辆进行了测试，然后
  2. 检查是否在车辆的测试结束日期之前尝试进行审查（您遗漏了一些内容）。

在包含触发器代码的 Oracle PL/SQL 中，您不能只SELECT. 你必须有SELECT INTO一个变量。然后您可以在逻辑中使用该变量。

同样重要的是，当您SELECT INTO使用一个变量时，查询只能返回一个结果。多行将触发您遇到的错误。

CREATE OR REPLACE TRIGGER review_check_validity
AFTER INSERT ON review
FOR EACH ROW
DECLARE
    testEnd DATE;
    vehicleTestCount NUMBER;
BEGIN

    SELECT COUNT(*)
      INTO vehicleTestCount
      FROM testing
      WHERE vehicle_id = :new.vehicle_id;

    IF vehicleTestCount = 0 THEN
      raise_application_error(-20000, 'Driver has never driven this vehicle');
    END IF;

    -- Assumes one test per driver per vehicle
    SELECT testing_end
      INTO testEnd
      FROM testing
      WHERE driver_no = :new.driver_no
        AND vehicle_id = :new.vehicle_id;

    IF :new.review_date < testEnd THEN
      raise_application_error(-20000, 'Review date cannot be before 
    testing end date');

    END IF;

END;
/

最后，您的表结构允许同一驾驶员对同一车辆进行多次测试。如果它应该允许这样做，那么表格应该通过而不是andreview链接到表格。testingtesting_iddriver_novehicle_id

推荐阅读