我有卖火车票的记录。我有Ticket
桌和Cancel
桌(这里是取消的票)。我已经写了一个触发器,当我添加一些票到表Cancel
,然后这个票设置在表票券(Client_id attribute = NULL)。
但是我想写一个新的:"如果我已经取消了票(Client_id = NULL在表票券),然后当我更新票券和设置Client_id = 'some_number'(例如:Client_id = 5),那么我应该从Cancel
表中删除这个票据。这是我的触发器:
CREATE OR REPLACE TRIGGER Buy_Cancel
AFTER UPDATE
ON TICKET
FOR EACH ROW
WHEN(old.CLIENT_ID IS NULL)
DECLARE
rowAmmount INTEGER;
BEGIN
SELECT COUNT(*)
INTO rowAmmount
FROM CANCEL c JOIN TICKET t ON c.TICKET_ID = t.TICKET_ID
WHERE t.TICKET_ID = :OLD.TICKET_ID;
IF rowAmmount > 0 THEN
DELETE FROM CANCEL c
WHERE c.TICKET_ID = :NEW.TICKET_ID;
END IF;
END;
当我运行查询时:
UPDATE TICKET t
SET t.CLIENT_ID = 5
WHERE t.TICKET_ID = 2;
会导致如下错误:
ORA-04091: table MYDB.TICKET is mutating, trigger/function may not see it
ORA-06512: at "MYDB.BUY_CANCEL", line 4
ORA-04088: error during execution of trigger 'MYDB.BUY_CANCEL' SQL6.sql 2 8
我不确定我很理解你的逻辑,但错误是因为你在该表的触发器内查询ticket
表。您不需要这样做—连接似乎没有添加任何东西,因为您已经有了票ID:
CREATE OR REPLACE TRIGGER Buy_Cancel
AFTER UPDATE
ON TICKET
FOR EACH ROW
WHEN(old.CLIENT_ID IS NULL)
DECLARE
rowAmmount INTEGER;
BEGIN
SELECT COUNT(*)
INTO rowAmmount
FROM CANCEL c
WHERE c.TICKET_ID = :OLD.TICKET_ID;
IF rowAmmount > 0 THEN
DELETE FROM CANCEL c
WHERE c.TICKET_ID = :NEW.TICKET_ID;
END IF;
END;
没有太多的意义做查询虽然,真的;只需要执行删除操作。会工作-它不会出错,如果没有匹配的行,所以它会简化它很多。在select
中使用:old
,在delete中使用:new
;如果它们是相同的(如果主键是主键就很可能是这样),这是可以的,但无论如何看起来都很奇怪。