This shows you the differences between two versions of the page.
— |
kurs:kreis_tabelle_trigger [2014/09/10 21:22] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ==== Kreis ==== | ||
+ | <code> | ||
+ | CREATE TABLE kreis | ||
+ | (id NUMBER NOT NULL, | ||
+ | radius NUMBER NOT NULL, | ||
+ | flaeche NUMBER, | ||
+ | changed NUMBER) | ||
+ | PCTFREE 10 | ||
+ | INITRANS 1 | ||
+ | MAXTRANS 255 | ||
+ | TABLESPACE users | ||
+ | STORAGE ( | ||
+ | INITIAL 65536 | ||
+ | NEXT 1048576 | ||
+ | MINEXTENTS 1 | ||
+ | MAXEXTENTS 2147483645 | ||
+ | ) | ||
+ | NOCACHE | ||
+ | MONITORING | ||
+ | NOPARALLEL | ||
+ | LOGGING | ||
+ | / | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ALTER TABLE kreis | ||
+ | ADD CONSTRAINT id_pk PRIMARY KEY (id) | ||
+ | USING INDEX | ||
+ | PCTFREE 10 | ||
+ | INITRANS 2 | ||
+ | MAXTRANS 255 | ||
+ | TABLESPACE users | ||
+ | STORAGE ( | ||
+ | INITIAL 65536 | ||
+ | NEXT 1048576 | ||
+ | MINEXTENTS 1 | ||
+ | MAXEXTENTS 2147483645 | ||
+ | ) | ||
+ | / | ||
+ | |||
+ | ALTER TABLE kreis | ||
+ | ADD CONSTRAINT radius_chk CHECK (radius > 0 | ||
+ | ) | ||
+ | / | ||
+ | |||
+ | CREATE OR REPLACE TRIGGER trg_kreis_id | ||
+ | BEFORE | ||
+ | INSERT | ||
+ | ON kreis | ||
+ | REFERENCING NEW AS NEW OLD AS OLD | ||
+ | FOR EACH ROW | ||
+ | begin | ||
+ | :new.id := hr_seq.nextval; | ||
+ | end; | ||
+ | / | ||
+ | |||
+ | CREATE OR REPLACE TRIGGER trg_kreis_flaeche | ||
+ | BEFORE | ||
+ | INSERT OR UPDATE | ||
+ | ON kreis | ||
+ | REFERENCING NEW AS NEW OLD AS OLD | ||
+ | FOR EACH ROW | ||
+ | begin | ||
+ | :new.flaeche := kreis_flaeche(:new.radius); | ||
+ | end; | ||
+ | / | ||
+ | |||
+ | CREATE OR REPLACE TRIGGER trg_changed | ||
+ | BEFORE | ||
+ | UPDATE OF radius | ||
+ | ON kreis | ||
+ | REFERENCING NEW AS NEW OLD AS OLD | ||
+ | FOR EACH ROW | ||
+ | declare | ||
+ | c number; | ||
+ | begin | ||
+ | -- geht | ||
+ | c := nvl(:old.changed, 0); | ||
+ | :new.changed := c + 1; | ||
+ | -- geht nicht | ||
+ | -- update kreis set changed = nvl(:old.changed, 0) + 1 where id = :old.id; | ||
+ | end; | ||
+ | / | ||
+ | </code> |