SQL Server trigger for 'Default (newsequentialid())' not working with '00000000-0000-0000-0000-000000000000'?

Question

I have an Entity with a Guid column as the primary key.

Its default is (newsequentialid()). So if I pass NULL it will generate the Guid.

Now I use this database with BDC Model Stuff. The problem is that this auto generated code doesn't pass NULL for the ID with new objects on creation, instead it uses probably just new Guid() which is 00000000-0000-0000-0000-000000000000. So my default constraint is not working and I get a primary key constraint violation error...

So is it possible to catch this problem with a trigger? Maybe check first if the Id is 0000...0 and then generate a valid one?

Answer 1

Pass Guid.NewGuid() which generates a valid Guid.

Answer 2

You can use triggers, although as others have pointed out, this would be an unwise solution, as introduces an unseen hack between code and data. Your real solution is to change your ORM such that it doesn't attempt to insert the GUID directly, as this is database generated.

So ignore the remainder of my answer.

SqlServer doesn't support BEFORE triggers, but you could use an INSTEAD OF TRIGGER
CREATE TRIGGER tSomeTable ON SomeTable INSTEAD OF INSERT
AS
BEGIN
INSERT INTO SomeTable(SomeKey, Name)
SELECT
CASE WHEN i.SomeKey = '00000000-0000-0000-0000-000000000000'
THEN newid() ELSE i.SomeKey END,
i.Name
FROM INSERTED i;
END

SqlFiddle here