Solved

Trigger based on transaction

Posted on 2002-07-03
4
723 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:damienm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 7126698
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
0
 
LVL 6

Accepted Solution

by:
M-Ali earned 75 total points
ID: 7126829
Triggers can be statement-level as well as row-level. Row-level triggers fire for each row, while statement level fires once for the statement. But in statement level, you will not be able to obtain the row values (eg tableA.id). We can solve this problem using two triggers: one statement level, and one row-level.


CREATE TABLE t1 (id number, name varchar2(10));
INSERT INTO t1 VALUES(1,'A1');
INSERT INTO t1 VALUES(2,'A2');
INSERT INTO t1 VALUES(3,'A3');
INSERT INTO t1 VALUES(4,'A4');
INSERT INTO t1 VALUES(5,'A5');

CREATE TABLE my_audit(seq NUMBER, id NUMBER);

CREATE SEQUENCE my_seq START WITH 1;

CREATE OR REPLACE PACKAGE pkg1 AS
  seq_no NUMBER;
END;
/

CREATE OR REPLACE TRIGGER bef_del_t1
BEFORE DELETE ON t1
BEGIN
  SELECT my_seq.nextval INTO pkg1.seq_no FROM DUAL;
END;
/


CREATE OR REPLACE TRIGGER after_del_t1
AFTER DELETE ON t1
FOR EACH ROW
BEGIN
  INSERT INTO my_audit VALUES(pkg1.seq_no, :old.id);
END;
/


SQL> select * from t1;

        ID NAME
---------- ----------
         1 A1
         2 A2
         3 A3
         4 A4
         5 A5

SQL> delete from t1 where id in (1,2);

2 rows deleted.

SQL> select * from my_audit;

       SEQ         ID
---------- ----------
         1          1
         1          2

SQL> delete from t1 where id in (3,5);

2 rows deleted.

SQL> select * from my_audit;

       SEQ         ID
---------- ----------
         1          1
         1          2
         2          3
         2          5

HTH
Ali
0
 
LVL 3

Expert Comment

by:p_yaroslav
ID: 7126867
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.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7127024
The two-trigger approach suggested by M-Ali is the only way I know of to do this.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question