|
|
| Re: adding new rows only on changes?: proper sql statement [message #370289 is a reply to message #370288] |
Mon, 02 August 1999 16:55   |
Nell Rehn
Messages: 1
Registered: August 1999
|
Junior Member |
|
|
No a unique index does not do what I want
because if I have:
date_and_time freenodes totalnodes
------------------------------------
... 6 8
... 7 8
... 6 8
Then I want all three rows preserved. It is
only when I have
date_and_time freenodes totalnodes
--------------------------------------
... 6 8
... 6 8
... 7 8
that I would like to never have inserted that
second row.
Nell
|
|
|
|
| Re: adding new rows only on changes?: proper sql statement [message #370290 is a reply to message #370289] |
Tue, 03 August 1999 12:51 |
hmg
Messages: 40
Registered: March 1999
|
Member |
|
|
I agree with the solution below which is using a trigger, but it's not possible to make a select within a row level trigger on the same table to determine the values for the max(date_and_time) because then the mutating table problem occur. My workaround is to use a shadow table, look at the script below.
create table nodes (
date_and_time DATE,
free_nodes NUMBER(3),
total_nodes NUMBER(3) );
insert into nodes values ( to_date('1999/08/01 03:46:01','YYYY/MM/DD HH24:MI:SS'), 3, 5 );
insert into nodes values ( to_date('1999/08/02 01:12:14','YYYY/MM/DD HH24:MI:SS'), 7, 14 );
insert into nodes values ( to_date('1999/08/02 02:14:41','YYYY/MM/DD HH24:MI:SS'), 6, 8 );
create table nodes_shadow as
select * from nodes;
create or replace trigger check_insert
before insert on nodes
for each row
declare
do_insert boolean := false;
cursor cur_shadow is
select * from nodes_shadow
order by date_and_time desc;
shadow_rec cur_shadow%rowtype;
begin
open cur_shadow;
fetch cur_shadow into shadow_rec;
if cur_shadow%notfound then
do_insert := true;
close cur_shadow;
end if;
close cur_shadow;
if (:new.free_nodes != shadow_rec.free_nodes) or (:new.total_nodes != shadow_rec.total_nodes) then
do_insert := true;
end if;
if ( do_insert ) then
insert into nodes_shadow values ( :new.date_and_time, :new.free_nodes, :new.total_nodes );
else
raise_application_error( -20000, 'no new values !');
end if;
end;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
