Post reply

Insert into view with trigger on LinkedServer not working

  • February 13, 2007 at 5:14 pm

    #175080

    forgive me for giving you the runabout, but this forum is probably where I would have started this thread in the first place if I had started it.  In the mean time, if you don't mind checking out the existing thread under Administration I'd be grateful...
     
     

  • February 13, 2007 at 7:43 pm

    #689617

    I've come across this sort of problem before, but it was a longish time ago so I can't guarantee that my account is exactly right, but from memory:

    1. SQL determines properties and constraints of view columns based on the columns of the underlying tables if these are referenced directly in the view definition. So the job_id column in the view in effect has the identity property and can't be specified under normal settings.

    2. When you attempt to write to a view, even one which has an appropriate 'instead of' trigger, SQL will use the implied properties and constraints in validating the DML statement you are issuing. So even though you have logic in your view which means the underlying table column won't in fact be touched, SQL still rejects the statement.

    You could do something kludgy like specifying job_id+0 AS job_id or isnull(job_id,0) AS job_id in the view definition: this would (I think!) 'screen off' the underlying table column so that the view column won't inherit the identity property, but this will prevent you using the index on the underlying table, and cause performance problems.

    Specifying a column list for the insert that doesn't include job_id would avoid the problem I think. This does seem to be a general issue with INSTEAD OF triggers - e.g. a similar problem arises if the base table column is defined NOT NULL. This makes them less useful than you might have wished - a bit like the totally unhelpful PIVOT functionality in SQL 9. I suspect someone at M$ had to rush out a solution that only superficially fulfils a prearranged spec.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • February 14, 2007 at 9:45 am

    #689743

    my response is posted on the original thread:
     
    in summary, that didn't work.
     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply