CREATE OR REPLACE 
TYPE ausbildung
 AS TABLE OF VARCHAR2(100)
/

CREATE OR REPLACE 
TYPE varchar2_100
 AS TABLE OF VARCHAR2(100)
/

CREATE TABLE kursteilnehmer_jn
    (id                             NUMBER,
    kursteilnehmer_id              NUMBER,
    name                           VARCHAR2(100 BYTE),
    punkte                         NUMBER,
    log_date                       DATE,
    dml                            VARCHAR2(1 BYTE),
    telnr                          HR.VARCHAR2_100)
   NESTED TABLE telnr STORE AS telnr_store

CREATE TABLE kursteilnehmer
    (id                             NUMBER ,
    name                           VARCHAR2(100 BYTE),
    ausbildung                     HR.AUSBILDUNG,
    create_date                    DATE,
    change_date                    DATE,
    punkte                         NUMBER)
   NESTED TABLE ausbildung STORE AS store_ausbildung  
/

-- Indexes for KURSTEILNEHMER

CREATE UNIQUE INDEX sys_c009774 ON kursteilnehmer
  (
    ausbildung                      ASC
  )
/

-- Constraints for KURSTEILNEHMER

ALTER TABLE kursteilnehmer
ADD CONSTRAINT kursteilnehmer_name_uk UNIQUE (name)
/

ALTER TABLE kursteilnehmer
ADD CONSTRAINT kursteilnehmer_id_pk PRIMARY KEY (id)
/

CREATE OR REPLACE 
PROCEDURE make_kursteilnehmer_jn
   ( p_dml    IN varchar2,
     p_id     IN number,
     p_name   in varchar2  default null,
     p_punkte in number    default null,
     p_telnr  in varchar2_100 default null)
   IS

BEGIN

insert into kursteilnehmer_jn (
    id,
    kursteilnehmer_id,
    name,
    punkte,
    log_date,
    dml,
    telnr
  ) values (
    s.nextval,
    p_id,
    p_name,
    p_punkte,
    sysdate,
    p_dml,
    p_telnr
  );
END; -- Procedure
/

-- Triggers for KURSTEILNEHMER

CREATE OR REPLACE TRIGGER kursteilnehmer_insert_id_trg
 BEFORE
  INSERT
 ON kursteilnehmer
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
begin
  :new.id := s.nextval;
  :new.create_date := sysdate;
end;
/

CREATE OR REPLACE TRIGGER kursteilnehmer_change_date_trg
 BEFORE
  UPDATE
 ON kursteilnehmer
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
begin
  :new.change_date := sysdate;
end;
/

CREATE OR REPLACE TRIGGER kursteilnehmer_log_trg
 AFTER
  INSERT OR DELETE OR UPDATE
 ON kursteilnehmer
REFERENCING NEW AS NEW OLD AS OLD
declare
v_dml varchar2(1);
begin
  if inserting then
    v_dml := 'I';
  elsif updating then
    v_dml := 'U';
  else
    v_dml := 'D';
  end if;
  
  insert into log (logging)
    values
  (v_dml||' :kursteilnehmer');
  
end;
/

CREATE OR REPLACE TRIGGER kursteilnehmer_jn_trg
 AFTER
   INSERT OR DELETE OR UPDATE OF punkte, id, name, changed
 ON kursteilnehmer
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
declare
  v_dml varchar2(1);
  cursor c_tel_per_kursteilnehmer(p_id number) is
  select telnr from kt_telefon where
  kursteilnehmer_id = p_id;

  v_telnr varchar2_100 := varchar2_100();
  
begin

v_dml := (case when inserting then 'I'
               when updating  then 'U'
         else 'D'
         end);

open c_tel_per_kursteilnehmer(coalesce(:new.id, :old.id));
fetch c_tel_per_kursteilnehmer bulk collect into v_telnr;
close c_tel_per_kursteilnehmer;

make_kursteilnehmer_jn(
   p_dml    => v_dml,
   p_id     => coalesce(:new.id, :old.id),
   p_name   => coalesce(:new.name, :old.name),
   p_punkte => coalesce(:new.punkte, :old.punkte),
   p_telnr  => v_telnr
   );

end;
/

CREATE TABLE kt_telefon
    (id                             NUMBER,
    kursteilnehmer_id              NUMBER,
    telnr                          VARCHAR2(30 BYTE))
/

ALTER TABLE kt_telefon
ADD CONSTRAINT kt_telefon_kt_id_fk FOREIGN KEY (kursteilnehmer_id)
REFERENCES kursteilnehmer (id) ON DELETE CASCADE
/

CREATE OR REPLACE TRIGGER kt_telefon_jn_trg
 BEFORE
  INSERT OR DELETE OR UPDATE
 ON kt_telefon
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
declare
  v_name varchar2(100);
  v_punkte number;
  v_telnr varchar2_100 := varchar2_100();

  cursor c_tel_per_kursteilnehmer(p_id number) is
  select telnr from kt_telefon where
  kursteilnehmer_id = p_id;
  
begin
  select name, punkte into v_name, v_punkte from kursteilnehmer
    where id = coalesce(:new.kursteilnehmer_id, :old.kursteilnehmer_id);

  open c_tel_per_kursteilnehmer(
    coalesce(:new.kursteilnehmer_id, :old.kursteilnehmer_id));
  fetch c_tel_per_kursteilnehmer bulk collect into v_telnr;
  close c_tel_per_kursteilnehmer;

  v_telnr.extend;
  v_telnr(v_telnr.last) := coalesce(:new.telnr,:old.telnr);

  make_kursteilnehmer_jn(
    p_dml    => 'U',
    p_id     => coalesce(:new.kursteilnehmer_id,:old.kursteilnehmer_id),
    p_telnr  => v_telnr,
    p_name   => v_name,
    p_punkte => v_punkte
    );
    
end;
/