create a timestamp trigger.

748152

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

Saubhik

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

Saubhik

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

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

748152

i like your choice.

default is lighter weight in general.

eevictim

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!