Oracle DB trigger compiles with warnings. No error available

Question

This is my first db trigger. It compiles with warnings and therefore doesn't work. I've re-read the Oracle docs and searched online but can't work out where I'm going wrong. Any help with my trigger below would gratefully received.

    CREATE OR REPLACE TRIGGER oa_mhd_update AFTER
      INSERT ON men_mhd FOR EACH row WHEN (new.mhd_tktc LIKE 'OA_A_%'
        OR new.mhd_tktc LIKE 'OA_T_%'
        OR new.mhd_tktc LIKE 'OA_M_%') 
      DECLARE seq_var NVARCHAR2 (20);
      BEGIN
        SELECT (MAX (seq) + 1) into seq_var FROM oa_mhd_data;
        INSERT
        INTO oa_mhd_data
          (
            mhd_code,
            seq,
            mhd_mst1,
            mhd_mst2,
            mhd_cred,
            mhd_cret,
            mhd_tsks,
            mhd_msgs,
            mhd_tktc,
            mhd_tref,
            mhd_actn,
            mhd_eref,
            mhd_subj,
            mhd_udf1,
            mhd_udf2,
            mhd_udf3,
            mhd_udf4,
            mhd_udf5,
            mhd_udf6,
            mhd_udf7,
            mhd_udf8,
            mhd_udf9,
            mhd_udfa,
            mhd_udfb,
            mhd_udfc,
            mhd_udfd,
            mhd_udfe,
            mhd_udff,
            mhd_udfg,
            mhd_udfh,
            mhd_udfi,
            mhd_udfj,
            mhd_udfk,
            mhd_updd,
            mhd_begd,
            mhd_begt,
            mhd_endd,
            mhd_endt,
            mhd_mrcc,
            mhd_mhdc,
            mhd_mscc,
            mhd_pprc,
            mhd_ppss,
            mhd_inst
          )
          VALUES
          (
            :new.mhd_code
            seq_var,
            :new.mhd_mst1,
            :new.mhd_mst2,
            :new.mhd_cred,
            :new.mhd_cret,
            :new.mhd_tsks,
            :new.mhd_msgs,
            :new.mhd_tktc,
            :new.mhd_tref,
            :new.mhd_actn,
            :new.mhd_eref,
            :new.mhd_subj,
            :new.mhd_udf1,
            :new.mhd_udf2,
            :new.mhd_udf3,
            :new.mhd_udf4,
            :new.mhd_udf5,
            :new.mhd_udf6,
            :new.mhd_udf7,
            :new.mhd_udf8,
            :new.mhd_udf9,
            :new.mhd_udfa,
            :new.mhd_udfb,
            :new.mhd_udfc,
            :new.mhd_udfd,
            :new.mhd_udfe,
            :new.mhd_udff,
            :new.mhd_udfg,
            :new.mhd_udfh,
            :new.mhd_udfi,
            :new.mhd_udfj,
            :new.mhd_udfk,
            :new.mhd_updd,
            :new.mhd_begd,
            :new.mhd_begt,
            :new.mhd_endd,
            :new.mhd_endt,
            :new.mhd_mrcc,
            :new.mhd_mhdc,
            :new.mhd_mscc,
            :new.mhd_pprc,
            :new.mhd_ppss,
            :new.mhd_inst
          )
      END;
    /

Answer 1

Hi there are two syntactical errors First please add a comma between two values you are inserting

 VALUES
      (
        :new.mhd_,
        seq_var,
        :new.mhd_mst1,...

and second please add a semi colon at he end of insert statement

 ...
 :new.mhd_pprc,
        :new.mhd_ppss,
        :new.mhd_inst
      );

Hope this will solve your problem

Answer 2

You're missing a comma between the first two elements of the values clause, and a semi-colon at the end of the insert statement:

      VALUES
      (
        :new.mhd_code
        seq_var,
        :new.mhd_mst1,
        ...
        :new.mhd_ppss,
        :new.mhd_inst
      )

... should be:

      VALUES
      (
        :new.mhd_code,
        seq_var,
        :new.mhd_mst1,
        ...
        :new.mhd_ppss,
        :new.mhd_inst
      );

Odd that you can't see the error though.

---

Incidentally, the max(seq) + 1 from ... pattern isn't reliable in a multi-user environment. It would be more normal (and safer) to use a proper sequence to generate that value.