Insert a range of dates and should be unique with another column

Question

I have table

CREATE TABLE T_TEST 
( KURS_NUMBER NUMBER PRIMARY KEY,
 KURS_ID NUMBER NOT NULL, 
DATEKURS DATE NOT NULL,
 CONSTRAINT UNIQUE2 UNIQUE
 (KURS_ID,DATEKURS)
 );

TRIGGER for kurs_number

create or replace trigger TR_INSERT_TEST01
  before insert on test01  
  FOR EACH ROW

declare
  -- local variables here
begin
  IF :NEW.KURS_NUMBER IS NULL
    THEN SELECT SEQ_TEST.NEXTVAL INTO :NEW.KURS_NUMBER FROM DUAL;
    END IF;
end TR_INSERT_T_TEST;

How can I insert data to kurs_id which will contain only one digit '1' and datekurs will contain date in order period from 2017year 1 january to 31 december 2017year ( or random date )

connect by level 365

---

This code works very well but if I want to use my trigger for new column kurs_number it doesn't work due (not enough values). I guess that should be in different way.

insert into t_test 
select 1 
       , date '2017-01-01' + (level-1)
from dual
connect by level <= 365
/

Answer 1

This trick generates 365 rows. We can do arithmetic with dates so adding (level-1) to a root date generates 365 dates.

insert into t_test 
select SEQ_TEST.NEXTVAL
       , 1 
       , date '2017-01-01' + (level-1)
from dual
connect by level <= 365
/

"not enough values"

You changed the structure of the target table so you needed to change the projection of the query to match. The revised version includes the additional primary key column.

---

"i want to use my trigger for new column kurs_number"

You can make this a procedure with parameters for kurs_id and the target year. As a bonus this code handles leap years correctly.

create or replace procedure generate_kurs_year_recs
    ( p_kurs_id in number
      , p_kurs_year in varchar2 )
is
    last_dayno number;
begin
    /* find number of days in year */
    select to_number(to_char(to_date( p_kurs_year||'-12-31', 'yyyy-mm-dd')
                    , 'DDD'))
    into last_dayno
    from dual;

    /* generate records for year */
    insert into t_test 
        select SEQ_TEST.NEXTVAL
               , p_kurs_id 
               , to_date( p_kurs_year||'-01-01', 'yyyy-mm-dd') + (level-1)
        from dual
        connect by level <= last_dayno;
end generate_kurs_year_recs;
/

Call the procedure like this:

begin
    generate_kurs_year_recs(1,'2017');
end;

To call from a trigger you will need to pass parameters somehow, presumably using values from the trigger's table.