防止插入触发器 [英] Prevent Insert Trigger

问题描述

我如何获得此触发器以防止前进不大于0或小于100的插入?谢谢.

How can I get this trigger to prevent the insert where the advance is not greater than 0 or less than 100? Thanks.

DROP TRIGGER CheckAdvance;
CREATE OR REPLACE TRIGGER CheckAdvance
BEFORE INSERT OR UPDATE OF advance ON titles
FOR EACH ROW
WHEN (new.advance<0 OR new.advance>100)
BEGIN
dbms_output.put_line('Advance is Invalid.');
END;

推荐答案

这是一个班级问题."

"it was a class question."

我在会议上作了演讲后，与一位大学讲师聊天，他讲授PL/SQL.我的演讲是关于PL/SQL的良好实践的.我的一张幻灯片只是说不要使用触发器".这位讲师告诉我，他发现这样的建议很难与课程需求保持一致.他们必须教给学生所有语法，但他承认，他们经常设置任务，要求我们在专业编写软件时不会使用的解决方案.

I had a chat with a university lecturer who taught PL/SQL after I gave a presentation at a conference. My talk was on PL/SQL good practice; one of my slides simply said "Don't use triggers". The lecturer told me he finds such advice difficult to reconcile with the needs of the curriculum. They have to teach their students all the syntax but he admitted that often they set tasks which demanded solutions we wouldn't use when writing software professionally.

这是一个问题.正确的方法是使用检查约束，如戈登的答案所示.约束更有效，也很惯用.但是您的老师希望您编写触发器，所以这是您的代码，已更正.

This is such a question. The correct approach is to use a check constraint, as Gordon's answer shows. Constraints are more efficient, and idiomatic too. But your teacher wants you to write a trigger, so here is your code, corrected.

CREATE OR REPLACE TRIGGER CheckAdvance
    BEFORE INSERT OR UPDATE OF advance ON titles
    FOR EACH ROW
BEGIN
    IF (:new.advance < 0  
         OR :new.advance > 100)
    THEN
        raise_application_error(-20000
                , 'Advance cannot be less than zero or greater than one hundred.');
    END IF;
END;

注意事项:

1. CREATE OR REPLACE意味着我们可以在没有初步DROP语句的情况下更改触发代码.
2. BEGIN和END框架代码块，例如触发器主体.
3. 静态条件条件用IF ... END IF关键字构成； WHEN用于退出循环构造.
4. 带有:NEW(和:OLD)关键字的参考表列值-注意冒号.
5. 使用RAISE_APPLICATION_ERROR引发异常；错误号必须在-20999到-20000之间，Oracle为用户定义的异常保留该错误号.
6. 使您的错误消息有意义:告诉用户他们做错了什么，而不是让他们猜测.
7. 学习使用缩进使代码可读.您未来的同事将为此感谢您.

这篇关于防止插入触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！