实现后的Oracle SQL触发器变异 [英] Oracle SQL Trigger mutating when implemeneted
本文介绍了实现后的Oracle SQL触发器变异的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
该触发器在执行插入或更新操作时遇到麻烦.触发器创建成功但没有错误.目的是检查invoice_total是否大于payment_total + credit_total的总和.任何帮助将不胜感激:
Having trouble with this trigger when it runs suring a insert or update operation. The trigger gets created without errors though. The objective is to check if the invoice_total is bigger than the total of payment_total + credit_total. Any help would be much appreciated:
Create or Replace Trigger invoices_before_update_payment
Before Insert or Update On invoices
For Each Row
Declare
InvTotal Number;
t_payment Number;
t_credit Number;
Begin
select invoice_total, payment_total, credit_total
Into
InvTotal, t_payment, t_credit
From invoices
Where invoice_id = :New.invoice_id;
DBMS_OUTPUT.PUT_LINE(InvTotal);
If (InvTotal) < (t_payment + t_credit)
Then
Raise_application_error(-20005, 'Invoice total is larger than the credit total and payment total');
End if;
End;
/
推荐答案
您无法从要为其触发触发器的表中进行选择,否则会出现此错误.
You can't select from the table that a trigger is firing for, else you get this error.
为什么不简单地在触发器中使用:new值?
Why not simply use the :new values in your trigger?
BEGIN
IF :new.invoice_total > :new.payment_total + :new.credit_total THEN
我根据错误消息的语义反转了关系运算符.
I reversed the relational operator based on the error message semantics.
这篇关于实现后的Oracle SQL触发器变异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文