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 they can be applied to sequence objects also. And if so, how?
Unfortunately, you cannot base a trigger on when someone issues a SELECT against an object. So a SELECT trigger is out of the question. Now, we must ask ourselves if we can create a UPDATE trigger on the sequence. After all, the process of selecting NEXTVAL does update the sequence value. So can we create an UPDATE trigger on the sequence? The answer is no as can be verified by this example:
ORA9I SQL> create trigger test_seq_audit 2 before update on test_seq 3 for each row 4 begin 5 insert into seq_audit values (sysdate,'sequence updated'); 6 end; 7 / before update on test_seq * ERROR at line 2: ORA-02201: sequence not allowed hereAs you can see, if a sequence is the object of the trigger, an ORA-2201 is raised.
Your only other option is to create a trigger on the data dictionary table that stores sequence information, SYS.SEQ$. Unfortunately, you can only audit changes to this table. And if a sequence caches values and one of those values is selected, then this table is not updated.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.