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;
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With