Reference Table name from inside a trigger???
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Reference Table name from inside a trigger???

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    Reference Table name from inside a trigger???

    using 9.2.0.4

    Is there a way to reference the table name from inside the trigger itself? (ie) What table am I acting on?

  2. #2
    Join Date
    Jan 2004
    Posts
    162
    Don't think there is a built-in way of doing this, in the past I have either popped the call stack or used DBMS_ROWID with the ROWID of a row, example follows...
    Code:
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.4.0 - Production
    
    SQL> CREATE TABLE table_name (column_name NUMBER);
    
    Table created.
    
    SQL> 
    SQL> CREATE FUNCTION object_name (
      2    p_rowid IN ROWID)
      3    RETURN all_objects.object_name%TYPE
      4  IS
      5    v_object_name all_objects.object_name%TYPE;
      6    v_object_id all_objects.object_id%TYPE;
      7  BEGIN
      8    v_object_id := DBMS_ROWID.ROWID_OBJECT (p_rowid);
      9  
     10    SELECT object_name
     11    INTO   v_object_name
     12    FROM   all_objects
     13    WHERE  object_id = v_object_id;
     14  
     15    RETURN v_object_name;
     16  END;
     17  /
    
    Function created.
    
    SQL> 
    SQL> CREATE TRIGGER trigger_name
      2    AFTER INSERT
      3    ON table_name
      4    FOR EACH ROW
      5  BEGIN
      6    DBMS_OUTPUT.put_line (object_name (:NEW.ROWID));
      7  END;
      8  /
    
    Trigger created.
    
    SQL> SET SERVEROUTPUT ON;
    SQL> INSERT INTO table_name VALUES (1);
    TABLE_NAME
    
    1 row created.
    
    SQL>

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Thats clever!

    Thanks

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Kewl

    Though you do need to know the table name to write the trigger!

Posting Permissions

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



Click Here to Expand Forum to Full Width