Forum Stats

  • 3,769,359 Users
  • 2,252,957 Discussions
  • 7,875,002 Comments

Discussions

Oracle 12c - Add Timestamp Trigger

SANPAT
SANPAT Member Posts: 1,086 Silver Badge
edited Jul 20, 2020 6:14AM in General Database Discussions

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

Tagged:
Mark D Powell

Best Answer

  • KayK
    KayK Member Posts: 1,685 Bronze Crown
    edited Jul 13, 2020 10:10AM Accepted Answer

    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.

Answers

  • KayK
    KayK Member Posts: 1,685 Bronze Crown
    edited Jul 13, 2020 6:46AM

    Hi Sanjay,

    maybe this link helps you

    regards

    Kay

  • SANPAT
    SANPAT Member Posts: 1,086 Silver Badge
    edited Jul 13, 2020 7:24AM

    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
    KayK Member Posts: 1,685 Bronze Crown
    edited Jul 13, 2020 7:58AM

    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.
    Mark D Powell
  • SANPAT
    SANPAT Member Posts: 1,086 Silver Badge
    edited Jul 13, 2020 9:22AM

    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
    KayK Member Posts: 1,685 Bronze Crown
    edited Jul 13, 2020 10:10AM Accepted Answer

    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.