dokuwiki

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;
/