problems with GETDATE()

Hello

I have an application with visualBasic that conects to SQL server 2000 through ODBC.

In sql server 2000 I have a trigger that inserts into an historic database de transaction made in the active database, like this:

CREATE TRIGGER [TRG_UPD_GTECON] ON [dbo].[GTECON]
FOR UPDATE
AS

INSERT INTO HISTORICO.dbo.HISTO_GTECON (GTECONCOD,GTECONNIV,GTECONORD,GTECONPAD,GTECONDES ,GTECONTIP,GTETIPNOD,CODUSUA,FECMODIF,ACCION, FECHAHIST)
SELECT GTECONCOD,GTECONNIV,GTECONORD,GTECONPAD,GTECONDES, GTECONTIP,GTETIPNOD,CODUSUA,FECMODIF , 'M',GETDATE() FROM INSERTED

FECMODIF field is a datetime field and it gives the problems.

I make two updates from my application: one to change one field and another one to change another one. It is made "one after the other", I mean: there is no user time between both but there are two different updates that should have different datetime at FECMODIF field as I use GETDATE() in both UPDATEs. the update id like this:

UPDATE GTECON
SET GTETIPNOD = 'H',
CODUSUA = 'coco',
FECMODIF = GETDATE()
WHERE GTECONCOD = 'A01'
AND GTECONTIP= 'H'

My problem is that when I see the historic database there are two registers of modification ('M') but BOTH HAVE THE SAME FECMODIF DATE!!.

It looks that GETDATE() is not indeterminist. If I debug the program, as there are user time between both updates, there is a difference between dates but when it works quickly It looks that theres no difference for getdate(). My historic is like this:

A01 ESTOMATOLOGICOS 2002-12-05 10:46:58.843 M
A01 ESTOMATOLOGICOS 2002-12-05 10:46:58.843 M

Please, some help or Ideas. It looks that nobody have this problem all over Internet.

Note1: I tried to put at the second update something like this FECMODIF = dateadd(ss,3,getdate()) in order to force the date to be different, but It doesn't work. It gives me the SAME DATETIME.

Note2: Everything is under the same transaction (maybe it helps)

RaulI don't know if you have solved your problem, however I ran the following test on SQL 2000 and 7.0
--create table abc (id int identity(1,1) not null, msg varchar(20), txnTime datetime)
--create table trigabc (id int not null, txnTime datetime)
/*
CREATE TRIGGER trig_test
ON abc
FOR UPDATE
AS
BEGIN
insert trigabc (id,txnTime) select id, getdate() from inserted
END
*/
truncate table abc
truncate table trigabc
go
insert abc (msg,txnTime) select 'First', getdate()
insert abc (msg,txnTime) select 'Second', getdate()
go
begin tran
update abc set msg='First Update', txnTime=getdate() where id = 1
waitfor delay '000:00:03'
update abc set msg='Second Update', txnTime=getdate() where id = 2
commit tran

select * from abc
select * from trigabc


My output on both systems showed a 3 second delay:
id msg txnTime
---- ------- ----------------
1 First Update 2002-12-06 09:55:15.720
2 Second Update 2002-12-06 09:55:18.773

(2 row(s) affected)

id txnTime
---- ----------------
1 2002-12-06 09:55:15.770
2 2002-12-06 09:55:18.773

Does this simulate your transaction process?|||Thank you very much

Your idea is good but I couldn't place it into the trigguers. Instead of this I have place it in program code and it looks to work.

Thank you very much

Raul

Originally posted by achorozy
I don't know if you have solved your problem, however I ran the following test on SQL 2000 and 7.0
--create table abc (id int identity(1,1) not null, msg varchar(20), txnTime datetime)
--create table trigabc (id int not null, txnTime datetime)
/*
CREATE TRIGGER trig_test
ON abc
FOR UPDATE
AS
BEGIN
insert trigabc (id,txnTime) select id, getdate() from inserted
END
*/
truncate table abc
truncate table trigabc
go
insert abc (msg,txnTime) select 'First', getdate()
insert abc (msg,txnTime) select 'Second', getdate()
go
begin tran
update abc set msg='First Update', txnTime=getdate() where id = 1
waitfor delay '000:00:03'
update abc set msg='Second Update', txnTime=getdate() where id = 2
commit tran

select * from abc
select * from trigabc


My output on both systems showed a 3 second delay:
id msg txnTime
---- ------- ----------------
1 First Update 2002-12-06 09:55:15.720
2 Second Update 2002-12-06 09:55:18.773

(2 row(s) affected)

id txnTime
---- ----------------
1 2002-12-06 09:55:15.770
2 2002-12-06 09:55:18.773

Does this simulate your transaction process?