damienm
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
http://osi.oracle.com/~tkyte/autonomous/index.html