Oracle SQL Trigger mutating when implemeneted

2019-09-19 02:25发布

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;
/

1条回答
可以哭但决不认输i
2楼-- · 2019-09-19 03:20

You can't select from the table that a trigger is firing for, else you get this error.

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.

查看更多
登录 后发表回答