,如果票对应于尚未离开的航班,请备份任何已删除机票的飞行ID,机票号和座位号。
创建序列generateKey 从100开始 增加1;
CREATE TABLE backUpTicket
(
cancelledKey NUMBER NOT NULL,
flightID char(9) NOT NULL,
ticketNum varchar2(5) NOT NULL,
seatNum NUMBER(3) NOT NULL,
PRIMARY KEY(cancelledKey)
);
CREATE OR REPLACE TRIGGER backUpTicketCancelled
BEFORE DELETE ON FLIGHT
FOR EACH ROW
BEGIN
INSERT INTO backUpTicket
VALUES
(generateKey.nextVal, :old.flightID, :old.TicketNum, :old.seatNum);
IF(:old.actDepartDateTime IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('The Flight has not departed yet');
END IF;
END backUpTicket;
/
if(:old.ActdepartdateTime为null(中发生的错误
首先,您的触发器应在售票表上,而不是飞行表。其次,如果航班已经离开,您不应该创建机票的备份。您的代码始终创建备份。我建议以下以下功能更好:
CREATE OR REPLACE TRIGGER backUpTicketCancelled
BEFORE DELETE ON TICKET
FOR EACH ROW
DECLARE
rowFlight FLIGHT%ROWTYPE;
BEGIN
SELECT *
INTO rowFlight
FROM FLIGHT f
WHERE f.FLIGHTID = :OLD.FLIGHTID;
IF rowFlight.actDepartDateTime IS NULL THEN
INSERT INTO backUpTicket
(CANCELLEDKEY, FLIGHTID, TICKETNUM, SEATNUM)
VALUES
(generateKey.nextVal, :old.flightID, :old.TicketNum, :old.seatNum);
ELSE
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE', 'DD-MON-YYYY HH24:MI:SS') || ': ' ||
'Flight ' || :OLD.FLIGHTID || ' departed at ' ||
TO_CHAR(rowFlight.actDepartDateTime, 'DD-MON-YYYY HH24:MI:SS' ||
' so ticket #' || :OLD.TICKETNUM || ' for seat #' || :OLD.SEATNUM ||
' is not eligible for backup');
END IF;
END backUpTicket;
另外,备份表的名称应该是BACKUP_TICKET
而不是backUpTicket
,因此您可能会为此标记。
此外,虽然我意识到这是一项家庭作业,但这是一个非常糟糕的设计选择。您应该创建一个程序,该过程执行删除机票所需的所有操作,而不是将"魔法代码"埋在触发器中,以执行此类业务决策。
最佳幸运。
从您的注释中,触发器位于表TICKET
上,但列actDepartDateTime
在表FLIGHT
中。您只能使用:OLD
和:NEW
从触发表中访问列。要获取actDepartDateTime
,您需要使用:OLD.flightID
值从FLIGHT
表中进行选择。