kurs:journal_trigger_mit_denormalisieren
Differences
This shows you the differences between two versions of the page.
|
kurs:journal_trigger_mit_denormalisieren [2011/12/01 09:21] 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 TABLE kursteilnehmer_jn | ||
| - | (id NUMBER, | ||
| - | kursteilnehmer_id NUMBER, | ||
| - | name VARCHAR2(100 BYTE), | ||
| - | punkte NUMBER, | ||
| - | log_date DATE, | ||
| - | dml VARCHAR2(1 BYTE)) | ||
| - | / | ||
| - | </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) | ||
| - | / | ||
| - | |||
| - | |||
| - | -- 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 | ||
| - | ON kursteilnehmer | ||
| - | REFERENCING NEW AS NEW OLD AS OLD | ||
| - | FOR EACH ROW | ||
| - | declare | ||
| - | v_dml varchar2(1); | ||
| - | begin | ||
| - | |||
| - | v_dml := (case when inserting then 'I' | ||
| - | when updating then 'U' | ||
| - | else 'D' | ||
| - | end); | ||
| - | |||
| - | insert into kursteilnehmer_jn ( | ||
| - | id, | ||
| - | kursteilnehmer_id, | ||
| - | name, | ||
| - | punkte, | ||
| - | log_date, | ||
| - | dml | ||
| - | ) values ( | ||
| - | s.nextval, | ||
| - | coalesce(:new.id, :old.id), | ||
| - | coalesce(:new.name, :old.name), | ||
| - | coalesce(:new.punkte, :old.punkte), | ||
| - | sysdate, | ||
| - | v_dml | ||
| - | ); | ||
| - | 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 | ||
| - | / | ||
| - | </code> | ||
kurs/journal_trigger_mit_denormalisieren.txt ยท 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
