CODEWITHSUNDEEP
oracle-databaseplsql
Thelnternet
Thelnternet

Reputation: 174

Trying to establish a trigger that counts rows after every update

I have two tables: SKN_ENJIN, and SKN_ENJIN_COUNT

SKN_ENJIN keeps track of usernames and emails. SKN_ENJIN_COUNT is being used to populate a chart for a dashboard report.

I created this trigger earlier today:

create or replace trigger "BI_SKN_ENJIN_COUNT_TG" 
  after insert or update or delete on "SKN_ENJIN"               

  DECLARE
  mCount    NUMBER;
  mDate     DATE;

begin 

  select COUNT(ID) into mCount from SKN_ENJIN where Status = 1;

  select TO_DATE(CURRENT_DATE, 'DD-MM-YYYY') into mDate from dual;

  MERGE INTO SKN_ENJIN_COUNT c
      USING dual d
      ON (c.Count_date = mDate)
    WHEN MATCHED THEN
      UPDATE SET c.Member_count = mCount
    WHEN NOT MATCHED THEN
      INSERT (Count_date, Member_count)
      VALUES (mDate, mCount);
end;

Up until about 10 minutes ago, the trigger worked beautifully. Suddenly, the trigger started throwing ORA-01843: not a valid month

I have tried changing CURRENT_DATE to SYSDATE(), I have tried changing TO_DATE to TO_CHAR. These approaches seemed to cause more errors to appear. What am I missing, and what should I change to solve this problem?

Upvotes: 0

Views: 44

Answers (2)

Luke Woodward
Luke Woodward

Reputation: 65064

There's no need to call TO_DATE on CURRENT_DATE or SYSDATE. These functions already return DATEs, so there's no need to do any conversion.

In fact, calling TO_DATE on something that is already a DATE forces Oracle to convert it to a string using NLS settings (NLS_DATE_FORMAT) and the convert it back to a date using a given date format picture. If the date format picture you are using does not match NLS_DATE_FORMAT, you will likely end up with errors or incorrect values.

Instead of writing

  select TO_DATE(CURRENT_DATE, 'DD-MM-YYYY') into mDate from dual;

you can write

  select CURRENT_DATE into mDate from dual;

or just

  mDate := CURRENT_DATE;

I don't know what type of the Count_date column in your SKN_ENJIN_COUNT table is, but if it is DATE, it is similarly incorrect to call TO_DATE on it.

Upvotes: 3

Thelnternet
Thelnternet

Reputation: 174

I think I found a solution while stumbling my way through it . It seems that the format of the date is extremely important. Earlier my formatting had been DD-MM-YYYY, when I used MM-DD-YYYY and just a touch of refactoring (ON (TO_DATE(c.Count_date, 'MM-DD-YYYY') = TO_DATE(mDate, 'MM-DD-YYYY')) the script worked without a fuss.

  select COUNT(ID) into mCount from SKN_ENJIN where Status = 1;

  select sysdate into mDate from dual;

  MERGE INTO SKN_ENJIN_COUNT c
      USING dual d
      ON (TO_DATE(c.Count_date, 'MM-DD-YYYY') = TO_DATE(mDate, 'MM-DD-YYYY'))
    WHEN MATCHED THEN
      UPDATE SET c.Member_count = mCount

Upvotes: -1

Related Questions