Forum Stats

  • 3,852,382 Users
  • 2,264,100 Discussions
  • 7,905,056 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,844 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,844 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.