golf_commentary.sql

来自「OReilly Oracle PL SQL Programming第4版源代码」· SQL 代码 · 共 59 行

/*-- golf_commentary.sql */
DROP TABLE golf_scores;

CREATE TABLE golf_scores
(timestamp  DATE   NOT NULL,
 score      NUMBER NOT NULL,
 commentary VARCHAR2(30));

CREATE OR REPLACE TRIGGER golf_commentary
BEFORE INSERT ON golf_scores
FOR EACH ROW
BEGIN
  IF :NEW.score < 72 THEN
    :NEW.commentary := 'Under Par';
  ELSIF :NEW.score = 72 THEN
       :NEW.commentary := 'Par';
  ELSE
    :NEW.commentary := 'Over Par';
  END IF;
END;
/

DROP TRIGGER golf_commentary;

CREATE OR REPLACE TRIGGER golf_commentary_under_par
BEFORE INSERT ON golf_scores
FOR EACH ROW
WHEN (NEW.score < 72)
BEGIN
  :NEW.commentary := 'Under Par';
END;
/

CREATE OR REPLACE TRIGGER golf_commentary_par
BEFORE INSERT ON golf_scores
FOR EACH ROW
WHEN (NEW.score = 72)
BEGIN
  :NEW.commentary := 'Par';
END;
/

CREATE OR REPLACE TRIGGER golf_commentary_over_par
BEFORE INSERT ON golf_scores
FOR EACH ROW
WHEN (NEW.score > 72)
BEGIN
  :NEW.commentary := 'Over Par';
END;
/


/*======================================================================
| Supplement to the third edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2002 O'Reilly &
| Associates, Inc. To submit corrections or find more code samples visit
| http://www.oreilly.com/catalog/oraclep3/
*/