LitSearch: Views & Triggers

Views

This section creates two views, one which displays a human-readable view of various information regarding a book including the title, year written, author, region written and era written. The second view displays the text of words which are synonyms. The "Synonym" table stores the id numbers of words that are synonyms -- the view shows what the text of those words are.


CREATE VIEW ReadableBookInfo AS
  SELECT Work.title as title, Work.year as year_written,
	 Author.name as author, WrittenIn.region as region,
	 WrittenIn.era as era
  FROM Work, Author, WrittenIn, WrittenBy
  WHERE Work.id = WrittenBy.work and 
        WrittenBy.name = Author.name and
        WrittenIn.work = Work.id;

CREATE VIEW Synonyms AS
  SELECT w1.word as root, w2.word as child
  FROM Word w1, Word w2, Synonym syn
  WHERE w1.id = syn.root AND
        w2.id = syn.child;

Triggers

This section creates two triggers on the database. The first causes a little fake review to be entered in to the databse every time a new work is added to the database. The second creates an entry in the synonym table with both entries as the same value (since every word is a synonym of iteself) every time a new word is added to the vocabulary.


CREATE TRIGGER GoodReviewsTrig
  AFTER INSERT ON Work
  FOR EACH ROW
  WHEN (NEW.title LIKE '%computer%')
  BEGIN
    INSERT INTO Criticism VALUES 
     (:NEW.title || ' is a great book', 
      'Keith Ito',
      'http://www.stanford.edu/~keithito/' || :NEW.id || '.html',
      'I thought that ' || :NEW.title || ' was the best book of the year',
      'Keiths Reviews',
      'great book');
  END GoodReviewsTrig;
.
run;

CREATE TRIGGER ReflexiveSynTrig
  AFTER INSERT ON Word
  FOR EACH ROW
  WHEN (NEW.id > 0)
  BEGIN
     INSERT INTO Syn VALUES (:NEW.id, :NEW.id);
  END ReflexiveSynTrig;

.
run;

Author: Keith Ito - September 2001