create trigger test_trg
before insert on test
for each row
begin
   :new.curr_datetime := sysdate; 
end;
/

create table test (num smallint, curr_datetime datetime)
go

create trigger test_trg
on test
for insert 
as

insert into test (curr_datetime) select getdate() from dual

create trigger test_trg
on test
for insert 
as
declare @dt datetime

set @dt = getdate() 

insert into test (num,curr_datetime) select num, @dt from test

create trigger test_trg
on test
for insert 
as
declare @dt datetime

set @dt = getdate() 

insert into test (num,curr_datetime) select num, @dt from test

create trigger test_trg
on test
for insert 
as
declare @dt datetime

set @dt = getdate() 

insert into test (num,curr_datetime) select num, @dt from test

quote:

---

Originally posted by psangeetha

Hi all,

I altered the column curr_datetime to have default as 'getdate()'.
It works fine when I insert a record into the table at the database end. But when the application inserts a record it does not show the time(only enters the date).

I guess, at this poiint we have to go with an insert trigger.

create trigger test_trg
on test
for insert 
as
declare @dt datetime

set @dt = getdate() 

insert into test (num,curr_datetime) select num, @dt from test

The above trigger inserts the record with NULL value and inserting 2nd record with the correct date. So its basically inserting 2 records 2nd record with the correct date and the 1st record with NULL value.
Please give me your suggestions. Thank you

---

quote:

---

Originally posted by psangeetha

Thanks for your reply, Visakh. I am not sure how the record is been inserted from the application. But when the application people insert a record, this column doesnt show the time in the database.
But when I insert a record from the database, it does show the date and time. It is weird but the application people would like to have a insert trigger for this instead of getdate() as default.

Since I do not have exposure on the application side, I have to create an insert trigger and check if that will work. Please help.

---

create trigger test_trg
on test
for insert 
as

update t
set t.curr_datetime=getdate() 
from test t
inner join inserted i
on i.pk=t.pk

go

create trigger test_trg
on test
for insert 
as

update t
set t.curr_datetime=getdate() 
from test t
inner join inserted i
on i.num=t.num

go

quote:

---

Originally posted by psangeetha

Thanks, Visakh. I tried the trigger you mentioned it works fine.
But when I insert a record it says 2 row(2) affected.
But when I select the table, it only inserted 1 record with the correct date and time. Is it something I should be concerned of?

create table test (num smallint not null, curr_datetime datetime)

insert into test (num) values (2)

select * from test

num curr_datetime
2 2008-06-16 12:14:46.243

create trigger test_trg
on test
for insert 
as

update t
set t.curr_datetime=getdate() 
from test t
inner join inserted i
on i.num=t.num

go

insert into test (num) values (90)

1 row(s) affected

1 row(s) affected

select * from test

num curr_datetime
2 2008-06-16 12:14:46.243
9 2008-06-16 12:15:23.340

Any suggestions? Thanks a lot

---

quote:

---

Originally posted by psangeetha
I altered the column curr_datetime to have default as 'getdate()'.
It works fine when I insert a record into the table at the database end. But when the application inserts a record it does not show the time(only enters the date).

---