create before insert trigger to add sysdate + 30 minutes in sql

Question

i have table with timestamp column i want to add on each insert to add the current timestamp + 30 minutes

how to achieve that im using oracle 11g;

Answer 1

As you have a timestamp column it's probably better to use systimestamp instead of sysdate, and add an interval rather than a fraction of a day - which would lose the fractional second precision (and time zone, if your column actually stores that too).

You would either just have a date (if you use sysdate + 30/1440) or implicitly convert to a date (if you use systimestamp + 30/1440); and either way will you'll end up with a date that is then implicitly or explicitly converted to a timestamp as it's stored in your column.

As a simple example of using an interval:

create table t42 (col1 number, col2 timestamp);

create trigger tr42
before insert on t42
for each row
begin
  :new.col2 := systimestamp + interval '30' minute;
end;
/

select systimestamp from dual;

SYSTIMESTAMP                     
---------------------------------
2019-02-13 07:17:11.971542000 GMT

insert into t42 (col1) values (42);

select col1, col2 from t42;

      COL1 COL2                         
---------- -----------------------------
        42 2019-02-13 07:47:12.253603000

You could also use a default value for the column instead of a trigger:

create table t42 (
  col1 number,
  col2 timestamp default systimestamp + interval '30' minute
);

select systimestamp from dual;

SYSTIMESTAMP                     
---------------------------------
2019-02-13 07:17:12.962268000 GMT

insert into t42 (col1) values (42);

select col1, col2 from t42;

      COL1 COL2                         
---------- -----------------------------
        42 2019-02-13 07:47:13.028670000

although that does allow the person doing the insert to provide their own value:

insert into t42 (col1, col2) values (43, timestamp '2000-01-01 00:00:00.0');

select col1, col2 from t42;

      COL1 COL2                         
---------- -----------------------------
        42 2019-02-13 07:47:13.028670000
        43 2000-01-01 00:00:00.000000000

The trigger would override any user-supplied value (though it could also be modified not to.)

You could also use current_timestamp instead of systimestamp - they do slightly different things.

Answer 2

you can create a trigger as shown below. this will insert current timestamp+30 minutes each time you insert a row to the table.

create or replace trigger before_insert
before insert 
  on table_name for each row
declare 
v_time date;
begin
select sysdate+30/1440 into v_time from dual;
:new.cur_time :=v_time;
end;
/