0

I have a trigger on a table for insert, delete, update that on the first line gets the current date with GetDate() method.

The trigger will compare the deleted and inserted table to determine what field has been changed and stores in another table the id, datetime and the field changed. This combination must be unique

A stored procedure does an insert and an update sequentially on the table. Sometimes I get a violation of primary key and I suspect that the GetDate() returns the same value.

How can I make the GetDate() return different values in the trigger.

EDIT Here is the code of the trigger

CREATE TRIGGER dbo.TR
ON table
FOR DELETE, INSERT, UPDATE
AS
BEGIN

SET NoCount ON
DECLARE @dt Datetime
SELECT @dt = GetDate()

insert tableLog (id, date, field, old, new) 
select I.id, @dt, 'field', D.field, I.field
from INSERTED I LEFT JOIN DELETED D ON I.id=D.id
where IsNull(I.field, -1) <> IsNull(D.field, -1)

END

and the code of the calls

...
insert into table ( anotherfield)
            values (@anotherfield)
if @@rowcount=1 SET  @ID=@@Identity

...

update table
   set field    = @field
where Id = @ID

...

Sometimes the GetDate() between the 2 calls (insert and update) takes 7 milliseconds and sometimes it has the same value.

Dan
  • 683
  • 2
  • 8
  • 24
  • Post the code for your trigger... It must be getting called multiple times in the same instant? Do you call a function from the trigger? – Milney Apr 21 '17 at 07:55

3 Answers3

1

That's not exactly full solution but try using SYSDATETIME instead and of course make sure that target table can store up datetime2 up to microseconds.

Note that you can't force different datetime regardless of precision (unless you will start counting up to ticks) as stuff can just happen at the same time wihthin given precision.

If stretching up to microseconds won't solve the issue on practical level, I think you will have to either redesign this logging schema (perhaps add identity column on top of what you have) or add some dirty trick - like make this insert in try catch block and add like microsecond (nanosecond?) in a loop until you insert successfully. Definitely not s.t. I would recommend.

nimdil
  • 1,361
  • 10
  • 20
0

Look at this answer: SQL Server: intrigued by GETDATE()

If you are inserting multiple ROWS, they will all use the same value of GetDate(), so you can try wrapping it in a UDF to get unique values. But as I said, this is just a guess unless you post the code of your trigger so we can see what you are actually doing?

Community
  • 1
  • 1
Milney
  • 6,253
  • 2
  • 19
  • 33
  • @Dan - Okay you are storing a DateTime once at the start of the trigger, then inserting that SAME DATETIME for each row... So if you have more than one row it will indeed be the same datetime... use a UDF to fix this – Milney Apr 21 '17 at 09:14
  • It is always a single row. Also I do not mind that the same date is stored for multiple rows. My problem is that it is the same date for insert "transaction" and then update "transaction" – Dan Apr 21 '17 at 10:59
-1

It sounds like you're trying to create an audit trail - but now you want to forge some of the entries?

I'd suggest instead adding a rowversion column to the table and including that in your uniqueness criteria - either instead of or as well as the datetime value that is being recorded.

In this way, even if two rows are inserted with identical date/time data, you can still tell the actual insertion order.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448