> Nedee & Co wrote:
> >
> > Hi Oracle-fans
> >
> > I am having a really difficult problem.
> >
> > We have the following situation :
> > we should do a control on a certain group of records in a certain
> > table.
> > Let us simplify the example and say that we have a table A, with two
> > columns : X en Y. For the sake of database consistency the sum of
> > column
> > Y for records which have the same X must always be zero.
> >
> > I would like to implement this with database triggers, instead of in
> > every program that manipulates data in this table. The problem with
> > database triggers is in two ways.
> > First : within the triggers you can't select the rows in A with the
> > same X, because of an Oracle Error 4091 (Mutating table thing).
> > Second : when working with a package stack you still have the problem
> > that the user inserts a record (the triggers pushes it onto the
> > stack),
> > then the user quits the application, the records stays there.
> >
Have you tried the AFTER triggers? Try something like this (warning, I
haven't access to Oracle at the moment, so this is untested):
create trigger aidu_test
after insert or delete or update of y
on test
declare
cursor bad_records is
select x, sum(y)
from test
group by x
having sum(y) != 0;
bad_x test.x%type;
bad_y test.y%type;
begin
open bad_records;
fetch bad_records into bad_x, bad_y;
if bad_records%found then
raise_application_error(-20001, 'Record identified with ' || bad_x || '
has invalid sum of ' || to_char(bad_y));
end if;
close bad_records;
end aidu_test;
/
Let me know if that doesn't work.
Diana Duncan
art2mis_at_apk.net
Received on Fri Jan 24 1997 - 00:00:00 CST
