| trigger and sum [message #10483] |
Mon, 26 January 2004 07:10  |
gr
Messages: 5
Registered: January 2002
|
Junior Member |
|
|
I have a problem using sum into a trigger,cause sum allways computes the sum before the last insertion.for example let's say we have the following table.
create table mytable
(owner varchar(10) not null,
money int not null,
primary key(owner));
And the following trigger
CREATE OR REPLACE TRIGGER MONEY_CHECK
BEFORE INSERT OR UPDATE ON MYTABLE
FOR EACH ROW
declare N_SUM MYTABLE.MONEY%TYPE;
BEGIN
SELECT SUM(MONEY) INTO N_SUM FROM MYTABLE
WHERE OWNER='bill';
IF(N_SUM > 1000 ) THEN
RAISE_APPLICATION_ERROR(-20010,'TOO RICH');
END IF;
END;
/
the problem is that sum in the trigger computes the sum of money before the insert and not after.If i try to make it an after trigger then i get a mutating table error.If anyone knows any workarounds i would be glad to hear them.Thanx.
|
|
|
|
|
|
|
|
|
|
| Re: trigger and sum [message #10489 is a reply to message #10488] |
Mon, 26 January 2004 08:34  |
gr
Messages: 5
Registered: January 2002
|
Junior Member |
|
|
|
This is just a simple example to show the case.In real i wanna use it after insert in a table where every user has a degree of ownership on a house and when a new owner is inserted a new degree is inserted and the total sum for a house sould not be over 100%.
|
|
|
|
| Re: trigger and sum [message #10490 is a reply to message #10489] |
Mon, 26 January 2004 09:45 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You might want to see this example I just posted in another forum here:
Using a package and trigger set to avoid mutating tables
Your triggers would be based on inserts and updates (instead of delete as in that example). The array would be a list of owner(s) involved in the insert. The process procedure would do the sum and raise the error if necessary.
Keep in mind though that this approach is not multi-user compatible (which may not matter in your case though). Two people could insert a row for an owner at the same time - individually the uncommited row may not exceed 100%, but after each commit, the total would violate your rule. Just an fyi...
[Updated on: Fri, 18 February 2005 23:32] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
