Oracle:在触发器中使用子查询

Question

如何解决Oracle不允许在触发器中使用子查询的限制？

下面是我试图创建的一个示例触发器，但由于不能使用子查询而无法创建。

CREATE OR REPLACE TRIGGER trigger_w_subquery
AFTER UPDATE OR INSERT ON project_archiving
FOR EACH ROW WHEN (old.archiving_status <> new.archiving_status
  AND new.archiving_status = 1
  AND (SELECT offer FROM projects WHERE projnum = :new.projnum) IS NULL
)
BEGIN
  INSERT INTO offer_log (offer, status, date)
  VALUES (null, 9, sysdate);
END;

Answer 1

此触发器将完成此操作：

CREATE OR REPLACE TRIGGER trigger_w_subquery
AFTER UPDATE OR INSERT ON project_archiving
FOR EACH ROW WHEN (old.archiving_status <> new.archiving_status
  AND new.archiving_status = 1
)
DECLARE
  l_offer projects.offer%TYPE;
BEGIN
  SELECT offer INTO l_offer 
  FROM projects 
  WHERE projnum = :new.projnum;

  IF l_offer IS NULL THEN
    INSERT INTO offer_log (offer, status, date)
    VALUES (null, 9, sysdate);
  END IF;
END;

我假设select from projects将始终找到一行；如果不是，它将引发您可能需要处理的NO_DATA_FOUND异常。

Answer 2

我希望你想要像这样的东西

CREATE OR REPLACE TRIGGER trigger_w_subquery
AFTER UPDATE OR INSERT ON project_archiving
FOR EACH ROW 
WHEN (old.archiving_status <> new.archiving_status
  AND new.archiving_status = 1)
DECLARE
  l_offer projects.offer%TYPE;
BEGIN
  SELECT offer 
    INTO l_offer
    FROM projects 
   WHERE projnum = :new.projnum;

  IF( l_offer IS NULL )
  THEN
    INSERT INTO offer_log (offer, status, date)
      VALUES (null, 9, sysdate);
  END IF;
END;

Answer 3

你能把条件放到动作中(在BEGIN和END之间)，而不是放在“是否触发”中吗？是的，这意味着触发器主体可能会更频繁地被触发-但如果它能让你绕过这个问题……