Combine Row Level and Statement Level Trigger 1

[MadJock]

Hi,

Is it possible to have one trigger that operates on both stetement level and row level?

Example
STATEMENT LEVEL:
check config table for auditing level
ROW LEVEL
IF auditing level is on THEN
Create audit record for each row

If not, is there a standard practice to achieve this? Checking the config table on each row is having a drastic performance impact.

The above trigger would be on AFTER UPDATE, INSERT or DELETE.

Thanks in advance,

Graeme

"Just beacuse you're paranoid, don't mean they're not after you

 

[LKBrwnDBA]

Yes, you can combine both trigger types.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[MadJock]

Thanks, do you have the syntax for this?

"Just beacuse you're paranoid, don't mean they're not after you

 

[LKBrwnDBA]

The difference between the two types of trigger would be the 'FOR EACH ROW' clause.

In order to 'keep' data that is queried by statement level trigger and to be used by row level trigger, you would have to create a package.

Example PSEUDOCODE:

-- Package
Create Or Replace Package My_Trig_Data 
Is
Auditing_Level Char(1);
End My_Trig_Data;
/
-- Statement Level Trigger
Create Or Replace Trigger Stmt_Trg
Before Insert Or Update On Mytable
Is
Begin
  Select Auditing_Level
    Into My_Trig_Data.Auditing_Level
    From Audit_Config
   Where Table= 'MYTABLE';
End;   
/
-- Row Level Trigger
Create Or Replace Trigger Row_Trg
Before Insert Or Update On Mytable
For Each Row
Is
Begin
  If My_Trig_Data.Auditing_Level = 'Y'
  Then
    -- Create Audit Record For Each Row
    Insert Into Audit_Table
     Values 
       ('Mytab'
       , :New.Id
       , User
       , Sysdate
       );
  End If;
End;   
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb