Re: trigger puzzlement

From: Raj Jamadagni <rjamadagni_at_hotmail.com>
Date: 15 Feb 2004 08:27:55 -0800
Message-ID: <76772c3f.0402150827.62660517_at_posting.google.com>

Assuming you have only one row in t2 for each date ... your problem might be just because of date and times ...

Try ...

BEGIN
-- test if record has already been inserted today   begin
    select 'TRUE'

      into v_exists 
      from DUAL 
     where EXISTS
           (select * 
              from test.t2
             where trunc(last_change) = Trunc(SYSDATE)
               and cmdy = :new.cmdy_sym);

  exception
    when no_data_found then
      v_exists := 'FALSE';
    when too_many_rows then
      raise_application_error('20001','Multiple rows found !!');     when others then
      raise;
  end;
  --
  if (v_exists = 'TRUE') then
    update test.t2

      set gv = :new.zone1
      where last_change = trunc(SYSDATE)
      and cmdy = :new.cmdy_sym;

  else
    insert into test.t2 ( last_change, cmdy, gv )      values (trunc(sysdate), :new.cmdy_sym, :new.zone1 );   end if;
END;
/

HTH
Raj Received on Sun Feb 15 2004 - 17:27:55 CET