I have created a trigger based on which I want to assign column with some value.

For ex:- I have a column name BILL_CALCULATED_DATE with value 19-10-21 which is in dd-mm-yy format. I want to extract mm and if it is 10 then I want to assign it as October

Below is my trigger

CREATE OR REPLACE TRIGGER TRG_UPD_GTL_BILL
BEFORE INSERT OR UPDATE ON IPFEE_MST_INSRT_GTL
FOR EACH ROW
 BEGIN
 :new.BILL_CALCULATED_DATE := case :NEW.UPLOADED_MONTH
                     when '01' then 'January'
                     when '02' then 'February'
                     when '03' then 'March'
                     when '04' then 'April'
                     when '05' then 'May'
                     when '06' then 'June'
                     when '07' then 'July'
                     when '08' then 'August'
                     when '09' then 'September'
                     when '10' then 'October'
                     when '11' then 'November'
                     when '12' then 'December'
end;
end;

But the problem is

Before assigning month in the trigger I want to extract the month. How should I achieve it ? Please help

CodePudding user response：

As per my understanding your column is not in date format. First convert this into date and then use EXTRACT function -

SELECT EXTRACT(month FROM TO_DATE(BILL_CALCULATED_DATE, 'dd-mm-yy'))
  FROM IPFEE_MST_INSRT_GTL;

CodePudding user response：

Something like this?

SELECT EXTRACT(month FROM SYSDATE) from dual

CodePudding user response：

Assuming that your BILL_CALCULATED_DATE column is of the DATE data type and you want the UPLOADED_MONTH column to be the name of the month corresponding to BILL_CALCULATED_DATE's month then:

Do not use a trigger; use a virtual column then the column values will always be synchronised.

Either create the table using:

CREATE TABLE IPFEE_MST_INSRT_GTL(
  BILL_CALCULATED_DATE DATE,
  UPLOADED_MONTH      VARCHAR2(9)
    GENERATED ALWAYS AS (
      CAST(
        TO_CHAR(BILL_CALCULATED_DATE, 'fmMonth', 'NLS_DATE_LANGUAGE=English')
        AS VARCHAR2(9)
      )
    )
);

Or modify the existing table to drop the non-virtual column and add a virtual column:

ALTER TABLE IPFEE_MST_INSRT_GTL DROP COLUMN UPLOADED_MONTH;

ALTER TABLE IPFEE_MST_INSRT_GTL
  ADD UPLOADED_MONTH VARCHAR2(9)
    GENERATED ALWAYS AS (
      CAST(
        TO_CHAR(BILL_CALCULATED_DATE, 'fmMonth', 'NLS_DATE_LANGUAGE=English')
        AS VARCHAR2(9)
      )
    );

If you must use a trigger and a real column then:

CREATE OR REPLACE TRIGGER TRG_UPD_GTL_BILL
BEFORE INSERT OR UPDATE ON IPFEE_MST_INSRT_GTL
FOR EACH ROW
BEGIN
  :NEW.UPLOADED_MONTH := TO_CHAR(
                           :new.BILL_CALCULATED_DATE,
                           'fmMonth',
                           'NLS_DATE_LANGUAGE=English'
                         );
END;
/

If your BILL_CALCULATED_DATE column is a CHAR or VARCHAR2 data type then:

1. Change that column to a DATE.
2. If you cannot change it to a DATE then go and talk to someone who can change it to a DATE.
3. If you really, really cannot change it to a DATE then spend time despairing about the inability to follow best practice, try to convince people who may have the ability to do something about it that you should change things and then replace BILL_CALCULATED_DATE with TO_DATE(BILL_CALCULATED_DATE, 'DD-MM-YY') or :new.BILL_CALCULATED_DATE with TO_DATE(:new.BILL_CALCULATED_DATE, 'DD-MM-YY') in the snippets above.

db<>fiddle here