How to create a trigger which will auto create sequence ID-CodePudding

I have been trying to implement the logic. But as only basic knowledge on SQL.

I have a below following table

create upload
(
  fileblob blob,
  mimetype varchar2(4000 char),
  filename varchar2(4000 char),
  createddate date,
  getsequence varchar2(4000 char) 
)

I have following data in it

fileblob,mimetype,filename,createddate,getsequeence
(BLOB)  ,text/plain , ABC.txt , 12-JULY-19
(BLOB)  ,text/plain , XYZ.txt , 13-JULY-19
(BLOB)  ,text/plain , HTC.txt , 14-JULY-19
(BLOB)  ,text/plain , LKG.txt , 15-JULY-19

I need to write some trigger as soon as the insert is performed depending upon createddate a sequence should be automatically inserted to sequence column for respective record.

Expected output :

fileblob,mimetype,filename,createddate,getsequeence
(BLOB)  ,text/plain , ABC.txt , 12-JULY-22,20220712_002414 
(BLOB)  ,text/plain , XYZ.txt , 13-JULY-22,20220713_003513
(BLOB)  ,text/plain , HTC.txt , 14-JULY-22,20220714_004510
(BLOB)  ,text/plain , LKG.txt , 15-JULY-22,20220715_001711

The sequence will be like YYYYMMDD_HHMMSS it comes from createddate as it is set to date. I will insert first 4 columns and last column data should be inserted base on createddate column by trigger itself.

This 4 column data is populated from my Oracle APEX web application using form

CodePudding user response：

You don't have a sequence column as you can still have non-unique values if two rows have the same createddate. Instead, you just have a formatted date. In this case, just convert the column to a virtual column:

create table upload
(
  fileblob    blob,
  mimetype    varchar2(4000 char),
  filename    varchar2(4000 char),
  createddate date,
  getsequence varchar2(4000 char)
              AS (TO_CHAR(createddate, 'YYYYMMDD_HH24MISS'))
);

If you do want a sequence value (where all the values are unique) then, from Oracle 12, you can use an identity column:

create table upload
(
  fileblob    blob,
  mimetype    varchar2(4000 char),
  filename    varchar2(4000 char),
  createddate date,
  getsequence NUMBER(10,0)
              GENERATED ALWAYS AS IDENTITY
              PRIMARY KEY
);