User Tools

Site Tools


kurs:kreis_tabelle_trigger

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

kurs:kreis_tabelle_trigger [2014/09/10 21:22]
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>​
kurs/kreis_tabelle_trigger.txt ยท Last modified: 2014/09/10 21:22 (external edit)