Oracle:在触发器中使用子查询 [英] Oracle: Using subquery in a trigger
本文介绍了Oracle:在触发器中使用子查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何解决Oracle不允许在触发器中使用子查询的限制.
How can I work around the Oracle's limitation of not allowing subqueries in triggers.
这是我尝试创建的示例触发器,但由于无法使用子查询而无法创建.
Here's an example trigger I'm trying to create, but am unable to because I can't use a subquery.
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;
推荐答案
此触发器可以做到:
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;
我假设从项目中进行选择将始终找到一行;如果不是这样,它将引发您可能需要处理的NO_DATA_FOUND异常.
I have assumed that the select from projects will always find a row; if not it will raise a NO_DATA_FOUND exception that you may need to handle.
这篇关于Oracle:在触发器中使用子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文