Oracle officially always states that firing order of same type triggers on the same table is random: http://www.oracle.com/technology/oramag/oracle/06-mar/o26asktom.html Trigger Firing Order "You cannot depend on the order of triggers firing in a given trigger type. Even if you observe that the three BEFORE triggers you have fire in the order TRIGGER_A, TRIGGER_B, TRIGGER_C, you cannot count on that." unless you use FOLLOWS clause in 11g: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7004.htm#SQLRF01405 11g FOLLOWS clause for CREATE TRIGGER But test shows they always fire in the reverse order of the trigger creation date (xxx_objects.created). (DB is 10gR2) create table t (x number); --tmp is our auditing or logging table create table tmp (d timestamp, x number); The triggers do the auditing by writing records to a file. create directory yongtest as '/tmp'; drop trigger t_trig1; create trigger t_trig1 before insert on t for each row declare v_output_file utl_file.file_type; begin v_output_file := utl_file.fopen('YONGTEST', 'newfile.txt', 'a'); utl_file .put_line(v_output_file, 'trig1'); utl_file.fclose_all; --dbms_lock.sleep(3); --You can uncomment the two lines and see the effect. --insert into tmp values (sysdate, 1); end; / --wait a few seconds at least drop trigger t_trig2; create trigger t_trig2 before insert on t for each row declare v_output_file utl_file.file_type; begin v_output_file := utl_file.fopen('YONGTEST', 'newfile.txt', 'a'); utl_file .put_line(v_output_file, 'trig2'); utl_file.fclose_all; --insert into tmp values (sysdate, 2); end; / alter session set nls_date_format = 'yyyymmdd hh24:mi:ss'; select object_name, created from user_objects where object_type = 'TRIGGER'; insert into t values (1); cat newfile.txt --drop and recreate t_trig1 and test --drop and recreate t_trig2 and test No effect is found after flushing shared pool, disabling/enabling triggers (so last_ddl_time changes). Writing to a file on the filesystem is a good logging alternative in general with no hassel of setting up autonomous transactions. In this test, it suits our need perfectly. In fact, without time-consuming SQL in the trigger code, the timestamp column (d) of tmp is useless because the times the rows are inserted by the two triggers are shown as exactly the same in tmp. However, sql_trace reveals a slight difference; in my test, "INSERT INTO TMP VALUES (SYSDATE, 1)" has tim=1185479580330956 while "INSERT ... 2)" has tim=1185479580335652, a difference of 4696 us or about 5 ms. With dbms_lock.sleep(3) in t_trig1 above insert into tmp: (1) If t_trig1 is created after t_trig2, then t_trig1 is run first but since sleep is before insert, the two triggers' insert's are fired too close in time and tmp.d can't differentiate; (2) If t_trig2 is created after t_trig1, then t_trig2 inserts a value into tmp 3 seconds before t_trig1 does and you can clearly see the different d values in tmp. http://www.itpub.net/thread-993657-5-1.html An incident we had recently. We were debugging some application code, generated by Oracle Designer. Imagine the complexity. The code works fine in current production. We exported and imported into another database, where one piece of code always raises ORA-14400: inserted partition key does not map to any partition. We used SQL trace to find that the value is missing or null (no wonder it doesn't fit in any partition). We spent quite a bit of time to find why it's null. The value is a package variable. Here's what we find. The table has multiple triggers of the same type (before insert). One trigger assigns a value to the package variable (let me call it producer trigger) and another trigger picks it up (consumer trigger). A coworker DBA asked me if triggers' firing order is fixed according to some rule. I said "Absolutely not! It's completely random" because I know documentation says so and Tom Kyte says too. However, the code has been working fine since it was written about two years ago, relying on the luck against this supposedly "random" order. Then the DBA said maybe the order is fixed, perhaps by trigger creation time? We checked. Indeed the consumer trigger was created later and in the new database it was created slightly earlier by the import. So I did a small test, where each trigger writes a value to an external file (because you shouldn't write to a logging table; the values in a heap table have no reliable order and the timestamps for the rows are exactly the same). Surprisingly, they *are* written in the reverse order of the trigger creation time! We then dropped and recreated the consumer trigger without changing the trigger code and it works fine in the new database. But since documentation says we should not rely on trigger firing order (unless it's 11g and triggers have the new follows clause defined), we modify the code to include the variable assignment logic into the consumer trigger, although it seems the problem was already solved by creating the trigger later. The moral of the story is not that the more knowledge the worse, but that sometimes we need to think out of the box in troubleshooting a problem. If we had insisted on Oracle's claim and hadn't even bothered to check the trigger creation time, we wouldn't have been able to find out why the old code works and new code does not. Yong Huang