Solved

Trigger based on transaction

Posted on 2002-07-03
4
703 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 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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now