Oracle: Using subquery in a trigger
18,168
Solution 1
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.
Solution 2
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;
Solution 3
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...
![vipirtti](https://i.stack.imgur.com/Aeh4w.jpg?s=256&g=1)
Author by
vipirtti
Updated on June 11, 2022Comments
-
vipirtti about 2 years
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;
-
Erich Kitzmueller about 15 yearsThe cost of executing the subquery is most likely much higher than the overhead for firing a trigger, so I guess it doesn't matter anyway.
-
vipirtti about 15 yearsAttempted BEGIN IF (SELECT offer FROM projects WHERE projnum = :new.projnum) IS NULL THEN INSERT INTO etc. However, it Oracle throws an error as it encounteres the SELECT in that statement. It just doesn't expect it.
-
Jonathan Leffler about 15 yearsI'm not sufficiently familiar with PL/SQL to know whether that's a plausible way of writing the code in the trigger. It looks good; other systems that I know would require some sort of assignment from the SELECT (with an INTO clause and a variable), and then check the variable, or something along those lines. There's probably a description of the limitations on triggers in the manuals. Also - can you call a procedure in the action part? If so, maybe that will work for you? (Pass the relevant values as parameters.)
-
vipirtti about 15 yearsSorry, Justin. I can only select one answer as corrent one. This time you were beaten by a simple exception warning :-) The up vote you deserved, never the less.
-
Gary Myers about 15 yearsJust a warning that, if you use multi-table inserts (inserting into both project and project_archiving), you may end up with a mutating table error. As such it is preferable to put the logic where the original insert is, rather than rely on a trigger.