This content has been marked as final. Show 7 replies
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,
Best practice is to use other table to update or any other operation.
create or replace trigger tb_rec_appl_tests1 after insert on tests for each row
<font size = 3 color = "red">pragma autonomous_transaction; -- EVIL EVIL EVIL</font>
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
where a_recrut_id = v_a_recruit_id;
set a_applic_id = v_application_id
where rec_appl_tests_id = v_rec_appl_tests_id;
<font size = 3 color="red">commit; -- EVIL EVIL EVIL</font>
Your code is EVIL. Never, I say again NEVER commit in a trigger. Just don't do it NEVER.
Now tell us your business requirement. We could come up with a better solution than the one above.
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 ?
How could it?, you've already inserted.
I think you're confused. Your original idea was, after the insert, do an update on the row you've just inserted.
You can't do that in a trigger.
What you can do, in a before trigger, is intercept the row before it gets inserted and change the data using :new.col = <something>
Does that make sense?