Are triggers executed with the current transaction isolation level?

Question

Consider a table with 3 columns: ID (unique, taken from an Oracle sequence), CATEGORY and CODE (no constraints on these last two).

Each category has multiple codes attached to it, but the codes must be unique within that category. Example:

ID    CATEGORY   CODE
1     1          X
2     1          Y
3     1          Y     //wrong

The third one is not OK since we already have code Y for the category 1.

Now consider a trigger that runs before inserts and checks to see if the values to be inserted are OK. That is, for the record that is being inserted the trigger reads the category, and then reads all codes from the table that have that category, and if the code from the record that must be inserted already exists then raises an exception so that the record is not inserted.

My question is what will the trigger "see" in the table if the transaction isolation level is READ_COMMITED and there are two inserts executed in two different transactions at almost exactly the same time but the transaction is committed later?

Example:

(1) Initially, the table looks like this:

ID    CATEGORY   CODE
1     1          X

(2) there are two transactions T1 and T2 (isolation level READ_COMMITED for both);

(3) both transactions want to insert category = 1 and code = Y;

(4) T1 performs the insert and the trigger is executed. There is no Y in the table so it is OK to insert;

(5) T2 performs the insert and the trigger is executed. There is no Y in the table (T1 has not yet commited) so it is OK to insert;

(6) T1 commits and the table now looks like this:

ID    CATEGORY   CODE
1     1          X
2     1          Y

(7) T2 now commits. What happens here? Do I get an error and the record is not inserted or I get the following table:

ID    CATEGORY   CODE
1     1          X
2     1          Y
3     1          Y     //this is wrong

?!

What do the triggers "see" and what happens to the insert?

Answer 1

Don't use triggers for such validation. Triggers don't scale. Also, as you have noticed they don't work in multi-user environments. This is why Nature gave us unique constraints.

alter table your_table
    add constraint yr_tab_uk unique (category, code)
    using index
/