[[ teilnehmer kurs dezember 2016 ]]


<code>
create or replace 
trigger TRG_TEILNEHMER_JN 
AFTER INSERT OR DELETE OR UPDATE ON TEILNEHMER 
FOR EACH ROW 
declare
v_dml varchar2(10);
v_now date := sysdate;
BEGIN
  if inserting then 
    v_dml := 'I';
  elsif updating then
    v_dml := 'U';
  else 
    v_dml := 'D';
  end if;
  
  update teilnehmer_jn set 
    tjn_valid_to = v_now
  where tjn_id = (select max(tjn_id) from  
    teilnehmer_jn where tjn_tln_id = :old.tln_id;
  
  insert into teilnehmer_jn (
    tjn_id,
    tjn_tln_id,
    tjn_tln_name,
    tjn_tln_height,
    tjn_tln_gen_name,
    tjn_dml,
    tjn_create_date,
    tjn_update_date,
    tjn_valid_from
   ) values (
    seq.nextval,
    coalesce(:new.tln_id, :old.tln_id),
    coalesce(:new.tln_name, :old.tln_name),
    coalesce(:new.tln_height, :old.tln_height),
   (select gen_name from gender where gen_id = 
           coalesce(:new.tln_gen_id, :old.tln_gen_id)),  
    v_dml,
    coalesce(:old.tln_create_date, sysdate),
    sysdate,
    v_now
    );      
    
END;
</code>