Posted by V T on 12/22/05 20:47

Hello all,

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 ?

VT


/*
--setup step 1. execute only inside of this comment
SET NOCOUNT ON
CREATE TABLE TestTable
(
keyField INT IDENTITY(1,1),
dataField1 INT NOT NULL,
dataField2 INT DEFAULT 1 NOT NULL
)
*/

/* --setup step 2. execute only inside of this comment
CREATE VIEW TestView AS
SELECT * FROM TestTable
*/
/*
--setup step 3. execute only inside of this comment
CREATE TRIGGER TestViewTrig_IU
ON dbo.TestView
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON

UPDATE TestTable
SET
DataField1 = inserted.DataField1,
DataField2 = inserted.DataField2

FROM
TestTable tt
INNER JOIN
inserted
ON inserted.KeyField = tt.KeyField

END
*/
/*
--setup step 4. execute only inside of this comment
INSERT INTO TestTable (DataField1,DataField2) Values (1,2)
INSERT INTO TestTable (DataField1,DataField2) Values (3,4)
INSERT INTO TestTable (DataField1,DataField2) Values (5,6)
*/



SELECT * FROM TestView
-- SQL Server lets me not specify DataField2 when update DataField1 or
reverse,
-- which is opposed to what documentation says
UPDATE TestView SET DataField1 = DataField1 + 1 where KeyField = 2
UPDATE TestView SET DataField2 = DataField2 + 1 where KeyField = 3
SELECT * FROM TestView


/*
-- remove test environment
DROP VIEW TestView
DROP TABLE TestTable
*/

[Back to original message]