oracle 在 pl/sql 中调用触发器内的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9376441/
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
Call a function within a trigger in pl/sql
提问by Posidon
I've searched through the Internet for some resource providing me with an example of how to call a function I've created from within a trigger in PL/SQL.
我在 Internet 上搜索了一些资源，这些资源为我提供了一个示例，说明如何调用我从 PL/SQL 中的触发器中创建的函数。
I've made a function called get_balancethat looks like this:
我创建了一个函数get_balance，它看起来像这样：
create or replace function get_balance(p_custno in number)
return number
as
v_balance account.balance%type;
begin
select balance
into v_balance
from account
where custno = p_custno;
return v_balance;
end;
/
Now I want to call this function from within a trigger to check the balance before a withdrawal. I tried to do it as follows but I think it's totally wrong:
现在我想从触发器中调用这个函数来检查提款前的余额。我尝试按如下方式进行，但我认为这是完全错误的：
create or replace trigger bifer_withdrawal
before insert on withdrawal
for each row
begin
if get_balance(p_custno) <= amount then
raise_application_error(-20001, 'Not enough money in account!');
end if;
end;
/
Could someone please provide a newbie with an example of how to call a function from within a trigger?
有人可以为新手提供一个如何从触发器中调用函数的示例吗？
回答by Ollie
You need to specify the value for p_custnoI have used the default NEWalias but see herefor trigger information, also René Nyffenegger's has a good explanationof the NEWand OLDusage:
您需要为p_custnoI have used the default NEWalias指定值，但请参阅此处了解触发器信息，René Nyffenegger对和用法也有很好的解释：NEWOLD
create or replace
trigger bifer_withdrawal
before insert on withdrawal
for each row
begin
if get_balance(:NEW.custno) <= amount
then
raise_application_error(-20001, 'Not enough money in account!');
end if;
end;
/
You will need to specify what AMOUNTis too. If it is a variable then declare it between the FOR EACH ROWand BEGINstatements:
您还需要指定什么AMOUNT是。如果它是一个变量，则在FOR EACH ROWandBEGIN语句之间声明它：
e.g.:
例如：
create or replace
trigger bifer_withdrawal
before insert on withdrawal
for each row
declare
c_amount CONSTANT account.balance%TYPE := 5000; -- Whatever limit you need
begin
if get_balance(:NEW.custno) <= c_amount
then
raise_application_error(-20001, 'Not enough money in account!');
end if;
end;
/
You should ask yourself, do you need to call the function?
You could easily wrap the cursor into the trigger and save yourself the function call.
Your answer will depend upon issues around whether you want to reuse the function elsewhere etc.
I'm not advocating one way over the other but it is something to consider.
你应该问问自己，你需要调用函数吗？
您可以轻松地将光标包装到触发器中并保存自己的函数调用。
您的答案将取决于您是否想在其他地方重用该功能等问题。
我不是提倡一种方式而不是另一种方式，但这是需要考虑的。
Hope it helps...
希望能帮助到你...
EDIT: After the two comments below, if AMOUNTis a column in the table WITHDRAWALthen:
编辑：在下面的两条评论之后，如果AMOUNT是表中的一列，WITHDRAWAL则：
create or replace
trigger bifer_withdrawal
before insert on withdrawal
for each row
begin
if get_balance(:NEW.custno) <= :NEW.amount
then
raise_application_error(-20001, 'Not enough money in account!');
end if;
end;
/
