Link to home
Start Free TrialLog in
Avatar of damienm
damienmFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Trigger based on transaction

If I run the following in Oracle

delete * from tableA where id in (1,2).

I want to run a trigger which audits these deletes, but I want it to audit them as part of the same transaction, so the audit table would have

transaction     idDeleted
-----------     --
  .              .
  .              .
  5              1
  5              2

If I then ran the following

delete * from tableA where id in (3,5).

Then the audit table would then have

transaction     idDeleted
-----------     --
  .              .
  .              .
  5              1
  5              2
  6              3
  6              5

But I have think Oracle triggers may be row based so I am not sure how to do this.

Thanks

Damien
Avatar of Helena Marková
Helena Marková
Flag of Slovakia image

I hope another experts will help you. But maybe this description of antonomous transactions will be useful for you.

http://osi.oracle.com/~tkyte/autonomous/index.html
ASKER CERTIFIED SOLUTION
Avatar of M-Ali
M-Ali

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of p_yaroslav
p_yaroslav

Hi!

You may use package dbms_application_info for this purpose:


  procedure set_module(module_name varchar2, action_name varchar2);
  --  Sets the name of the module that is currently running to a new
  --    module.  When the current module terminates, this should
  --    be called with the name of the new module if there is one, or
  --    null if there is not a new module.  Passing null for either of these
  --    values is equivalent to passing a zero length string.
  --  Input arguments:
  --    module_name
  --      The name of the module that will now be running.  The maximum
  --      length of the module name is 48 bytes.  Longer names will be
  --      truncated.
  --    action_name
  --      The name of the action that will now be running.  The maximum
  --      length of the action_name is 32 bytes.  Longer names will be
  --      truncated. If the action name is not being specified, then null
  --      should be passed for this value.
  --
  procedure set_action(action_name varchar2);
  --  Sets the name of the current action within the current module.
  --    When the current action terminates, this should be called with the
  --    name of the new action if there is one, or null if there is not a
  --    new action.  Passing null for this value is equivalent to passing
  --    a zero length string.
  --  Input arguments:
  --    action_name
  --      The name of the action that will now be running.  The maximum
  --      length of the action_name is 32 bytes.  Longer names will be
  --      truncated.
  --


Best regards,
Yaroslav.
The two-trigger approach suggested by M-Ali is the only way I know of to do this.