Вернуться

Листинг 2

--| PACKAGE PK_Trigg001
--|
Create Or Replace Package PK_Trigg001 Is
  triggersEnabled   Boolean:=true;
  insertRefOnInsert Boolean:=true;

  Procedure P_Reset;
  Procedure P_PutRowList(inRec In Tab%RowType, isNew In Boolean);
  Function  F_EmptyRowList(isNew In Boolean) Return Boolean;
  Procedure P_Handle_Insert;
  Procedure P_Handle_Update;
  Procedure P_Handle_Delete;
End PK_Trigg001;
/

--| PACKAGE BODY PK_Trigg001
--|
Create Or Replace Package Body PK_Trigg001 Is
  Type RowListType Is Table Of Tab%RowType Index By Binary_Integer;

  newList    RowListType;
  oldList    RowListType;
  newCount#  Number:=0;
  oldCount#  Number:=0;

  Procedure P_Reset Is .....as in Listing 1.....

  Procedure P_PutRowList(inRec In Tab%RowType, isNew In Boolean) Is
  Begin
    If isNew Then
       newCount#:=newCount# + 1;
       newList(newCount#).pk_tab   :=inRec.pk_tab;
       newList(newCount#).col_code :=inRec.col_code;
    Else
       oldCount#:=oldCount# + 1;
       oldList(oldCount#).pk_tab   :=inRec.pk_tab;
       oldList(oldCount#).col_code :=inRec.col_code;
    End If;
  End P_PutRowList;

  Function F_EmptyRowList(isNew In Boolean) Return Boolean Is 
           .....as in Listing 1.....

  Procedure P_CheckNoofTab Is
    noof Number;
  Begin
    Select Count(*) Into noof
    From   Tab
    Where  col_code = newList(newCount#).col_code;

    If noof > 5 Then
       Raise_Application_Error(-20104,'Number of rows in Tab with same '||
                               'col_code exceeded (max.5 rows pr.col_code).');
    End If;
  End P_CheckNoofTab;

  Procedure P_CreateRefRow Is
    pk_ref Number;
  Begin
    pk_ref:=..........

    --| Создаем новую строку в таблице Ref
    Insert Into Ref(pk_ref,
                    fk_tab)
            Values (pk_ref,
                    newList(newCount#).pk_tab);
  End P_CreateRefRow;

  Procedure P_Handle_Insert Is
  Begin
    While newCount# > 0 Loop
       --| Проверка: Не привышено ли максимальное число строк таблицы Tab
       P_CheckNoofTab;
       --| Создаем соответствующую запись в B, если эта опция не выключена.
       If insertRefOnInsert Then
          P_CreateRefRow;
       End If;

       newCount#:=newCount# - 1;
    End Loop;
  End P_Handle_Insert;

  Procedure P_Handle_Delete Is .....as in Listing 1.....

  Procedure P_Handle_Update Is .....as in Listing 1.....
End PK_Trigg001;
/

--| INSERT TRIGGER

Create Or Replace Trigger IBS_Tab Before Insert On Tab
Begin
  If PK_Trigg001.triggersEnabled Then
     PK_Trigg001.P_Reset;
  End If;
End;
/

Create Or Replace Trigger IBR_Tab Before Insert On Tab For Each Row
Declare
  inRec Tab%RowType;
Begin
  If PK_Trigg001.triggersEnabled Then
     --| Запоминаем значения полей внесенной записи
     inRec.pk_tab   :=:new.pk_tab;
     inRec.col_code :=:new.col_code;
     PK_Trigg001.P_PutRowList(inRec,True);
  End If;
End;
/

Create Or Replace Trigger IAS_Tab After Insert On Tab
Begin
  If PK_Trigg001.triggersEnabled Then
     If Not PK_Trigg001.F_EmptyRowList(True) Then
        PK_Trigg001.P_Handle_Insert;
     End If;
  End If;
End;
/

Вернуться