Solve the problem of accessing the variant table in the ORA-04091 row trigger

The core idea is to save the value to the package variable during the first insertion, instead of reading the table itself during the second insertion, and then reading the package variable, which can successfully solve this problem.

During work, each item in each BOM must be automatically numbered. For items in different Bom, The seq_number column must be set to 1.2.3... The sequence number is automatically generated.
For the first time, I wrote,

Create   Or   Replace   Trigger Tr_bom_autonumber_seqnumber
Before Insert
On BOM
Referencing new As New old As Old
For Each row
Declare
Tmpvar Number ;
Begin
Tmpvar: =   0 ;
Select Greatest (nvl ( Max (To_number (seq_number )), 0 ), Count ( * )) +   1   Into Tmpvar From BOM Where Bomid = : New. bomid;
: New. seq_number: = Nvl (tmpvar, 1 );
End Tr_bom_autonumber_seqnumber;

Because the insert operation will modify table data, insert... When a SELECT statement inserts multiple rows of data, a ORA-04091: Table string. String is mutating, trigger/function may not see it error is reported because the table cannot be accessed when 2nd rows of data are inserted.

I checked a lotArticleIn this example, a message is prompted to use Pragma autonomous_transaction to ensure that the insert action for each row is an autonomous transaction. But in fact, after my test, although the DML will not make an error, but the actual seq_number is all 1, not to achieve the purpose of automatic numbering in turn.

After many experiments, I used the index table saved in the package to save the maximum seq_number of each Bom, which can prevent concurrent conflicts between BOM and users.

The core idea is to save the value to the package variable during the first insertion, instead of reading the table itself during the second insertion, and then reading the package variable, which can successfully solve this problem.

DetailsCodeAs follows:

Create   Or   Replace Package bom_autonumber
Is
Type t_max_seqnumber Is   Table   Of   Number   Index   By Pls_integer;
V_max_seqnumber t_max_seqnumber;
End Bom_autonumber;
/ Create   Or   Replace   Trigger Tr_bom_autonumber_seqnumber
Before Insert
On BOM
Referencing new As New old As Old
For Each row
Declare
Vnumber Number ;
Vbomid Number ;
Begin
Vnumber: =   0 ;
Vbomid: = : New. bomid;
If   Not Bom_autonumber.v_max_seqnumber. Exists (Vbomid) Then
Select Greatest (nvl ( Max (To_number (seq_number )), 0 ), Count ( * )) Into Vnumber From BOM Where Item = Vbomid;
Bom_autonumber.v_max_seqnumber (vbomid ): = Nvl (vnumber, 0 );
End   If ;
Bom_autonumber.v_max_seqnumber (vbomid ): = Bom_autonumber.v_max_seqnumber (vbomid) +   1 ;
: New. seq_number: = Bom_autonumber.v_max_seqnumber (vbomid );
End Tr_bom _ autonumber_seqnumber;
/