June 10, 2008 at 7:53 am
--------------------------------------------------------------------------------
Hi all,
I am trying to create a trigger for a table, when a record is inserted into the table, the currdate_time field should be inserted with the current date and time. I tried below in Oracle
create trigger test_trg
before insert on test
for each row
begin
:new.curr_datetime := sysdate;
end;
/
For Sql server, I tried the below,
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
I inserted a record into test:
insert into test (num) values (8)
select * from test
num curr_datetime
8 NULL
NULL 2008-06-10 09:49:06:317
Trigger is inserting NULL values. Please give me your suggestions.Thanks.
June 10, 2008 at 7:59 am
I tried the below 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
Inserted a record:
insert into test (num) values (7)
select * from test
num curr_datetime
7 NULL
7 2008-06-10 09:58:20:887
Please help. Thanks much.
June 10, 2008 at 8:12 am
Is there a way to insert a record without the NULL value in the curr_datetime field? THe below trigger I used is inserting a record with NULL value and inserting 2nd record with the currect date.
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
Thank you for all your help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply