Oracle trigger inserting or updating
2) Statement level trigger - An event is triggered for each sql statement executed.
The following hierarchy is followed when a trigger is fired. 2) Next BEFORE row level trigger fires, once for each row affected.
It does not "see" the new record because the record has not been committed so the update comes back with 0 rows updated.
It might be better include the logic in the trigger itself.
This is an undesirable situation where more than one trigger enter into an infinite loop. all I have a situation where I have to check for insert on a table or change for a perticular field in the table and populate two other fields of the same table.I am trying it at a smaller level with this example but it is not giving me updated value of the column col3. Created a new table : ================ create table mytest1(col1 number not null , col2 number not null, col3 number ) Created a procedure to update col3 ============================= create or replace procedure mytestproc(in_col1 in number) as pragma autonomous_transaction; begin update mytest1 set col3 = 500 where col1 = in_col1; commit; end; Created a trigger to check for insert or update (right now for any column) ================================== create or replace trigger Trig On Test1 after insert or update on mytest1 referencing OLD as old NEW as new for each row begin if :new.col1 = 100 then mytestproc ( :new.col1 ); end if; end; when trying to insert values it does not calcualte the value for column col3 and populated NULL insert into mytest1 values(100,2,3); Please let me know if any one has any suggestions Thanks The procedure is an autonomous transaction.4) Finally the AFTER statement level trigger fires.For Example: Let's create a table 'product_check' which we can use to store messages when triggers are fired.