Oracle Compound Trigger : PLS-00103 Encountered when Attempting to get Max of Date Column

I am trying to construct a trigger that will update Table B with Max value end_date column when the corresponding end_date of table A is updated.

This is so I can calculated and save the date difference between a date that I have saved prior to this, and the max end date that I want constantly updated from Table A.

So far compound trigger seems like a valid solution given that my row trigger does not fully cover all the use cases, however, I got the error stated in the title.

Full error:

PLS-00103: Encountered the symbol “)” when expecting one of the following: current delete exists prior

I have tried to find out any syntax error that I might have overlooked, but so far I can’t manage to progress.

The database version seemed to be correct as well, as I am using oracle 11g which should support compound triggers.

This is the aforementioned SQL:

CREATE OR REPLACE TRIGGER DATE_DIFF_CALC_A
FOR UPDATE OR INSERT OF END_ON ON TABLE_A
COMPOUND TRIGGER
   TYPE temp_record IS RECORD (
      COUNTER           NUMBER,
      B_ID             TABLE_B.id%TYPE,
      U_ID             TABLE_B.U_ID%TYPE,
      U_TYPE           TABLE_B.U_TYPE%TYPE,
      U_VOL_NO         TABLE_B.U_VOL_NO%TYPE,
      MAX_DATE         TABLE_B.MAX_DATE%TYPE,
   ); 

   TYPE temp_table IS TABLE OF temp_record INDEX BY PLS_INTEGER; 

   row_record temp_table;

   AFTER EACH ROW IS
     COUNTER NUMBER;
     MAX_DATE DATE;
     B_ID NUMBER;
   BEGIN
      SELECT COUNT(*), MAX_DATE, ID
      INTO COUNTER,
           MAX_DATE,
           B_ID
      FROM TABLE_B
      WHERE U_ID = :NEW.U_ID
        AND U_TYPE = :NEW.TYPE AND U_VOL_NO = :NEW.U_VOL_NO GROUP BY ID, MAX_DATE;
-- Tool I am using for query tells me there is an error in this line, but there's nothing here :(
      row_record(row_record.COUNT + 1).COUNTER := COUNTER;
      row_record(row_record.COUNT).MAX_DATE := MAX_DATE;
      row_record(row_record.COUNT).B_ID := B_ID;
      row_record(row_record.COUNT).U_ID := :NEW.U_ID;
      row_record(row_record.COUNT).U_TYPE := :NEW.TYPE;
      row_record(row_record.COUNT).U_VOL_NO := :NEW.U_VOL_NO;
   END AFTER EACH ROW; 

   AFTER STATEMENT IS 
      new_max_enddate   TABLE_B.MAX_DATE%TYPE;
   BEGIN
      FOR indx IN 1 .. row_record.COUNT
      LOOP
         SELECT MAX(a.end_on)
          INTO new_max_enddate
          from TABLE_A a, TABLE_C C 
          where a.c_id = c.id and UPPER(c.place_name) not like 'XTEST%'
          and a.status not in ('1', '2', '3', '4')
          and a.U_ID = row_record(indx).U_ID
          and a.TYPE = row_record(indx).U_TYPE
          and a.U_VOL_NO = row_record(indx).U_VOL_NO;

        IF row_record(indx).COUNTER = 1 THEN
           IF new_max_enddate > row_record(indx).MAX_DATE THEN 
              UPDATE TABLE_B 
                 SET MAX_DATE = new_max_enddate 
                WHERE U_ID = :NEW.U_ID
                AND U_TYPE = :NEW.TYPE
                AND U_VOL_NO = :NEW.U_VOL_NO;
           END IF;
        END IF;
      END LOOP; 
   END AFTER STATEMENT; 
END DATE_DIFF_CALC_A;

I commented on the line of code that the tool I am using tells me the error is at, if it helps at all.

Do forgive me if this problem is suppose to be easy to solve; I am still not that familiar with PL/SQL and would love to learn more.

Answer

Please remove the comma after TABLE_B.MAX_DATE%TYPE

Also,

FOR UPDATE OR INSERT OF END_ON ON TABLE_A should be FOR UPDATE OF END_ON OR INSERT ON

Advertisement

Answer