| Triggers/mutating [message #10998] |
Sun, 29 February 2004 12:59  |
Carl Hanson
Messages: 4
Registered: January 2004
|
Junior Member |
|
|
So I've made this trigger that will subtract the transaction total from the members balance, or delete the transaction if they don't have enough to complete the transaction. Problem is, I get the 'table is mutating, trigger/function may not see it' error when I try inserting a transaction for which the member's balance is not high enough. Any ideas on how I can change/redo the if structure to elminate this error?
Any help would be greatly appreciated..
CREATE OR REPLACE TRIGGER ReduceBalance
AFTER INSERT ON TRANSACTION
FOR EACH ROW
WHEN (new.TTYPE = 'BUY')
DECLARE b INTEGER;
BEGIN
SELECT BALANCE INTO b
FROM MEMBER m
WHERE m.MID = :new.MID;
IF b >= :new.AMOUNT THEN
UPDATE MEMBER m
SET BALANCE = BALANCE - :new.AMOUNT
WHERE m.MID = :new.MID;
ELSE
DELETE FROM TRANSACTION WHERE MID = :new.MID AND SYMBOL = :new.SYMBOL AND TDATE = :new.TDATE;
END IF;
END;
.
run
|
|
|
|
| Re: Triggers/mutating [message #10999 is a reply to message #10998] |
Sun, 29 February 2004 14:39  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It can't delete from tansaction because transaction is the table that the trigger is on, so it is mutating (changing). Instead of deleting, you should use raise_application_error, which will cause the insert to be rejected and an error message returned to the inserter. Please see the example below.
scott@ORA92> CREATE OR REPLACE TRIGGER ReduceBalance
2 AFTER INSERT ON transaction
3 FOR EACH ROW
4 WHEN (NEW.ttype = 'BUY')
5 DECLARE
6 b INTEGER;
7 BEGIN
8 SELECT balance
9 INTO b
10 FROM member m
11 WHERE m.mid = :NEW.mid;
12
13 IF b >= :NEW.amount THEN
14 UPDATE member m
15 SET balance = balance - :NEW.amount
16 WHERE m.mid = :NEW.mid;
17 ELSE
18 <b>RAISE_APPLICATION_ERROR (-20001, 'Insufficient funds!');</b>
19 END IF;
20 END ReduceBalance;
21 /
Trigger created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> -- starting data:
scott@ORA92> SELECT * FROM member
2 /
MID BALANCE
---------- ----------
1 1000
scott@ORA92> SELECT * FROM transaction
2 /
no rows selected
scott@ORA92> -- test valid insert:
scott@ORA92> INSERT INTO transaction (mid, ttype, amount) VALUES (1, 'BUY', 600)
2 /
1 row created.
scott@ORA92> SELECT * FROM member
2 /
MID BALANCE
---------- ----------
1 400
scott@ORA92> SELECT * FROM transaction
2 /
MID TTY AMOUNT
---------- --- ----------
1 BUY 600
scott@ORA92> -- test invalid insert due to insufficient funds:
scott@ORA92> INSERT INTO transaction (mid, ttype, amount) VALUES (1, 'BUY', 700)
2 /
INSERT INTO transaction (mid, ttype, amount) VALUES (1, 'BUY', 700)
*
ERROR at line 1:
ORA-20001: Insufficient funds!
ORA-06512: at "SCOTT.REDUCEBALANCE", line 14
ORA-04088: error during execution of trigger 'SCOTT.REDUCEBALANCE'
scott@ORA92> SELECT * FROM member
2 /
MID BALANCE
---------- ----------
1 400
scott@ORA92> SELECT * FROM transaction
2 /
MID TTY AMOUNT
---------- --- ----------
1 BUY 600
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
