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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy