预防触发器

2022-01-01 00:00:00 sql insert oracle triggers date-arithmetic

我有一张这样的桌子:

StudentID  Student Name   Birthdate Student Birthplace Gender Height Weight 
--------- --------------- --------- ------------------ ------ ------ ------ 
83        Adam Stone      30-JUN-94 Towson, USA        M      193    88               
84        Stephanie Love  17-JUN-93 KL,Malaysia        F      176    67                 
85        Rachel Kim      17-FEB-92 Seoul, South Korea F      179    56

如何编写触发器来防止任何 15 岁以下的学生被存储在学生表中?

How do i write a trigger to prevent any student under the age of 15 from being stored in the student's table?

推荐答案

您有一个出生日期.因此，您需要确定 DoB 至少比今天早 16 年.有多种不同的方法可以做到这一点；这是一个使用间隔文字的例子.>

You have a Date of Birth. So you need to determine that the DoB is at least sixteen years before today. There are various different ways of doing this; here's one using an interval literal.

create or replace trigger students_biur
     before insert or update on students for each row 
begin
    if (:new.student_birthdate + INTERVAL '15' YEAR ) < sysdate
    then 
         raise_application_error( -20000, 'This student is too young be registered.');     
    end if;
end;

此触发器还会检查更新，以防止后续更改使学生无效.

This trigger also checks for updates, to prevent subsequent changes invalidating an student.

触发器名称 students_biur 只是我使用的约定:表名后缀表示 *B*efore *I*nsert *U*pdate 对于每个 *R*ow.

The trigger name students_biur is just a convention I use: the table name with a suffix indicating *B*efore *I*nsert *U*pdate for each *R*ow.

RAISE_APPLICATION_ERROR 是一个标准过程，用于抛出用户定义的异常和消息.了解更多信息.

RAISE_APPLICATION_ERROR is a standard procedure for throwing user-defined exceptions with a message. Find out more.

Oracle 为用户定义的错误保留了 -20999 到 -20000 的范围；任何其他数字都可能与 oracle 定义的异常发生冲突.

Oracle reserves the range -20999 to -20000 for user-defined errors; any other number may clash with a oracle-defined exception.

相关文章