SQL & PL/SQL

Hello experts!

I have a problem with a trigger I'm trying to create. It compiles but I receive an error message when the trigger fires.

The scenario is as follows:

I have a table TBL_PUNKTDATEN. Whenever the status for a record in that table is changed to 3 or 4 I need the trigger to insert a dataset into my target table (TBL_ARBEIT_ZU_GEBIET).
However, the trigger must only insert data when there's no existing record in the target table. The condition that specifies whether there is a dataset or not, is the field LNG_GEBIET, which exists in the source as well as in the target table. Hence, for each LNG_GEBIET there can be only one dataset in the target table!

I created a trigger using the following code. However it doesn't work.

Maybe you'll see what I want to achieve when having a look at my code.

Can you please help me out on this one?

Thanks a lot!

Sebastian

create or replace
TRIGGER set_status_arbeit_zu_gebiet AFTER
  UPDATE ON TBL_PUNKTDATEN FOR EACH ROW WHEN(new.INT_STATUS=3 or new.INT_STATUS=4)

declare
    cursor c is select LNG_GEBIET from TBL_ARBEIT_ZU_GEBIET where PNUM = 1114 and LNG_GEBIET=:new.LNG_GEBIET;
    x number;
begin
    open c;
    fetch c into x;
    if c%NOTFOUND  then  
    INSERT INTO TBL_ARBEIT_ZU_GEBIET
        ( 
        LNG_GEBIET,
          LNG_ARBEITSSCHRITT,
          PNUM,
          INT_BEARBEITER,
          DATE_DATUM, 
          GEPL_DATUM
        )
        VALUES
        (:new.LNG_GEBIET,
         52,
         1114,
         895,
         sysdate,
         to_date('01.01.1990', 'DD.MM.YYYY')
        ); 
    end if;
end;

Well, on the first insert the code works properly and inserts the recordset as expected. However, if there is an existing recordset where :new.LNG_GEBIET matches LNG_Gebiet in my target table, I receive the ORA-06502 error!

Maybe that spcifies it a little bit???

Hope you can help me!

Thank you!

Edited by: skahlert on 23.09.2009 10:26

Edited by: skahlert on 23.09.2009 10:28