Oracle：在触发器中使用子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/909156/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: Using subquery in a trigger
提问by vipirtti
How can I work around the Oracle's limitation of not allowing subqueries in triggers.
我如何解决 Oracle 不允许在触发器中使用子查询的限制。
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;
回答by Tony Andrews
This trigger would do it:
这个触发器会这样做：
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;
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.
我假设从项目中选择总是会找到一行；如果不是，它将引发您可能需要处理的 NO_DATA_FOUND 异常。
回答by Justin Cave
I expect that you want something like
我希望你想要类似的东西
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;
回答by Jonathan Leffler
Can you put the condition into the action (between BEGIN and END) instead of in the 'whether it fires'? Yes, it means that the trigger body might be fired more often - but if it gets you around the problem...
您能否将条件放入操作中（在 BEGIN 和 END 之间）而不是“是否触发”？是的，这意味着触发器主体可能会更频繁地被触发 - 但如果它能让你解决问题......
