reset sequence by trigger

Question

I am using a trigger to reset a sequence every year,

but there is some issue when calling a procedure into the triggers

CREATE OR REPLACE TRIGGER t_dmd_pk
   BEFORE INSERT
   ON S_DEMANDE
   FOR EACH ROW
BEGIN
   IF (TO_CHAR (SYSDATE, 'dd') = '16' AND TO_CHAR (SYSDATE, 'mm') = '12')
   THEN
      reset_seq ('SEQ_ID_DMD');
   END IF;

   SELECT SEQ_ID_DMD.NEXTVAL || TO_CHAR (SYSDATE, 'yyyy')
     INTO :new.DMD_ID
     FROM DUAL;
END;
/

and that's my procedure

CREATE OR REPLACE PROCEDURE reset_seq (p_seq_name IN VARCHAR2)
IS
   l_val   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'select ' || p_seq_name || '.nextval from dual'
      INTO l_val;

   EXECUTE IMMEDIATE
      'alter sequence ' || p_seq_name || ' increment by -' || l_val;
END;
/

What is the error? You would save yourself some pain by scheduling a job that calls a procedure to do this. — kevinskio
– kevinskio, Commented Dec 16, 2013 at 15:40
Running DDL from a trigger is almost always a bad idea. There are better ways of restarting a numeric sequence at the end of a calendar year. — Jeffrey Kemp
– Jeffrey Kemp, Commented Dec 17, 2013 at 2:50

acesargl · Accepted Answer · 2013-12-16 17:38:38Z

The trigger is executed inside an INSERT statement, and the trigger call a procedure that tries to commit the transaction (ALTER SEQUENCE is a DDL statatement, so it is auto-commited).

To ensure statement atomicity the transaction can only be commited when the last statement is finalized. So it is not possible to commit the current transaction inside a trigger.

But you can execute your trigger or procedure as an autonomous transaction (Oracle opens a new transaction and executes the code of your trigger or porcedure inside this new transaction).

See this link for more details: http://www.oracle-base.com/articles/misc/autonomous-transactions.php

But remember:

the autonomous transaction cannot see your still uncommited data, and
if you finally rollback your current transaction (after the execution of the trigger and the commit of the autonomous transaction) the inserted tuples will be rolled back, but the autonomous transaction will not be rolled back.

Craig · Accepted Answer · 2013-12-16 17:49:56Z

Your procedure doesn't work the way you think it should.. If your sequence last value was 10, then you are altering the sequence to increment by -10 every time it is called. I am guessing the first time you execute it, you get an ORA-08004 because your minvalue is probably 1 and it would be trying to return 0 (which isn't allowed). Even if that didn't error, the next time you called it would, as it would try to return -10 in my example. What I believe you really want is:

CREATE OR REPLACE PROCEDURE reset_seq (p_seq_name IN VARCHAR2)
IS
   l_val   NUMBER;
BEGIN
   -- Get Current Value of Sequence
   EXECUTE IMMEDIATE 'select ' || p_seq_name || '.nextval from dual'
      INTO l_val;

   -- Alter to sequence to allow to go to 0 and decrease by current value
   EXECUTE IMMEDIATE
      'alter sequence ' || p_seq_name || ' minvalue 0 increment by -' || l_val;

   -- Get value from sequence again (should set seq to 0)
   EXECUTE IMMEDIATE 'select ' || p_seq_name || '.nextval from dual'
      INTO l_val;       

   -- Alter sequence to increase by 1 again
   EXECUTE IMMEDIATE
      'alter sequence ' || p_seq_name || ' increment by 1';
END;

This allows the sequence to be 0 (which you need if you want the next call to return 1), sets it to 0, then changes it back to increment by 1 with each successive call. However, it is probably much easier to just drop and recreate the sequence.

The real question though, is why you would ever want to do this. This looks like bad design. A sequence is just supposed to return a unique number. Nothing more, nothing less. There should be no meaning behind the number, and it sure seems like you are trying to assign meaning here. Sequences don't guarantee your rows will be inserted in order and don't guarantee there won't be gaps, they just provide a unique number. Concatenating the year on the end of this unique number makes this design more suspect.

user1759420 · Accepted Answer · 2013-12-17 08:29:29Z

0

i found a solution, i used dbms_job instead of trigger, that works fine for me

answered Dec 17, 2013 at 8:29

user1759420

533 silver badges8 bronze badges

Collectives™ on Stack Overflow

reset sequence by trigger

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related