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