Oracle - 使用触发器删除行

Question

当插入Condo_assign时，我使用多个触发器将条目添加到reserveError表中以指示错误是什么。在他们登录reserveError后，我现在尝试使用另一个触发器从导致问题的condo_assign中删除记录。 基本上，错误的插入应该记录在 保留错误 并从Condo_assign中删除。 问题是，虽然我的删除触发器编译并且没有导致任何问题，但它似乎没有做任何事情。 当我select * from condo_assign错误条目仍然存在时。

Condo_Assign表：

CREATE TABLE Condo_Assign (
  MID INT
, RID VARCHAR2(3)
, CONSTRAINT Condo_Assign Primary Key (MID,RID)
, CONSTRAINT MID_Assign_FK Foreign Key (MID) references SkiClub (MID)
, CONSTRAINT RID_Assign_FK Foreign Key (RID) references Condo_Reservation (RID)
);

储备错误表：

CREATE TABLE ReserveError (
Err INT PRIMARY KEY
, MID INT
, RID VARCHAR2(3)
, errorDate DATE
, errorCode VARCHAR2(6)
, errorMsg VARCHAR2(60)
, CONSTRAINT Error_MID_FK FOREIGN KEY (MID) REFERENCES SkiClub
, CONSTRAINT Error_RID_FK FOREIGN KEY (RID) REFERENCES Condo_Reservation
);

导致触发器触发的过程：

CREATE OR REPLACE Procedure addCondo_Assign
(
inMID in Condo_Assign.MID%type
, inRID in Condo_Assign.RID%type
, inPaymentDate in Payment.PaymentDate%type
, inPayment in Payment.Payment%type
)
is
begin
insert into Condo_Assign (MID,RID) values (inMid,inRid);
IF inPayment >= 50 then 
    insert into Payment (MID,RID,PaymentDate,Payment) values (inMID,inRID,inPaymentDate,inPayment);
ELSE
    raise_application_error(-20088,'Deposit less than 50');
end if;
exception
    when others then
    raise_application_error(-20005,'Cannot add to entry to Condo_Assign Table.');
end addCondo_Assign;
/

写入保留错误表的触发器

-- Trigger to prevent gender mismatchs in room assignment
CREATE OR REPLACE TRIGGER Gender_Assign_Trigger
BEFORE INSERT ON Condo_Assign
FOR EACH ROW
DECLARE
Room_Gender Char(1);
Guest_Gender Char(1);
BEGIN
SELECT Gender 
    INTO Room_Gender
    From Condo_Reservation
    WHERE RID = :new.RID;
SELECT Gender
    INTO Guest_Gender
    FROM SkiClub
    WHERE MID = :new.MID;       
IF Room_Gender = 'M' AND Guest_Gender = 'F' THEN
    addReserveError(:new.MID,:new.RID,SYSDATE,'g00001','Female guest assigned to male room');
ELSIF Room_Gender = 'F' AND Guest_Gender = 'M' THEN
    addReserveError(:new.MID,:new.RID,SYSDATE,'g00002','Male guest assigned to female room');
END IF;
END Gender_Assign_Trigger;
/

应从condo_assign中删除条目的触发器：

CREATE OR REPLACE TRIGGER Remove_errors_trigger
after Insert on ReserveError
FOR EACH ROW
BEGIN
  DELETE FROM Condo_Assign
  WHERE MID = :new.MID and RID = :new.RID;
END remove_errors_trigger;
/

Answer 1

整个过程在

插入前触发器中启动。

尝试删除记录的代码

不会删除任何内容，因为尚未插入记录。触发删除代码时，没有任何要删除的内容。

通常，签入触发器可防止插入引发异常的错误数据。插入数据的代码必须处理异常。

但是您已经有一个处理插入的过程。它甚至对存款进行检查。为什么不在那里处理性别检查？