user546710 wrote:you are creating triger on the table and updating it. It will not allow to update since you are firing trigger on same table.
I am creating one after insert trigger on table tests, which will check, if application id is null, application will be fetched from another table and update the table
tests table. But this trigger is not updating the application id of the table tests
CREATE OR REPLACE
AFTER INSERT ON tests FOR EACH ROW
v_rec_appl_tests_id := :NEW.rec_appl_tests_id;
v_a_recruit_id := :NEW.a_recruit_id;
IF :NEW.a_applic_id IS NULL THEN
SELECT a_applic_id INTO v_application_id FROM recruit WHERE a_recrut_id = v_a_recruit_id;
UPDATE tests SET a_applic_id = v_application_id
WHERE rec_appl_tests_id = v_rec_appl_tests_id;--:NEW.rec_appl_tests_id;
Thanks in advance,
CREATE OR REPLACE TRIGGER TB_REC_APPL_TESTS1 AFTER INSERT ON tests FOR EACH ROW DECLARE v_application_id NUMBER; BEGIN IF :NEW.a_applic_id IS NULL THEN SELECT a_applic_id INTO v_application_id FROM recruit WHERE a_recrut_id = :NEW.a_recruit_id; :NEW.a_applic_id := v_application_id; END IF; END; /
user546710 wrote:So, AFTER you do the insert, you expect it to work?
Yes, by using, BEFORE trigger, we can do it. But any idea why, it is not updating while using AFTER trigger. Is it Oracle behavior ?