Oracle 12c - Add Timestamp Trigger

SANPAT

Dear Friends

We had created 2 Fields : Add_date & Mod_date

I need to put the trigger on database schema itself when add the record it should take the timestamp in the add_date field and when modify the  record database trigger to update the mod_date with timestamp.

Sanjay

KayK

But that is the way your trigger will work. If you update a row the column add_date remains unchanged, mod_date is updated by the trigger.

< scott:[email protected] > drop   table FDS_PRODUCT;Table dropped.< scott:[email protected] > create table FDS_PRODUCT  2  ( id       number,  3    ADD_DATE TIMESTAMP(6) WITH TIME ZONE DEFAULT systimestamp NOT NULL,  4    mod_date timestamp);Table created.< scott:[email protected] >< scott:[email protected] > create or replace TRIGGER TRIGGER1  2  before insert or update on FDS_PRODUCT  3  for each row  4  begin  5    :new.mod_date := systimestamp;  6  END;  7  /Trigger created.
< scott:[email protected] > insert into FDS_PRODUCT ( id ) values ( 1);1 row created.< scott:[email protected] > select * from FDS_PRODUCT;        ID ADD_DATE                                                                    MOD_DATE---------- --------------------------------------------------------------------------- ------------------------         1 13.07.20 16:06:00,678132 +02:00                                             13.07.20 16:06:00,6782931 row selected.< scott:[email protected] > update FDS_PRODUCT set id = 100 where id = 1;1 row updated.< scott:[email protected] > select * from FDS_PRODUCT;        ID ADD_DATE                                                                    MOD_DATE---------- --------------------------------------------------------------------------- ------------------------       100 13.07.20 16:06:00,678132 +02:00                                             13.07.20 16:06:00,8176661 row selected.

KayK

Hi Sanjay,

maybe this link helps you

regards

Kay

SANPAT

Dear Kayak

I did the exactly the same , in the SQL developer created the trigger . My database Field is Mod_date , when i modify any entry in table mod_date which is timestamp is not updating . and its giving the error for trigger

create or replace TRIGGER TRIGGER1

before insert or update on FDS_PRODUCT

BEGIN

  for each row

  begin

  :new.mod_date := systimestamp;

END;

Sanjay

KayK

You trigger works for me

< scott:[email protected] > create table FDS_PRODUCT  2  ( id number,  3    mod_date timestamp);Tabelle wurde erstellt.< scott:[email protected] > create or replace TRIGGER TRIGGER1  2  before insert or update on FDS_PRODUCT  3  for each row                                   -- in your code there is an obsolete BEGIN  4    begin  5    :new.mod_date := systimestamp;  6  END;  7  /Trigger wurde erstellt.< scott:[email protected] > insert into FDS_PRODUCT ( id ) values ( 1);1 Zeile wurde erstellt.< scott:[email protected] > select * from FDS_PRODUCT;        ID MOD_DATE---------- ---------------------------------------------------------------------------        1 13.07.20 13:48:58,6128421 Zeile wurde ausgewählt.

SANPAT

Dear Kayk

In the table structure already having

ADD_DATE TIMESTAMP(6) WITH TIME ZONE DEFAULT systimestamp NOT NULL 

which is updating correctly whenever I am adding new record, but i need when i will modify the record that time the system should capture system date and store in Mod_date field.

Sanjay

KayK

But that is the way your trigger will work. If you update a row the column add_date remains unchanged, mod_date is updated by the trigger.

< scott:[email protected] > drop   table FDS_PRODUCT;Table dropped.< scott:[email protected] > create table FDS_PRODUCT  2  ( id       number,  3    ADD_DATE TIMESTAMP(6) WITH TIME ZONE DEFAULT systimestamp NOT NULL,  4    mod_date timestamp);Table created.< scott:[email protected] >< scott:[email protected] > create or replace TRIGGER TRIGGER1  2  before insert or update on FDS_PRODUCT  3  for each row  4  begin  5    :new.mod_date := systimestamp;  6  END;  7  /Trigger created.
< scott:[email protected] > insert into FDS_PRODUCT ( id ) values ( 1);1 row created.< scott:[email protected] > select * from FDS_PRODUCT;        ID ADD_DATE                                                                    MOD_DATE---------- --------------------------------------------------------------------------- ------------------------         1 13.07.20 16:06:00,678132 +02:00                                             13.07.20 16:06:00,6782931 row selected.< scott:[email protected] > update FDS_PRODUCT set id = 100 where id = 1;1 row updated.< scott:[email protected] > select * from FDS_PRODUCT;        ID ADD_DATE                                                                    MOD_DATE---------- --------------------------------------------------------------------------- ------------------------       100 13.07.20 16:06:00,678132 +02:00                                             13.07.20 16:06:00,8176661 row selected.