Triggers on sequences????
I am using a sequence as a primary key in a table. In some cases the application will pull a new sequence (NEXTVAL) for the user's record, but if the user elects not to save the record, this sequence value is discarded. However for audit purposes (new requirement!!) I need to record 1) who pulled each sequence number 2) the date/time 3) the sequence number -- whether the record was ultimately saved or not.
I'm trying to create an "AFTER UPDATE" trigger on the sequence and am not having much luck. When I try the following, either my client, server, or both of them "go away" and I have to log in again.
CREATE OR REPLACE trg_seqtest
AFTER UPDATE OF seq_tkt.NEXTVAL
ON seq_tkt
BEGIN
INSERT INTO auditlog (aud_user, aud_dttm) VALUES (USER, SYSDATE);
/* will insert the ticket nextval later if I can get this to work first */
end;
Can triggers be written on sequences? If not, I have some other programming work-arounds that I can use, but this was the most direct and logical approach.
Thanks
Don