1

I have write a trigger that insert a Sequence next value number into a table column.But Now i want to Concatenate current Date + Sequence and save that result as a number into that column.

Sample :new.MED_RECORDNO (Number) = Date(YYYYMMDD) + Sequence(nextval)
       :new.MED_RECORDNO = 20160120 + 75 =  2016012075 (Number)

How i can do this.Please help me on this.thanks

Here is the code:

create or replace TRIGGER MED_RECORDNO_TRIGGER 
BEFORE INSERT ON TBL_MEDICAL_CENTER_BILLS
FOR EACH ROW
BEGIN
   :new.MED_RECORDNO  := to_number(to_char(sysdate, 'yyyymmdd')) + MED_RECORDNO_seq.nextval;
END;
user5005768Himadree
  • 1,375
  • 3
  • 23
  • 61

1 Answers1

1

As you said Concatenate, then you should use Concatenate operator. In PL/SQL '+' is for adding numerical value as plus (which is used for concatenate in other languages such as JavaScript. Hence, your trigger could be something like this: 

create or replace TRIGGER MED_RECORDNO_TRIGGER 
BEFORE INSERT ON TBL_MEDICAL_CENTER_BILLS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF INSERTING THEN
IF :NEW.MED_RECORDNO IS NULL THEN
SELECT to_number(to_char(sysdate, 'yyyymmdd')||TO_CHAR(MED_RECORDNO_seq.nextval)) INTO :new.MED_RECORDNO
FROM DUAL;
END IF;
END IF;
END;
Hawk
  • 5,060
  • 12
  • 49
  • 74
  • Thanks @Hawk.I want to know that, will this :NEW.MED_RECORDNO is taking as a number or string? i want as a number. – user5005768Himadree Jan 20 '16 at 07:23
  • 1
    It's a number, because we concatenated two string (date & sequence) into one string, then converted the resultant string to a number – Hawk Jan 20 '16 at 07:24
  • Thanks @Hawk so much.Very clear explanation.if you have any idea on DBMS_SCHEDULER job then also visit & share your thoughts on that .thanks for being very helpful. http://stackoverflow.com/questions/34651461/how-can-we-delete-a-table-row-automatically-after-a-specific-time-in-pl-sql – user5005768Himadree Jan 20 '16 at 07:29