插入触发器之前 [英] Before insert trigger

问题描述

在某些情况为真时，我想在将插入触发器插入表中之前禁用

I want to disable in before insert trigger inserting into table when some condition is true

 create or replace trigger CHECK_FOR_MAX_ENTRANTS
before insert on application
declare
    entrants_count number;
    max_entrants number;
begin
    select count(*) into entrants_count from application 
    where id_speciality = :new.id_speciality;

    select max_students_number into max_entrants from speciality s
    where s.id_speciality = :new.id_speciality;

    IF entrants_count >= max_entrants THEN
        **disable this insert**
end;

我该怎么做?

推荐答案

假设您正在谈论Oracle，那么，代替禁用此插入，您可以:

Assuming you're talking about Oracle, then, in place of disable this insert you could:

IF entrants_count >= max_entrants THEN
    raise_application_error(-21010, 'Max number of Entrants Reached');
END IF;

请参阅: http://下载. oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm#sthref2006

编辑:通常，让插入静默失败(您的要求)是一个坏主意.如果您尝试在插入后触发器中删除记录，那么您也可能会遇到表突变的错误.

It's generally a bad idea to have inserts fail silently (what you're asking for). You also may run into mutating table errors if you try to delete the record in an after insert trigger.

相反，只是不要插入记录开头. 您可能实现此目标的一种方法是在insert语句的末尾添加以下内容:

Instead, just don't insert the record to begin with. One way you could probably achieve this is to add something like this to the end of your insert statement:

WHERE EXISTS SELECT null FROM 
(SELECT COUNT(*) entrants_count FROM application 
WHERE id_speciality = :new.id_speciality) a,
(SELECT max_students_number max_entrants 
FROM speciality WHERE id_speciality = :new.id_speciality) s
WHERE a.entrants_count < s.max_entrants

仅当entrants_count<时才执行插入语句. max_entrants(您想要的).

This should only execute the insert statement when entrants_count < max_entrants (what you want).

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