This shows you the differences between two versions of the page.
kurs:journal_trigger_mit_denormalisieren [2011/12/01 12:54] mh |
kurs:journal_trigger_mit_denormalisieren [2014/09/10 21:22] |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | <code> | ||
- | CREATE OR REPLACE | ||
- | TYPE ausbildung | ||
- | AS TABLE OF VARCHAR2(100) | ||
- | / | ||
- | </code> | ||
- | |||
- | <code> | ||
- | CREATE OR REPLACE | ||
- | TYPE varchar2_100 | ||
- | AS TABLE OF VARCHAR2(100) | ||
- | / | ||
- | </code> | ||
- | |||
- | <code> | ||
- | 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 | ||
- | </code> | ||
- | |||
- | <code> | ||
- | 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) | ||
- | / | ||
- | </code> | ||
- | |||
- | <code> | ||
- | 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 | ||
- | / | ||
- | </code> | ||
- | |||
- | <code> | ||
- | -- 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; | ||
- | / | ||
- | </code> | ||
- | |||
- | <code> | ||
- | 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; | ||
- | / | ||
- | </code> | ||