Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Trigger based on transaction

Posted on 2002-07-03
4
Medium Priority
?
739 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 300 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

Technology Partners: 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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

704 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