0

I want to make every time I insert my document number data in sequence

CREATE OR REPLACE FUNCTION trig_ar_docno_pos_line_insert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$

DECLARE v_doc_no NUMERIC;
v_insertby varchar;
    BEGIN

        IF (TG_OP = 'DELETE') THEN

            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') then

            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') then             
           SELECT INTO v_doc_no count (*) FROM ar_transaksi_product
          WHERE ar_transaksi_product_key = NEW.ar_transaksi_product_key 
         AND to_char(NEW.insertdate, 'MMYY') LIKE to_char(now(), 'MMYY');

          IF COALESCE(v_doc_no,0)=0 THEN
          V_doc_no=1;
         ELSE

            v_doc_no=v_doc_no+1;


         END IF;



       UPDATE ar_transaksi_product SET

      doc_no = to_char(NEW.insertdate, 'MMYY')||to_char(v_doc_no, 'fm0000')

      WHERE ar_transaksi_product_key = NEW.ar_transaksi_product_key;



      RETURN NEW;
    END IF;

    END;

$function$

enter image description here

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Rahmad Nasution
  • 1
  • No need for a trigger, you should simply create a sequence and use it as `DEFAULT` value for the column. – Laurenz Albe Jan 23 '20 at 07:07
  • @LaurenzAlbe Does a sequence guarantee consecutive numbering though? – Bergi Jan 23 '20 at 07:40
  • Is that a `BEFORE` or `AFTER` trigger? I guess instead of running an `UPDATE` on the table, you should simply manipulate the new row before it's getting inserted, with `NEW.doc_no = …;` – Bergi Jan 23 '20 at 07:41

0 Answers0