首页 文章

Oracle触发编译错误

提问于
浏览 次
0

对于以下触发器，我收到以下编译错误：缺少SET关键字，忽略SQL语句（第2行），遇到以下其中一项时遇到符号“end-of-file”:(开始大小写声明结束异常退出goto if loop mod null pragma raise返回选择更新，同时使用<< continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge（第9行（ . 我搜索过2个以上的例子，仍然可以'弄清楚我正在做出哪些导致这些编译错误的语法错误 .

CREATE OR REPLACE TRIGGER CHANGE_RENTAL_RATE
BEFORE INSERT ON film FOR EACH ROW
BEGIN
UPDATE film
SELECT *
SET rental_rate = CASE special_features
              WHEN 'Trailers' THEN rental_rate := :new.rental_rate + 0.1
              WHEN 'Commentaries' THEN rental_rate := new.rental_rate + 0.5
              WHEN 'Deleted Scenes' THEN rental_rate := new.rental_rate + 0.2
              WHEN 'Behind the Scenes' THEN rental_rate := new.rental_rate - 0.2
END;
/
sql oracle11g

1 回答

  • 1

    您的update语句没有意义，因为它影响 film 中的所有行 . 你正在做一个"before"触发器 . 也许你想要：

    CREATE OR REPLACE TRIGGER CHANGE_RENTAL_RATE
BEFORE INSERT ON film FOR EACH ROW
BEGIN
    if :new.special_features = 'Trailers' THEN :new.rental_rate := :new.rental_rate + 0.1;
    elsif :new.special_features = 'Commentaries' THEN :new.rental_rate := :new.rental_rate + 0.5;
    elsif :new.special_features = 'Deleted Scenes' THEN :new.rental_rate := :new.rental_rate + 0.2;
    elsif :new.special_features = 'Behind the Scenes' THEN :new.rental_rate := :new.rental_rate - 0.2;
    else :new.special_features := NULL
    end if;
END;
    回复于

相关问题