kurs:journal_trigger_mit_denormalisieren
Differences
This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
kurs:journal_trigger_mit_denormalisieren [2011/12/01 09:08] mh created |
kurs:journal_trigger_mit_denormalisieren [2014/09/10 21:22] (current) |
||
|---|---|---|---|
| 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> | <code> | ||
| CREATE TABLE kursteilnehmer_jn | CREATE TABLE kursteilnehmer_jn | ||
| Line 6: | 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 38: | 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 90: | 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 96: | 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 103: | 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 132: | 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> | ||
kurs/journal_trigger_mit_denormalisieren.1322726923.txt.gz · Last modified: 2014/09/10 21:22 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
