Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Trigger based on transaction

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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

885 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