CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Richard Cane
Richard Cane

Reputation: 33

Oracle after update trigger not performing correctly

I have two tables, tblapplication and tblapplicationhistory. tblapplicationhistory is an archive of every change made to the status of applications in the application table. A student in the application table can have many applications.

When an application status becomes "Offer accepted", the status ID is set to 7. This is then reflected in both the application and applicationhistory table. At this point, all other application statuses for the given student should be set to 8, "Offer rejected".

create or replace
TRIGGER trg_declineapplications AFTER UPDATE ON tblapplicationhistory FOR EACH ROW

BEGIN
   IF :NEW.statusid_fk_nn = 7 THEN
      UPDATE tblapplication
      SET statusid_fk_nn = 8
      WHERE studentrecordnumber_fk_nn = ( SELECT studentrecordnumber_fk_nn
                                          FROM tblapplication
                                          WHERE applicationid_pk_nn = :NEW.applicationid_fk_nn
                                        )
      AND applicationid_pk_nn != :NEW.applicationid_fk_nn;
   END IF;
END;

The trigger is compiled without errors, and the trigger activates without returning any SQL errors, but performs no computation on any rows in the application table. There must be an error in the logic of the trigger in that case, but I do not see it.

To my mind, if the updated row in tblapplicationhistory contains statusID 7, then an update is performed on the application table, setting statusID to 8 for every application belonging to the same student other than the accepted application.

More information can be given if required.

Table definitions:

tblapplication:
applicationid_pk_nn
studentrecordnumber_fk_nn
jobid_fk_nn
statusid_fk_nn

tblapplicationhistory:
applicationid_fk_nn
statusid_fk_nn
datechanged_nn
applicationhistoryid_pk_nn

In tblapplication, the primary key is applicationid_pk_nn and all other field are foreign keys.

In tblapplicationhistory, applicationhistoryid_pk_nn is the primary key. statusid_fk_nn is retreived from tblapplication with applicationid_fk_nn.

Upvotes: 0

Views: 394

Answers (1)

David Aldridge
David Aldridge

Reputation: 52336

The trigger method does not look very robust.

How about when you update the records to set the application accepted/rejected you do something like this:

 update my_table
 set    status_id = case my_table.application_id
                      when application_id_for_accepted_offer then 7
                      else 8
                    end
 where  student_id = student_id_to_update;

Upvotes: 1

Related Questions