May 22, 2008 at 5:36 am
HI Guys,
Quick question about views.
If I have a table:
MyTable
Col1 VARCHAR(10) Null
If I then create a view:
MyView
as
SELECT Col1(not null)
FROM MyTable
Now why is it that when I update my view with col as null that an error doesn't fire?
I find that I need use an instead of trigger on the view to raise my own error!
Is this a bug in 2005?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 22, 2008 at 6:18 am
I'm not entirely sure what your problem is. The example code won't compile. I ran this:
CREATE VIEW MyView
AS SELECT Col1
FROM MyTable
WHERE Col1 IS NOT NULL
And it's returning the values as appropriate.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2008 at 6:24 am
Sorry that wasn't real code
CREATE VIEW MyView
as
SELECT
ISNULL(Col1,'') as Col1
FROM MyTable
GO
Now if you run sp_help MyView
you will see the column is not nullable.
How come then it doesn't raise an error when I write nulls to it.
Hope that makes it clear.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 22, 2008 at 6:43 am
When you "write" to an updatable view, the query optimizer figures out the underlying table and writes to it. You obviously cannot write to a view because it does not store data, it is just a query. So trying to create data constraints on a view will not do anything.
You could do what you are saying with an INSTEAD OF trigger on your view, but I would suggest that if you want the column to not allow NULL, you do so in the table.
May 22, 2008 at 6:48 am
What he said. Sorry I didn't fully understand the problem. The key point is a view is just a mask on top of a table (or tables). It doesn't actually redefine that table (or tables).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2008 at 7:26 am
hi ,
Thanks guys,
I was under the impression that in 2000 the views schema would over ride the table for not null in the view and null in the table...
thanks again
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply