oracle SQL触发器WHEN子句编译错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14820820/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Trigger WHEN Clause compilation error
提问by AWS-Cloud-Architect-Rob
I am trying to create a trigger that will check that the date entered is in range if the Prog_Typeentered is 'FILM', however I receive a compilation error every time the statement is run.
我正在尝试创建一个触发器，如果Prog_Type输入的是'FILM'，它将检查输入的日期是否在范围内，但是每次运行该语句时我都会收到编译错误。
The error code is 00103, "Encountered a ; when expected ....."
错误代码是 00103，“遇到 ; 预期时......”
then a list of many punctuation marks. From researching the problem I have seen references to a DECLAREstatement, however I am not sure I would need to declare if the Prog_Typeis created in the Program_Table, and I have reference that table in the trigger.
然后是许多标点符号的列表。通过研究这个问题，我看到了对DECLARE语句的引用，但是我不确定是否需要声明Prog_Type是在 中创建的Program_Table，并且我在触发器中引用了该表。
CREATE OR REPLACE TRIGGER Prog_Made_Limits
BEFORE INSERT OR UPDATE OF Prog_Made ON Program_Table
FOR EACH ROW
WHEN (NEW.Prog_Type <> 'FILM')
BEGIN
IF (:new.Prog_Made < date '1864-12-31' or
:new.Prog_Made > sysdate );
THEN
RAISE_APPLICATION_ERROR( -20001, 'Program Made date must be later than Dec 31st 1864, and earlier than today, if FILM is inserted');
END IF;
END Prog_Made_Limits;
/
回答by GarethD
Your syntax almost fine, but you have a ;after your IFcondition which is causing the error. ;is used to separate statements and consequent of your If is part of the the same statement as the condition.
您的语法几乎没问题，但是您;的IF条件后面有一个导致错误的条件。;用于分隔语句，并且您的 If 的后续语句与条件是同一语句的一部分。
This should work:
这应该有效：
CREATE OR REPLACE TRIGGER Prog_Made_Limits
BEFORE INSERT OR UPDATE OF Prog_Made ON Program_Table
FOR EACH ROW
WHEN (NEW.Prog_Type <> 'FILM')
BEGIN
IF (:new.Prog_Made < date '1864-12-31' or
:new.Prog_Made > sysdate )
THEN
RAISE_APPLICATION_ERROR( -20001, 'Program Made date must be later than Dec 31st 1864, and earlier than today, if FILM is inserted');
END IF;
END Prog_Made_Limits;
I say should, because I don't have Oracle on my laptop, so can only test on SQL Fiddle, but by removing all semicolons I was able to get this to compile, however I think SQL-Fiddle treats semicolons as batch breaks rather than statement breaks, so I am not sure it is necessary to remove them all.
我说应该，因为我的笔记本电脑上没有 Oracle，所以只能在 SQL Fiddle 上进行测试，但是通过删除所有分号我能够编译它，但是我认为 SQL-Fiddle 将分号视为批处理中断而不是语句中断，所以我不确定是否有必要将它们全部删除。
回答by user2001117
Hey you have used the semicolon after the IF clause. That will end the statement. So please remove the column from that place and use the below code once:
嘿，您在 IF 子句后使用了分号。这将结束声明。因此，请从该位置移除该列并使用以下代码一次：
CREATE OR REPLACE TRIGGER Prog_Made_Limits
BEFORE INSERT OR UPDATE OF Prog_Made ON Program_Table
FOR EACH ROW
WHEN (NEW.Prog_Type <> 'FILM')
BEGIN
IF (:new.Prog_Made < date '1864-12-31' or
:new.Prog_Made > sysdate )
THEN
RAISE_APPLICATION_ERROR( -20001, 'Program Made date must be later than Dec 31st 1864, and earlier than today, if FILM is inserted');
END IF;
END Prog_Made_Limits;
/
