With the compound trigger, both the statement-level and row-level action can be put up in a single trigger.
Plus there is an added advantage: it allows sharing of common state between all the trigger-points using variable.
This is because compound trigger in oracle 11g has a declarative section where one can declare variable to be used within trigger.
This common state is established at the start of triggering statement and is destroyed after completion of trigger (regardless of trigger being in error or not).
Triggers in different schemas can have the same name.
Sometimes the method that you think would be the worst might actually outperform the way that you think would be the better way.
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.
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.