Problem solve Get help with specific problems with your technologies, process and projects.

Can triggers be applied to sequence objects?

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 here
As 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

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.