This shows you the differences between two versions of the page.
Both sides previous revision Previous revision | |||
kurs:journal_trigger_mit_denormalisieren [2011/12/01 09:21] mh |
kurs:journal_trigger_mit_denormalisieren [2011/12/01 12:54] mh |
||
---|---|---|---|
Line 2: | Line 2: | ||
CREATE OR REPLACE | CREATE OR REPLACE | ||
TYPE ausbildung | TYPE ausbildung | ||
+ | AS TABLE OF VARCHAR2(100) | ||
+ | / | ||
+ | </code> | ||
+ | |||
+ | <code> | ||
+ | CREATE OR REPLACE | ||
+ | TYPE varchar2_100 | ||
AS TABLE OF VARCHAR2(100) | AS TABLE OF VARCHAR2(100) | ||
/ | / | ||
Line 13: | Line 20: | ||
punkte NUMBER, | punkte NUMBER, | ||
log_date DATE, | log_date DATE, | ||
- | dml VARCHAR2(1 BYTE)) | + | dml VARCHAR2(1 BYTE), |
- | / | + | telnr HR.VARCHAR2_100) |
+ | NESTED TABLE telnr STORE AS telnr_store | ||
</code> | </code> | ||
Line 45: | Line 53: | ||
ADD CONSTRAINT kursteilnehmer_id_pk PRIMARY KEY (id) | 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 | -- Triggers for KURSTEILNEHMER | ||
Line 97: | Line 139: | ||
CREATE OR REPLACE TRIGGER kursteilnehmer_jn_trg | CREATE OR REPLACE TRIGGER kursteilnehmer_jn_trg | ||
AFTER | AFTER | ||
- | INSERT OR DELETE OR UPDATE | + | INSERT OR DELETE OR UPDATE OF punkte, id, name, changed |
ON kursteilnehmer | ON kursteilnehmer | ||
REFERENCING NEW AS NEW OLD AS OLD | REFERENCING NEW AS NEW OLD AS OLD | ||
Line 103: | Line 145: | ||
declare | declare | ||
v_dml varchar2(1); | 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 | begin | ||
Line 110: | Line 158: | ||
end); | end); | ||
- | insert into kursteilnehmer_jn ( | + | open c_tel_per_kursteilnehmer(coalesce(:new.id, :old.id)); |
- | id, | + | fetch c_tel_per_kursteilnehmer bulk collect into v_telnr; |
- | kursteilnehmer_id, | + | close c_tel_per_kursteilnehmer; |
- | name, | + | |
- | punkte, | + | make_kursteilnehmer_jn( |
- | log_date, | + | p_dml => v_dml, |
- | dml | + | p_id => coalesce(:new.id, :old.id), |
- | ) values ( | + | p_name => coalesce(:new.name, :old.name), |
- | s.nextval, | + | p_punkte => coalesce(:new.punkte, :old.punkte), |
- | coalesce(:new.id, :old.id), | + | p_telnr => v_telnr |
- | coalesce(:new.name, :old.name), | + | ); |
- | coalesce(:new.punkte, :old.punkte), | + | |
- | sysdate, | + | end; |
- | v_dml | + | |
- | ); | + | |
- | end; | + | |
/ | / | ||
</code> | </code> | ||
Line 139: | Line 184: | ||
ADD CONSTRAINT kt_telefon_kt_id_fk FOREIGN KEY (kursteilnehmer_id) | ADD CONSTRAINT kt_telefon_kt_id_fk FOREIGN KEY (kursteilnehmer_id) | ||
REFERENCES kursteilnehmer (id) ON DELETE CASCADE | 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> | </code> | ||