sql - PL/SQL DB 触发器 - 如果不满足给定条件，则插入失败

Question

我有一个用于学习 SQL 的小型测试数据库。
有一个 Duel 表，其中包含两个 Pilot 外键(决斗者)。我想检查决斗者在插入之前是否还没有“见面”。

伪代码:

before insertion on Duel
for each row already in the table
  if ((new_row.numpilot1 = old_row.numpilot1 and new_row.numpilot2 = old_row.numpilot2) OR
      (new_row.numpilot1 = old_row.numpilot2 and new_row.numpilot2 = old_row.numpilot1)
     )
    insertion fails

另一种选择是

tempnum integer;
select numpilot1 into tempnum from duel 
where (:NEW.numpilot1 = numpilot1 and :NEW.numpilot2 = numpilot2) OR
      (:NEW.numpilot1 = numpilot2 and :NEW.numpilot2 = numpilot1);

if tempnum == null
    fail insertion

什么是 PL/SQL (Oracle DBMS) 版本？

Answer 1

通常情况下，您不会对此类要求使用触发器。相反，您将在表上创建几个约束。我建议对 ( numpilot1 , numpilot2 ) 使用唯一约束以及确保 numpilot1 < numpilot2 的检查约束。 .

ALTER TABLE duel
  ADD CONSTRAINT unique_pilot_combination
          UNIQUE( numpilot1, numpilot2 );

ALTER TABLE duel
  ADD CONSTRAINT chk_pilot1_lt_pilot2
           CHECK( numpilot1_fk < numpilot2_fk );

如果你想在触发器中做这种事情，那会有点复杂。通常，DUEL 上的行级触发器无法查询 DUEL表——这样做会创建一个变异表异常。您需要在包中创建一个集合、一个用于初始化集合的 before 语句触发器、一个将新的引导键插入集合的行级触发器，以及一个读取集合中的数据并执行验证。除了潜在的性能影响之外，还有很多需要管理的移动部件。但是，如果您真的坚持使用触发解决方案，可以使用 using the three trigger solution to work around mutating table exceptions 的示例。在 Tim Hall 的网站上。