Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    2

    Exclamation Unanswered: 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)

    Raul

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I don't know if you have solved your problem, however I ran the following test on SQL 2000 and 7.0
    Code:
    --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:
    Code:
    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?
    MCDBA

  3. #3
    Join Date
    Dec 2002
    Posts
    2
    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
    Code:
    --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:
    Code:
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •