Home » SQL & PL/SQL » SQL & PL/SQL » PROBLEM WITH AFTER INSERT TRIGGER (DATA NOT THERE YET!!)
PROBLEM WITH AFTER INSERT TRIGGER (DATA NOT THERE YET!!) [message #214639] Wed, 17 January 2007 06:10 Go to next message
Zeevaarders
Messages: 1
Registered: January 2007
Location: Netherlands
Junior Member
hello everybody,

i have a serious problem with one of my triggers. the code is as follows:


CREATE OR REPLACE TRIGGER OSIRIS.ostr_hsz_eai_schrijf_student_i
   AFTER INSERT
   ON OST_STUDENT_CURSUS
   FOR EACH ROW

DECLARE

   student       hsz_eai_stud_cur_ins;
   werkvorm      hsz_eai_stud_cur_wv;
   mailpersoon   hsz_eai_mail_pers;
   msg_txt       VARCHAR2 (500);
   RESULT        BINARY_INTEGER;
   i             BINARY_INTEGER;



   CURSOR c_2
   IS
      SELECT achternaam, e_mail_adres, voorvoegsels, voorletters
        FROM OST_STUDENT
       WHERE studentnummer = :NEW.studentnummer;
	   
	   CURSOR c_1
   IS
      SELECT cw.werkvorm, cw.omschrijving, cw.aantal_bijeenkomsten,
             cw.contacturen, cw.aanwezigheidsplicht, cw.deelnemers_maximum,
             cw.deelnemers_minimum, cw.aantal_groepen, cw.omschrijving_en,
             scw.groepsnummer, scw.inschrijfdatum, scw.bevestigingsdatum,
             scw.blok
        FROM OST_STUDENT_CURSUS_WERKVORM scw JOIN OST_CURSUS_WERKVORM cw
             ON (    scw.collegejaar = cw.collegejaar
                 AND scw.cursus = cw.cursus
                 AND scw.werkvorm = cw.werkvorm
                 AND scw.voltijd_deeltijd = cw.voltijd_deeltijd
                )
       WHERE (    scw.studentnummer = :NEW.studentnummer
              AND scw.cursus = :NEW.cursus
              AND scw.collegejaar = :NEW.collegejaar
              AND scw.aanvangsblok = :NEW.aanvangsblok
              AND scw.voltijd_deeltijd = :NEW.voltijd_deeltijd
             );
BEGIN


   
   i := 1;
   student :=
      hsz_eai_stud_cur_ins (:NEW.studentnummer,
                            :NEW.cursus,
                            :NEW.collegejaar,
                            :NEW.aanvangsblok,
                            :NEW.voltijd_deeltijd,
                            :NEW.onderwerp,
                            :NEW.examendoel,
                            :NEW.voorkeursgroep,
                            :NEW.factuur_prijs,
                            :NEW.factuur_werkgever,
                            hsz_eai_stud_cur_wv_array (),
                            hsz_eai_mail_pers_array ()
                           );


   
   FOR r_c2 IN c_2
   LOOP
      mailpersoon :=
         hsz_eai_mail_pers (r_c2.achternaam,
                            r_c2.voorvoegsels,
                            r_c2.voorletters,
                            r_c2.e_mail_adres
                           );
      student.mailpersonen.EXTEND ();
      student.mailpersonen (i) := mailpersoon;
      i := i + 1;
   END LOOP;
   

   
  
   FOR r_c1 IN c_1
   LOOP
      werkvorm :=
         hsz_eai_stud_cur_wv (r_c1.werkvorm,
                              r_c1.omschrijving,
                              r_c1.aantal_bijeenkomsten,
                              r_c1.contacturen,
                              r_c1.aanwezigheidsplicht,
                              r_c1.deelnemers_maximum,
                              r_c1.deelnemers_minimum,
                              r_c1.aantal_groepen,
                              r_c1.omschrijving_en,
                              r_c1.blok,
                              r_c1.groepsnummer,
                              r_c1.inschrijfdatum,
                              r_c1.bevestigingsdatum
                             );
      student.werkvormen.EXTEND ();
      student.werkvormen (i) := werkvorm;
      i := i + 1;
   END LOOP;

   i := 1;

   RESULT := student.schrijfstudentin();
   
EXCEPTION
   WHEN OTHERS
   THEN
      msg_txt :=
            'Er is een fout opgetreden'
         || ' sqlerror='
         || SQLERRM (SQLCODE)
         || ' rowcount='
         || TO_CHAR (SQL%ROWCOUNT);
      RAISE_APPLICATION_ERROR (-20001, msg_txt);
END;
/
/



My problem is as follows: when this trigger is raised i create a cursor (C_1) with the information inserted in the table in the where clause. This creates a result set that i need for further processing. Then I open the cursor and want to retrieve information from this other table (OST_STUDENT_CURSUS_WERKVORM) (where also rows are being added, in the same transaction).

Except this information is not yet there at that moment. My cursor is therefore empty.

I am looking for a way to let the trigger wait till the data is inserted in the other table so that my cursor contains the desired result set form the other table (and i dont mean using a sleep function).

This is very important for me since im doing a project for my university and i have to get this working!!!!!

Many thx to the person who can help me with this
Re: PROBLEM WITH AFTER INSERT TRIGGER (DATA NOT THERE YET!!) [message #214672 is a reply to message #214639] Wed, 17 January 2007 07:49 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
No, triggers aren't designed to wait for other transactions. It wouldn't really be desirable to insert into a table and then have the insert hang while it waits for some data to appear in another table. What would happen if you were inserting 15 rows at the same time and 14 of them were OK, but one failed. As it's part of a single transaction, all 15 would be held up. Is that really what you want ? As you imply, you could probably do it using a sleep function and a polling mechanism, but I think it would be a crass solution.

You really need to do this through the application that creates the data, by ensuring that tables are populated in the correct order. Maybe you could control the process of populating these tables by using a single stored procedure rather than lots of separate insert statements. If the data comes in a different times, you could put the OST_STUDENT_CURSUS
rows into a sort of staging table first and have a background process through something like dbms_job that validates that all the linked tables are populated before moving rows to the proper destination.
Re: PROBLEM WITH AFTER INSERT TRIGGER (DATA NOT THERE YET!!) [message #214694 is a reply to message #214672] Wed, 17 January 2007 10:48 Go to previous messageGo to next message
2042004Zeevaarders
Messages: 3
Registered: January 2007
Location: Netherlands
Junior Member
thx for your reaction.

the problem is that i cant change anything in the application that creates the data because it is a 'black box'. I could send the inserted record in OST-STUDENT_CURSUS to an temp tbl and then poll it periodically to see which records are new. With that info i can then fill my object with the proper data. But i dont know how to implement a periodic polling mechanism in oracle. Do you have mighyt have information about implementing that?

Thx a lot mate!

Kim
Re: PROBLEM WITH AFTER INSERT TRIGGER (DATA NOT THERE YET!!) [message #214695 is a reply to message #214639] Wed, 17 January 2007 10:56 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Have you looked at the DBMS_JOB utility ? It's a sort of batch system, which enables you to schedule jobs to run at certain times (similar to UNIX cron). It would be easy to set up a job to poll for new data every 5 minutes.
Re: PROBLEM WITH AFTER INSERT TRIGGER (DATA NOT THERE YET!!) [message #214725 is a reply to message #214695] Wed, 17 January 2007 15:25 Go to previous messageGo to next message
2042004Zeevaarders
Messages: 3
Registered: January 2007
Location: Netherlands
Junior Member
I read some info about the package, but i still dont know how i can identify new data in the table.

The package only lets you execute a procedure at a regular interval. The problem is that i need to indentify which records are new since the last 'poll'. I'm thinking about adding a timestamp field to the temporary table. But then still i dont know how to compare the timestamp with the last time the table was polled. If i know this i could select the information fromm the table in the following form:


select ...
from tmp_tbl
where timestamp is > last_poll_timestamp



i could then put this statement in a cursor (how do i het the value of the last_poll_timestamp? )and process the result rows one by one, filling my object and send it out of oracle.

Could someone please help me with this?

Many thx,

Kim

PS
Re: PROBLEM WITH AFTER INSERT TRIGGER (DATA NOT THERE YET!!) [message #214815 is a reply to message #214639] Thu, 18 January 2007 03:21 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I think using timestamps would be quite dodgy. It only needs someone to change the system time (say, when daylight savings kicks in) and you could end up missing out a whole load of records.

A better solution might be to add a flag to the table to say whether the record has been processed. You can then select records where the flag is set to New and then update the flag when the record has been successfully transferred. If you select the rowid of the records which are to be transferred to the main table (or do a select for update and update where current of cursor), then the update should be quite efficient.
Re: PROBLEM WITH AFTER INSERT TRIGGER (DATA NOT THERE YET!!) [message #214969 is a reply to message #214639] Thu, 18 January 2007 15:45 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Since your temp table will only be used to hold a record until it can be processed, then do the following.

Have an ID column in your temp table and use a sequence to populate this column when the row is inserted. You scheduled job will run every 5 minutes and you will use a cursor to return all the rows CURRENTLY in the table. As you loop through them and insert into the real tables, simply delete the specific row by using it's ID column.
Previous Topic: Copy database value in text file
Next Topic: SET DEFAULT in a trigger
Goto Forum:
  


Current Time: Wed Dec 07 14:35:06 CST 2016

Total time taken to generate the page: 0.10630 seconds