title: SQL CREATE SEQUENCE based on a combination of date and counter
body: <p>I am using Oracle SQL:</p>
  
  <pre><code>Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0 
  PL/SQL Release 12.1.0.2.0 - Production                                                    0 
  CORE    12.1.0.2.0  Production                                                            0 
  TNS for Linux: Version 12.1.0.2.0 - Production                                            0 
  NLSRTL Version 12.1.0.2.0 - Production    
  </code></pre>
  
  <p>I need help figuring out how to create a sequence for a key field below:</p>
  
  <p>I have a table with a field named MY_ID.</p>
  
  <p>MY_ID needs to be automatically generated using sequence when a record is inserted.</p>
  
  <p>The rule for the sequence is a combination of a string prefix, an counter increment by 1 per day and reset at midnight, and the date.</p>
  
  <p>for example:
  on Sept 10, we inserted 2 records, then the MY_ID should be:</p>
  
  <pre><code>PREFIX_01_20170910
  PREFIX_02_20170910
  </code></pre>
  
  <p>on Sept 11 :</p>
  
  <pre><code>PREFIX_01_20170911
  PREFIX_02_20170911
  PREFIX_03_20170911
  </code></pre>
  
  <p>on Sept 12, the whole table might look like this</p>
  
  <pre><code>PREFIX_01_20170910
  PREFIX_02_20170910
  PREFIX_01_20170911
  PREFIX_02_20170911
  PREFIX_03_20170911
  PREFIX_01_20170912
  </code></pre>
  
  <p>so far, all i can do with the sequence is increment by 1 regardless of the date:</p>
  
  <pre><code>CREATE SEQUENCE SEQ_MY_TABLE INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCACHE;
  </code></pre>
  
  <p>and the trigger:</p>
  
  <pre><code>create or replace TRIGGER MY_TABLE_TRG 
  BEFORE INSERT ON MY_TABLE
  FOR EACH ROW
  BEGIN
    &lt;&lt;COLUMN_SEQUENCES&gt;&gt;
    BEGIN
      IF INSERTING AND :NEW.MY_ID IS NULL THEN
        SELECT SEQ_MY_TABLE .NEXTVAL INTO :NEW.MY_ID FROM SYS.DUAL;
      END IF;
    END COLUMN_SEQUENCES;
  END;
  </code></pre>
  
  <p>Thank you!</p>