Linq to SQL with INSTEAD OF Trigger and an Identity Column

Question

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.

enter image description here

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.

Randy Minder · Accepted Answer · 2012-03-29 17:55:22Z

2

Bob,

It seems you are attempting to solve two different problems here. One of which has to do with a L2S error with an Instead Of trigger and another with using the date on the SQL Server box for your column. I think you might have problems with Instead of Triggers and L2S. You might want to try an approach that uses an After trigger, like this. I think this will solve both your problems.

ALTER TRIGGER [Master].[ProcessLoggingEventTimeTrigger] 
ON [Master].[ProcessLogging]
AFTER INSERT

AS 
BEGIN
UPDATE [Master].[ProcessLogging] SET EventTime = GETDATE() WHERE ProcessLoggingId = (SELECT ProcessLoggingId FROM inserted)
END

answered Mar 29, 2012 at 17:55

Randy Minder

48.8k56 gold badges222 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JotaBe · Accepted Answer · 2012-03-29 17:30:09Z

2

Don't use a trigger, use a defualt:

create table X
(id int identity primary key,
value varchar(20),
eventdate datetime default(getdate()))

insert into x(value) values('Try')
insert into x(value) values('this')

select * from X

It's much better.

answered Mar 29, 2012 at 17:30

JotaBe

39.3k9 gold badges102 silver badges121 bronze badges

1 Comment

HLGEM Over a year ago

If you can usea default, it is almost always preferable to using a trigger.

Phil · Accepted Answer · 2012-03-29 17:33:23Z

1

Have you tried using a default value of (getdate()) for the EventTime colum? You wouldn't then need to set the value in the trigger, it would be set automatically.

A default value is used when you don't explicitly supply a value, e.g.

INSERT INTO ProcessLogging (ProcessLoggingId, ProcessCode, SubId, LastModifiedUser)
    SELECT ProcessLoggingId, ProcessCode, SubId, LastModifiedUser FROM inserted

enter image description here

edited Mar 29, 2012 at 17:33

answered Mar 29, 2012 at 17:26

Phil

43.2k9 gold badges102 silver badges102 bronze badges

4 Comments

Bob Horn Over a year ago

That's what I wanted to do, but the problem is the ID column. I get different errors. Let me try your exact scenario.

Bob Horn Over a year ago

I get this error when I try your suggestion: 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.

Phil Over a year ago

I see, your problem is because you are explicitly setting the primary key and overriding the auto-increment. Linq to SQL is getting confused. Can you remove the trigger completely?

Bob Horn Over a year ago

I could, but then the default value would only get used when the property on the class is NULL.

Mohamed Ramadan · Accepted Answer · 2012-07-20 10:08:19Z

0

Bob,

I see it is better to don't use triggers in SQL server; it have a lot of disadvantage and not recommended for database performance enhancements. Please check SQL Authority blog for more information about the Triggers problems.

You can achieve what you want without Triggers using the following steps:

Change Eventime column to allow null
Set Eventtime column Default Value to GetDate(). So it always will have a the current insertion value.
Don't set Eventtime value to DateTime.Now from your LinqToSQL code, so it will take the default value in the SQL Server.

answered Jul 20, 2012 at 10:08

Mohamed Ramadan

8036 silver badges15 bronze badges

Collectives™ on Stack Overflow

Linq to SQL with INSTEAD OF Trigger and an Identity Column

4 Answers 4

Comments

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related