Oracle-如何强制用户插入多行 [英] Oracle - How to force user to INSERT multiple row

问题描述

我在学术作业中使用Oracle 11gR2.一个房间必须有3-5个人是有一个限制的.我知道我可以编写这样的触发器来检查一个房间是否有超过5个人:

I am using Oracle 11gR2 on an Academic Assignment. There is a constraint which a room must have 3 - 5 people. I know that I can write a Trigger like this to check if a room has more than 5 people:

CREATE TABLE People
(
  PID INTEGER PRIMARY KEY
);

CREATE TABLE Room
(
  RID INTEGER PRIMARY KEY
);

CREATE TABLE Living
(
  RID INTEGER,
  PID INTEGER,
  CONSTRAINT Living_PK PRIMARY KEY (RID, PID),
  CONSTRAINT Living_FK_RID FOREIGN KEY (RID) REFERENCES Room(RID),
  CONSTRAINT Living_FK_PID FOREIGN KEY (PID) REFERENCES People(PID)
);

CREATE OR REPLACE TRIGGER Living_BIU
  BEFORE INSERT OR UPDATE ON Living
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
DECLARE
  Count NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO Count
    FROM Living
    WHERE RID = :NEW.RID;
  IF(Count > 5)
  THEN
    RAISE_APPLICATION_ERROR(-20002, 'Too many people in a room.');
  END IF;
END Living_BI;

但是我不能检查数字是否小于3，因为那样我就不能在Living中插入任何东西.所以我的问题是如何创建一个强制用户一次插入多于3行且少于5行的触发器?

But I can not check if the number is lesser than 3 because then I can not insert any things into Living. So my question is how can I create a trigger that force user to insert more than 3 rows and less than 5 rows at a time?

推荐答案

使用标准序言，这不是您在现实世界中真正做这种事情的方式...

With the standard preface that this isn't how you would really do this sort of thing in the real world...

您实际上需要在此处使用语句级触发器.如果您不介意每次检查每个房间都会带来性能上的损失

You would realistically need to use a statement-level trigger here. If you don't mind the performance hit of checking every room every time

CREATE OR REPLACE TRIGGER Living_AIUD
  AFTER INSERT OR UPDATE OR DELETE
  ON Living
DECLARE
  Count NUMBER;
BEGIN
  FOR x IN (SELECT rid, count(*) cnt
              FROM living
             GROUP BY rid
            HAVING COUNT(*) < 3)
  LOOP
    RAISE_APPLICATION_ERROR(-20002, 'Too few people in room ' || x.rid);
  END LOOP;
END Living_AIUD;

如果您不想每次都在每个房间都检查一次，则需要一个包含rid值的集合，初始化该集合的before语句触发器以及添加的行级触发器的程序包.集合的:new.rid值.然后，您的after语句触发器将遍历集合中的元素，并仅检查这些房间中的人数.

If you don't want to check this for every room every time, you'd need a package with a collection of rid values, a before statement trigger that initialized the collection, and a row-level trigger that added the :new.rid value to the collection. Your after statement trigger would then iterate over the elements in the collection and check the number of people in just those rooms.

这篇关于Oracle-如何强制用户插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！