Posted by Erland Sommarskog on 12/22/05 23:44

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

• Next in forum: Re: TransferText dropping fractions
• Prev in forum: Re: Making a view that shows the results of several different queries.
• Thread view: Re: intead of update/insert trigger on view question

[Reply to this message]