nulls in pivoted view: error in updating base table that does not allow nulls

Question

I have a question on dealing with nulls in a pivoted view (that has an update trigger to put the values into the base normalized table correctly).  I get this error when editing the view when the pivot creates nulls for some non-edited blank columns and I edit a different column's non-null value:
Cannot insert the value NULL into column 'Price', table 'dbo.BaseTable'; column does not allow nulls. UPDATE fails (#515).   Is there a way of getting the update trigger to pass over the null values in the pivot view, and only update columns that have values?

The view is a pivoted (crosstab) transform of a normalized base table. The columns in the view are: dtm, fldOne, fldTwo, fldThree etc.  In the source table (BaseTable) that gets updated, the fields are: dtm, xId, Price.

The instead of update trigger on the view is this:

INSTEAD OF UPDATE
AS
BEGIN
UPDATE dbo.BaseTable
SET Price=
CASE bse.xId
WHEN 1 THEN i.fldA
WHEN 2 THEN i.fldB
WHEN 3 THEN i.fldC
[and so on for fldD etc. and all the columns in the view]
END
FROM dbo.BaseTable AS bse
JOIN INSERTED AS i ON b.Dtm = i.Dtm;

 

Answer 1

You can use COALESCE(i.FldA, Price) etc., so you will only update Price when the field is NOT NULL.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

If Naomi's suggestion does not work, please post the CREATE VIEW statement, the CREATE TABLE statement for the underlying table(s), INSERT statements with sample data and the "edit" (do you mean UPDATE?) command that fails.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

btw, a NULL column in a pivot can only exist when there is no corresponding row in the base table(s). So I would also take a look at the pivot query. In this you should use the MERGE statement.

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 4

Thanks Naomi, i just discovered it was a typo in the long list of columns in the trigger that was producing spurious nulls--one argument for using dynamic sql.   it turns out there would have been no inserted rec created for that row to join had there really been a null from the pivot and I hadn't had a typo in the case statement.

EDIT: It looks like Stefan Hoffmann suspected the issue before my post, thanks.

@Erland, I meant "edit" the opened view (via ssms table or via a client table), not edit the view design nor by an update statement directly (though ssms or the client is generating the update statements behind the scene).  

btw, one headache is there is no first-rate graphical client for sql server--Access comes closest I assume but it was initially designed for jet not sql server.  So I have to copy manually the graphical browsing/paging/search experience on a big table that one gets for "free" with a local jet database of the same size using Access.  It wouldn't have been so hard to include that in Access . . .  

Answer 5

@Erland, I meant "edit" the opened view (via ssms table or via a client table), not edit the view design nor by an update statement directly (though ssms or the client is generating the update statements behind the scene).  

Yeah, but this is the T-SQL forum, so we want the SQL statements you ran. I never use these Edit tools. SQL script you can save and reuse.

btw, one headache is there is no first-rate graphical client for sql server--Access comes closest I assume but it was initially designed for jet

SQL Server is a server application, and does not pretend to be an all-in-one solution like Access. SQL Server provides the database engine. You write the application. (Or buy one.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

we want the SQL statements you ran. I never use these Edit tools. SQL script you can save and reuse.
I should have pasted the sql ssms was generating, but the problem is solved now anyway.  (It's to create an updatable pivoted view table for the end user, who will not be writing any scripts to edit.)

You write the application.
well I'm a laconic guy when it comes to coding.

(Or buy one.)
any recommendations?  (not faulting sql server on this, though there may possibly be some pretensions along those lines with the sql express+lightswitch combo.)  really access+sql server would be perfect for the purpose if only the glue were somewhat better between them. 

 

Answer 7

(Or buy one.)
any recommendations? 

How could I give you recommendations when I have no ideas what your business? If you are into CRM, there are this and that application. If you are into medecine there that and this application. If you are into securities trading, I even have a bridge to sell to you.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

some days I have no idea what my business is either.  But on the tool, it would be a general purpose gui to sql server, like access, helpful to developers and end users alike.  there are lots of them out there, including toad and ssms of course.  but ideally it would be nice to have an ssms that was as flexible or rich as access, especially at gui table search and browsing and quick and dirty forms creation, but as tightly wound with sql server as access is with jet.  (I think sql server developers are a hardy lot and are used to not having the frills, and may be all the better for it.  I however could stand some more of the frills. on the bridge, well apparently these days that would be a safer investment than say, Italy or Greece.  but they could still sell off or lease their nations to the Germans.)