Posted by V T on 12/23/05 16:09

I guess you are right. Strange thing then that this discrepancy betweeen doc
and product still not fixed in "SQL2000+5years" version.

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9734E76664139Yazorman@127.0.0.1...
> V T (moreaboutit@hotmail.com) writes:
> > SQL Server 2000 documentation
> >
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3761.mspx
> > states that if view is using "NOT NULL" columns of a base table, then
> > insert/update performed on a view must provide dummy values for those
> > columns, and code of the trigger should ignore them.
> >
> > But I cannot reproduce this restriction. Code below pasted to
> > QueryAnalyser shows that I can not supply dummy values for "NOT NULL"
> > fields when I update view and still have update done. What do I miss ?
>
> It seems that text goes a bit too far, and drags UPDATE into the business
> when it shouldn't. I tried with an INSTEAD OF trigger, and found that
> I had to specify dummy values all over the place.
>
> SET NOCOUNT ON
> CREATE TABLE TestTable
> (
> KeyField INT IDENTITY(1,1),
> DataField1 INT NOT NULL,
> DataField2 INT DEFAULT 1 NOT NULL
> )
> go
> CREATE VIEW TestView AS
> SELECT * FROM TestTable
>
> go
> --setup step 3. execute only inside of this comment
> CREATE TRIGGER TestViewTrig_IU
> ON dbo.TestView
> INSTEAD OF INSERT
> AS
> BEGIN
> SET NOCOUNT ON
>
> INSERT TestTable (DataField1, DataField2)
> SELECT DataField1, DataField2
> FROM inserted
>
> END
>
> go
> --setup step 4. execute only inside of this comment
> INSERT INTO TestView (KeyField, DataField1,DataField2) Values (0,1,2)
> INSERT INTO TestView (KeyField, DataField1,DataField2) Values (0,3,4)
> INSERT INTO TestView (KeyField, DataField1,DataField2) Values (0,5,6)
> INSERT INTO TestView (KeyField, DataField1, DataField2)
> Values (0,DEFAULT, 56)
> INSERT INTO TestView (KeyField, DataField1, DataField2)
> Values (0,156, DEFAULT)
>
> go
> SELECT * FROM TestView
> -- SQL Server lets me not specify DataField2 when update DataField1
orreverse,
> -- which is opposed to what documentation says
> go
> UPDATE TestView SET DataField1 = DataField1 + 1 where KeyField = 2
> UPDATE TestView SET DataField2 = DataField2 + 1 where KeyField = 3
> SELECT * FROM TestView
>
>
> go
> -- remove test environment
> DROP VIEW TestView
> DROP TABLE TestTable
>
>
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]