Example 17-25: INSTEAD OF INSERT trigger handles user preferences

CREATE TRIGGER user_prefs_ins INSTEAD OF INSERT ON user_prefs_view FOR EACH ROW
DECLARE
  debugct      NUMBER;
  stockList    VARCHAR2(200) := :new.stocks;
  catList      VARCHAR2(200) := :new.categories;
  curItem      VARCHAR2(80);
  x            NUMBER;
  temp         VARCHAR2(1);
  userExists   BOOLEAN;
  CURSOR c_userExists(c_userid VARCHAR2) IS SELECT 'x' FROM user_prefs
                                             WHERE userid = c_userid;
BEGIN
  OPEN c_userExists(:new.userid);
  FETCH c_userExists INTO temp;
  userExists := c_userExists%FOUND;
  CLOSE c_userExists;
  IF userExists THEN -- Update existing user
   UPDATE user_prefs SET headlines  = :new.headlines
                       WHERE userid = :new.userid;
  ELSE -- Insert a new user
    INSERT INTO user_prefs(userid,headlines)VALUES(:new.userid,:new.headlines);
  END IF;
  -- Handle Stocks
  DELETE FROM user_stocks WHERE userid = :new.userid;
  WHILE (stockList IS NOT NULL) LOOP
    x := INSTR(stockList,' ');
    IF x > 0 THEN
      curItem := SUBSTR(stockList,1,x-1); stockList := SUBSTR(stockList,x+1);
    ELSE
      curItem := stockList; stockList := NULL;
    END IF;
    INSERT INTO user_stocks(userid,ticker) VALUES(:new.userid,curItem);
  END LOOP;
  -- Handle Categories
  DELETE FROM user_news_categories WHERE userid = :new.userid;
  WHILE (catList IS NOT NULL) LOOP
    x := INSTR(catList,' ');
    IF x > 0 THEN
      curItem := SUBSTR(catList,1,x-1); catList := SUBSTR(catList,x+1);
    ELSE
      curItem := catList; catList := NULL;
    END IF;
    INSERT INTO user_news_categories(userid,category)VALUES(:new.userid,curItem);
  END LOOP;
END;