Oracle trigger on same table

Question

Im reviewing a trigger but im not able to understand the correct meaning.

This is the trigger DDL.

BEGIN
:new.C_DATE:=round((cast(sys_extract_utc(current_timestamp) as date) - TO_DATE('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400)*1000;
:new.U_DATE:=round((cast(sys_extract_utc(current_timestamp) as date) - TO_DATE('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400)*1000;
END;

From my understanding, they are just getting the current timestamp value as epoch format and convert it to UTC, and assign it to C_DATE and U_DATE column.

From information schema:

Trigger Type: BEFORE EACH ROW

Trigger Event: INSERT

Is it correct? And are they inserting this epoch value to the same table on C_DATE and U_DATE columns?

That's what it looks like, yes. Is that not what you are seeing when you test it locally? Are you just looking for a yes or no answer? — Justin Cave
actually I don't know what this trigger is doing. I want to understand it — TheDataGuy

devnull devnull · Accepted Answer · 2020-10-08T16:03:57

Trigger populates values using the Unix Epoch and multiply it by 1000 - i.e. Unix Epoch in Milliseconds :-)

Test it by executing on SQL*Plus:

select 
  to_char(round((cast(sys_extract_utc(SYSTIMESTAMP) as date) - 
                 TO_DATE('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) 
      * 86400) * 1000, 
      '999999999999999')
from 
   dual;

on a DB machine connected to a Stratum 0 Timeserver (normally production environments got this) and then update the page in browser from address

https://www.epochconverter.com/

SQL*PLUS:

TO_CHAR(ROUND((C
----------------
1602172506000

WEBPAGE:

1602172507

When you multiply webpage value by 1000 you get

1602172507000

which is only one second (or 1000 ms) away from SQL*Plus (I was quite fast switching :-)

Oracle trigger on same table

From information schema:

1 Answers