Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146

    Unanswered: Delete Trigger - How to refer a Value there....

    Hi All,

    Is there any way i can access New Value of record in Delete Triggers? That is, I want to refer :NEW.<Column_Name> in a Delete Trigger. Or, is there any-other way to do it.?

    I will describe what is my requirement. The details are given below:

    My Setup is: Oracle9i R-2 DB 9.2.0.4 on Sun Solaris-9
    Application Server (Mid-Tier): Oracle9i AS R-2 9.2.0.3
    Application: Developped in Java / JSP and deployed as J2EE applicaiton on Ora9iAS. The application is accessed via Web-Browser.

    Basically i want to keep history of all the records being deleted from a table into a audit_log table. The structure of main table & audit_log table is exaclty same except audit_log is having one more column <user_id>. I can do it simply in delete trigger by inserting in audit_log table and referring to ld value. But while inserting into audit_log table, i also need to get the current user_id who is deleting this record. This is the user ID which is passed to my java application, basically my registered Login Id.

    I am able to get this value in Update Trigger as :new.user_id or even in Insert Trigger. But is it possible to do it in any way the same thing in Delete Trigger.

    Thanks for ur concern and is looking forward to your valuable input to this.

    Regards,
    Kamesh Rastogi
    - KR

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    There are no new values in a DELETE operation, only old ones.

    The logged-in Oracle user is simply USER.

  3. #3
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    Hi William,
    Thanks for ur quick response. That's true there is no new value in delete operation. I thought it might be possible by doing some work-around. What u say is really true. Thanks.

    And regardign Oracle User, it will not work for my case. Because my user id is provided by us as per our security arrangement and programs. SO basically i need my user id by which i looged into my application.

    Thanks a lot.

    Regards,
    Kamesh Rastogi
    - KR

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Before insert or update or delete trigger...

    Code:
    if inserting then
       :new has values
    elsif updating then
       :new and :old have values
    else -- elsif deleting
       :old has values
    end if;
    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •