getdate function issue

[niall5098]

hi There,

i have an issue with a php application that inserts rows to a sql server database. each single transaction create 2 new rows in a table however the datetime in the second row is before the datetime in the first row. the insert uses the getdate function to assign the date and time. initially i thought it was a clock syncing issues from the esx host to the virtual db server but this has been ruled out.

has anyone out there encountered this issue before? the software is mssql 2005.

thanks in advance,

Niall

 

[xlbo]

How is your insert statement constructed?


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[niall5098]

thanks for getting back. here is the process:

PHP code begins transaction
Inserts a new record into TD_ENFORCEMENT table
Inserts a new record into TD_ARCHIVALS
Inserts a new record into TD_ENFORCEMENT_ARCHIVALS (efa_efm_id and efa_arl_id only) efa_created_date default value is GETDATE()
Php code does some additional stuff
Inserts a new record into td_enforcement_history table
Commits a transaction
Inserts a new record into TD_ARCHIVALS
Inserts a new record into TD_ENFORCEMENT_ARCHIVALS (efa_efm_id and efa_arl_id only) efa_created_date default value is GETDATE()

So, timestamps in TD_ARCHIVALS and TD_ENFORCEMENT_ARCHIVALS tables for second records are older than for the first records

 

[gmmastros]

Are you certain that 2nd row is inserted before the first? Specifically, how are you determining this? I assume you are running some sort of query to determine this. Are you just looking at the data in the table (without using an order by)?

It's also possible, I suppose, that there could be insert triggers that are changing the dates on you?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[niall5098]

Hi George,

Let me know if you can view the pic in the url.

You will see the id is aut incremented so the most recent row has a slightly older time, this is the issue.

Regards,

Niall

 

[gmmastros]

I had to right click the pic, copy url, and then open a new browser window and paste the url. Then I was able to see it.

ARL_ID is an identity column?

Do you have any triggers on any of the tables? Run this and post the results.

sp_helptrigger 'TD_ENFORCEMENT'
sp_helptrigger 'TD_ARCHIVALS'
sp_helptrigger 'TD_ENFORCEMENT_ARCHIVALS'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[niall5098]

arl_id is an identity column. there are 4 triggers altogether:

[TD_ARCHIVALS]
FTER UPDATE
AS
BEGIN

UPDATE
td_archivals
SET
arl_ver = del.arl_ver + 1
, arl_updated_by = dbo.Fn_GetAuditUser()
, arl_updated_date = GETDATE()
FROM
td_archivals arl
JOIN deleted del
ON arl.arl_id = del.arl_id
JOIN inserted ins
ON arl.arl_id = del.arl_id
WHERE
arl.arl_id = ins.arl_id
AND arl.arl_ver = COALESCE (ins.arl_ver, del.arl_ver)
;

END

[TD_ENFORCEMENT_ARCHIVALS]
AFTER UPDATE
AS
BEGIN

UPDATE
td_enforcement_archivals
SET
efa_ver = del.efa_ver + 1
, efa_updated_by = dbo.Fn_GetAuditUser()
, efa_updated_date = GETDATE()
FROM
td_enforcement_archivals efa
JOIN deleted del
ON efa.efa_id = del.efa_id
JOIN inserted ins
ON efa.efa_id = del.efa_id
WHERE
efa.efa_id = ins.efa_id
AND efa.efa_ver = COALESCE (ins.efa_ver, del.efa_ver)
;

END

[TD_ENFORCEMENTS]
AFTER UPDATE
AS
BEGIN

UPDATE
td_enforcements
SET
efm_ver = del.efm_ver + 1
, efm_updated_by = dbo.Fn_GetAuditUser()
, efm_updated_date = GETDATE()
FROM
td_enforcements efm
JOIN deleted del
ON efm.efm_id = del.efm_id
JOIN inserted ins
ON efm.efm_id = del.efm_id
WHERE
efm.efm_id = ins.efm_id
AND efm.efm_ver = COALESCE (ins.efm_ver, del.efm_ver)
;
IF UPDATE (EFM_DELETED)
BEGIN
DELETE dbo.TD_INB_TODO_TOTALS
WHERE USR_ID IN
(SELECT ASM_USR_ID USR_ID
FROM dbo.TD_ASSIGNMENTS ASM
JOIN dbo.TD_INSPECTION_ASSIGNMENTS ISPA
ON ISPA_ASM_ID = ASM_ID
JOIN dbo.TD_INSPECTION_ENFORCEMENTS IPEF
ON ISPA.ISPA_ISP_ID = IPEF_ISP_ID
JOIN dbo.TD_ENFORCEMENTS EFM
ON IPEF.IPEF_EFM_ID = EFM.EFM_ID
JOIN INSERTED INS
ON EFM.EFM_ID = INS.EFM_ID
);
END

END

AFTER INSERT
AS
BEGIN
DELETE dbo.TD_INB_TODO_TOTALS
WHERE USR_ID IN
(SELECT ASM_USR_ID USR_ID
FROM dbo.TD_ASSIGNMENTS ASM
JOIN dbo.TD_INSPECTION_ASSIGNMENTS ISPA
ON ISPA_ASM_ID = ASM_ID
JOIN dbo.TD_INSPECTION_ENFORCEMENTS IPEF
ON ISPA.ISPA_ISP_ID = IPEF_ISP_ID
JOIN INSERTED INS
ON IPEF.IPEF_EFM_ID = INS.EFA_EFM_ID
);
END