This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,588 Users
  • 2,269,776 Discussions
  • 7,916,825 Comments

Discussions

create a timestamp trigger.

748152
748152 Member Posts: 147
edited Nov 16, 2010 11:08PM in SQL & PL/SQL
would someone help me with the following table?

i am trying to create a timestamp column to store the datetime when the row was created.

when i test it with an insert, it gave me an error.

how to fix this timestamp column?

create table jobHistory
(
createTS timestamp
, ProcedureName varchar2(100)
, description varchar2(100)
)
;


create or replace trigger ins_jobHistory_timestamp
before insert
on .jobHistory
for each row
begin
:new.createTS := sysdate;
end;


insert into jobHistory (ProcedureName, Start_Or_End, description) values ('Hello', 's', 'testsing');

TRIGGER INS_JOBHISTORY_TIMESTAMP compiled
Errors: check compiler log
Tagged:

Best Answer

  • Saubhik
    Saubhik Member Posts: 5,957 Gold Crown
    edited May 28, 2010 10:01AM Answer ✓
    No need to create a trigger
    SQL> create table jobHistory
      2  (
      3  createTS timestamp default sysdate
      4  , ProcedureName varchar2(100)
      5  , description varchar2(100)
      6  )
      7  ;
    
    Table created.
    
    SQL> insert into jobHistory (ProcedureName,description) values ('Abcd','Xyz');
    
    1 row created.
    
    SQL> select * from jobHistory;
    
    CREATETS
    ---------------------------------------------------------------------------
    PROCEDURENAME
    --------------------------------------------------------------------------------
    DESCRIPTION
    --------------------------------------------------------------------------------
    28-MAY-10 07.27.40.000000 PM
    Abcd
    Xyz
    Still if you want to achieve with trigger then omit the default clause and
    SQL> create or replace trigger ins_jobHistory_timestamp
      2  before insert
      3  on jobHistory
      4  for each row
      5  begin
      6  :new.createTS := sysdate;
      7  end;
      8  /
    
    Trigger created.
    
    SQL> insert into jobHistory (ProcedureName,description) values ('ADS','FDR');
    
    1 row created.
    
    SQL> select * from jobHistory;
    
    CREATETS
    ---------------------------------------------------------------------------
    PROCEDURENAME
    --------------------------------------------------------------------------------
    DESCRIPTION
    --------------------------------------------------------------------------------
    28-MAY-10 07.27.40.000000 PM
    Abcd
    Xyz
    
    28-MAY-10 07.29.58.000000 PM
    ADS
    FDR
    Edited by: Saubhik on May 28, 2010 6:59 AM

Answers

  • Saubhik
    Saubhik Member Posts: 5,957 Gold Crown
    edited May 28, 2010 10:01AM Answer ✓
    No need to create a trigger
    SQL> create table jobHistory
      2  (
      3  createTS timestamp default sysdate
      4  , ProcedureName varchar2(100)
      5  , description varchar2(100)
      6  )
      7  ;
    
    Table created.
    
    SQL> insert into jobHistory (ProcedureName,description) values ('Abcd','Xyz');
    
    1 row created.
    
    SQL> select * from jobHistory;
    
    CREATETS
    ---------------------------------------------------------------------------
    PROCEDURENAME
    --------------------------------------------------------------------------------
    DESCRIPTION
    --------------------------------------------------------------------------------
    28-MAY-10 07.27.40.000000 PM
    Abcd
    Xyz
    Still if you want to achieve with trigger then omit the default clause and
    SQL> create or replace trigger ins_jobHistory_timestamp
      2  before insert
      3  on jobHistory
      4  for each row
      5  begin
      6  :new.createTS := sysdate;
      7  end;
      8  /
    
    Trigger created.
    
    SQL> insert into jobHistory (ProcedureName,description) values ('ADS','FDR');
    
    1 row created.
    
    SQL> select * from jobHistory;
    
    CREATETS
    ---------------------------------------------------------------------------
    PROCEDURENAME
    --------------------------------------------------------------------------------
    DESCRIPTION
    --------------------------------------------------------------------------------
    28-MAY-10 07.27.40.000000 PM
    Abcd
    Xyz
    
    28-MAY-10 07.29.58.000000 PM
    ADS
    FDR
    Edited by: Saubhik on May 28, 2010 6:59 AM
  • Peter Gjelstrup
    Peter Gjelstrup Member Posts: 4,128 Gold Trophy
    edited May 28, 2010 10:01AM
    Hi,

    Since you don't have createTS in you insert statement, you could do the trick with a default value, instead:
    create table jobhistory(createts        timestamp   default systimestamp 
                           ,procedurename   varchar2(100)
                           ,description     varchar2(100));
    It's hard to read your unformatted post, but it looks like you have a dot that shouldn't be there:
    on .jobHistory
    One more thing, you column is of type TIMESTAMP, yet you seem to put in a DATE. Better to have column as DATE then.

    Regards
    Peter
    Peter Gjelstrup
  • 748152
    748152 Member Posts: 147
    i like your choice.

    default is lighter weight in general.
  • eevictim
    eevictim Member Posts: 319
    Saubhik, thank you for your post. I set default for my timestamp column to sysdate. Additionally, since I'm using JDev, I set my entity object column that required a timestamp to a History Column with a type of Created On.

    Solved my issue and no trigger needed!
This discussion has been closed.