By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,636 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,636 IT Pros & Developers. It's quick & easy.

intead of update/insert trigger on view question

P: n/a
V T
Hello all,

SQL Server 2000 documentation
http://www.microsoft.com/technet/pro...t10/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
*/
Dec 22 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
V T (mo*********@hotmail.com) writes:
SQL Server 2000 documentation
http://www.microsoft.com/technet/pro...t10/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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 22 '05 #2

P: n/a
V T
I guess you are right. Strange thing then that this discrepancy betweeen doc
and product still not fixed in "SQL2000+5years" version.

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
V T (mo*********@hotmail.com) writes:
SQL Server 2000 documentation
http://www.microsoft.com/technet/pro...t10/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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Dec 23 '05 #3

P: n/a
V T (mo*********@hotmail.com) writes:
I guess you are right. Strange thing then that this discrepancy betweeen
doc and product still not fixed in "SQL2000+5years" version.


Given the complexity in a product like SQL 2000, it is inevitable that
there are errors in the documentation, and many of them are not corrected,
not even after five years.

I've reported this particular error in our MVP forum, so that it at least
will not be included in the SQL 2005 Resource kti.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.