I need
to use the clock on my SQL Server
to write a time
to one of my tables, so I thought I'd just use GETDATE(). The problem is that I'm getting an error because of my INSTEAD OF trigger. Is there a way
to set one column
to GETDATE() when another column is an identity column?
This is the Linq-to-SQL:
internal void LogProcessPoint(WorkflowCreated workflowCreated, int processCode)
{
ProcessLoggingRecord processLoggingRecord = new ProcessLoggingRecord()
{
ProcessCode = processCode,
SubId = workflowCreated.SubId,
EventTime = DateTime.Now // I don't care what this is. SQL Server will use GETDATE() instead.
};
this.Database.Add<ProcessLoggingRecord>(processLoggingRecord);
}
This is the table. EventTime is what I want
to have as GETDATE(). I don't want the column
to be null.
And here is the trigger:
ALTER TRIGGER [Master].[ProcessLoggingEventTimeTrigger]
ON [Master].[ProcessLogging]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
SET IDENTITY_INSERT [Master].[ProcessLogging] ON;
INSERT INTO ProcessLogging (ProcessLoggingId, ProcessCode, SubId, EventTime, LastModifiedUser)
SELECT ProcessLoggingId, ProcessCode, SubId, GETDATE(), LastModifiedUser FROM inserted
SET IDENTITY_INSERT [Master].[ProcessLogging] OFF;
END
Without getting into all of the variations I've tried, this last attempt produces this error:
InvalidOperationException
Member AutoSync failure. For members
to be AutoSynced after insert, the type must either have an auto-generated identity, or a key that is not modified by the database after insert.
I could remove EventTime from my entity, but I don't want
to do that. If it was gone though, then it would be NULL during the INSERT and GETDATE() would be used.
Is there a way that I can simply use GETDATE() on the EventTime column for INSERTs?
Note: I do not want
to use C#'s DateTime.Now for two reasons:
1. One of these inserts is generated by SQL Server itself (from another stored procedure)
2. Times can be different on different machines, and I'd like
to know exactly how fast my processes are happening.
