Trigger to track sequence usage
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Trigger to track sequence usage

  1. #1
    Join Date
    Jan 2002
    Posts
    12
    Is there a trigger I can create to track activity on a sequence ?? I need to know when a sequence has been updated. I know there are event triggers but I don't know if that can be applied to sequence objects also. And if so, how??

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    You can certainly create a package to act as an interface to the sequence and use it to do the logging. So everyplace where you access the sequence you would call this packager or function instead and it would log the call as well as return the sequence.

  3. #3
    Join Date
    Jan 2002
    Posts
    12

    trigger to track sequence usage

    Do you have an example you could share as to how what this procedure/function would look like ?? I'm difficulty envisioning how I trap for sequence_id.nextval in a procedure.


  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    All you need to do is call this function instead of the sequence directly. You can put all of your sequnce calls into one package if you like and then if Oracle changed the way sequences are handled then you only need to make changes in one place.

    CREATE OR REPLACE FUNCTION ReturnSeqNumber
    RETURN NUMBER IS
    MyResults NUMBER(18,0);
    BEGIN
    SELECT Seqa.NextVal INTO MyResults FROM DUAL;
    INSERT INTO LogTable (Owner, Value) VALUES
    (USER, MyResults);
    RETURN MyResults;
    END ReturnSeqNumber;
    /

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