CODEWITHSUNDEEP
sqlplsqltriggersauditaudit-logging
TugaWantsToLearn
TugaWantsToLearn

Reputation: 1

Audit tables with parameters

I want to audit changes in several tables to a history table. I read about triggers, the problem is that I want to receive a reason for the operation on the table as well as the backend user who did it not the DB user.

So I'll have like

Table 1

ID_TABLE_1
FIELD_A
FIELD_B

Table 2

ID_TABLE_2
FIELD_C
FIELD_D

Table HISTORY

TABLE_NAME
RECORD_ID
USER
REASON
OPERATION

Looking for the best aproach here, as I see I've two options:

I can create a trigger for the tables save TABLE_NAME , RECORD_ID and OPERATION and then have a function to update USER and REASON ?
or

Create a function to do all that?

Upvotes: 0

Views: 78

Answers (1)

fuNcti0n
fuNcti0n

Reputation: 189

Use predicates like INSERTING/UPDATING/DELETING in the trigger to determine which DML operation happens.

Try this:

create trigger trg_operation_type
  before insert or update
  on table_1
  for each row
begin
  case
    when inserting then
        --insert to history table
    when updating then
        --insert to history table
  end case;
end;

Upvotes: 1

Related Questions