Home » RDBMS Server » Server Administration » Re: adding new rows only on changes?: proper sql statement
Re: adding new rows only on changes?: proper sql statement [message #370288] Mon, 02 August 1999 16:15 Go to next message
Bruce
Messages: 21
Registered: November 1998
Junior Member
Try a unique index.
Re: adding new rows only on changes?: proper sql statement [message #370289 is a reply to message #370288] Mon, 02 August 1999 16:55 Go to previous messageGo to next message
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 Go to previous message
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;
/
Previous Topic: Re: How do you create a fixed width file?
Next Topic: Re: prompting in PL
Goto Forum:
  


Current Time: Wed Oct 28 07:21:08 CDT 2020