CODEWITHSUNDEEP
oracle-databasetriggers
saravanan
saravanan

Reputation: 11

After Insert Trigger to modify one column and update newly inserted row values in Oracle

/* Formatted on 24/09/2015 09:36:39 (QP5 v5.215.12089.38647) */
CREATE OR REPLACE TRIGGER TR_student_PREF_sub_UPDATE
   AFTER INSERT OR UPDATE
   ON student
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
   v_preferred_sub_value   NUMBER (1);
   no_project_record       EXCEPTION;
BEGIN
   IF UPDATING OR INSERTING
   THEN
      IF (:NEW.is_sub = 0 AND :NEW.standard_id IS NOT NULL)
      THEN
         SELECT preferred_sub
           INTO v_preferred_sub_value
           FROM STANDARD
          WHERE standard_id = :NEW.standard_id;
      ELSIF (:NEW.is_sub = 1 AND :NEW.subject_id IS NOT NULL)
      THEN
         SELECT preferred_sub
           INTO v_preferred_sub_value
           FROM admin_subject.source
          WHERE source_id = :NEW.subject_id;
      END IF; /* v_preferred_sub_value IS NOT NULL THEN UPDATE preferred_sub VALUE IN student TABLE */

      IF (v_preferred_sub_value IS NOT NULL)
      THEN
         UPDATE student
            SET preferred_sub = v_preferred_sub_value
          WHERE student_id = :NEW.student_id;

         COMMIT;
      END IF;
   END IF;
EXCEPTION
   WHEN NO_PROJECT_RECORD
   THEN
      NULL;
END TR_student_PREF_sub_UPDATE;

Process : When the user click save action from UI, this invoke insert method in java code. While inserting values into subject table, trigger called and checking is_sub value if 0 then fetching preferred_sub value from standard table. If the is_sub = 1 then fetching preferred_sub from admin_subject.source schema table.

Based on v_preferred_sub_value it is trying to update subject table which was newly inserted row. I ran the insert action from UI, after the trigger called, i checked the subject table with preferred_sub column it shows null value.

Please help me, what i made the mistake over there. 

student table have student_id, subject_id, standard_id and is_sub values and more.
standard table & student table are in same schema.
source is different schema which has grant all privileged.

Upvotes: 0

Views: 3661

Answers (1)

Rene
Rene

Reputation: 10541

Change your trigger to a before trigger and assign the determined values to the appriopriate :new pseudo column. The database will take care of the rest.

IF (v_preferred_sub_value IS NOT NULL)
THEN
     :NEW.preferred_sub = v_preferred_sub_value;
END IF;

The value of :NEW.preferred_sub will now be placed in the table record.

Upvotes: 2

Related Questions