1

I have a table to which I would like to add computed column (with values different for each user - needed for permissions).
Problem is that this table is part of Microsoft Dynamics NAV which don't know anything about computed columns.
I've managed how to cheat NAV so that I change the column type after NAV creates it and I can read the data.

Now I'm stuck with inserts.
NAV don't use nullable columns so it always tries to insert default value and SQL Server fails with error on computed column.
I've tried to write INSTEAD OF INSERT trigger but seems that SQL Server is doing the check before it runs the trigger and still fails with error.

Is there any way to force SQL Server to ignore inserted value on computed column?

Alex Peck
  • 4,603
  • 1
  • 33
  • 37
SeeR
  • 2,158
  • 1
  • 20
  • 35

1 Answers1

2

Personally I wouldn't change the schema of a third-party application, especially a financial system. Instead of changing the tables you could create views - you can even create them in another database if you want - that include your computed column definition, then put your INSTEAD OF triggers on the views and do INSERTs through the views.

Pondlife
  • 15,992
  • 6
  • 37
  • 51
  • First you say don't change, then you say create views - but you may not know that rebinding some form to view means much bigger change and confusion for other developers/consultants. Also I've not managed how to achieve good performance with views - somehow they are much slower in NAV when connected to form. So views are not my option right now :-( – SeeR Nov 30 '12 at 08:37
  • By "change" I meant adding a column to a table, which was your original idea. Modifying the structure of tables managed by another application is risky, for obvious reasons. That's why I suggested adding views, which would be totally new objects and therefore safer; putting them into a second database and using 3-part naming (`SELECT foo from NAV.dbo.SomeTable`) would mean you don't have to touch the NAV database at all. – Pondlife Nov 30 '12 at 16:27