Microsoft SQL Server forum - intead of update/insert trigger on view question

Author

intead of update/insert trigger on view question

V T

2005-12-22, 1:23 pm

Hello all,

SQL Server 2000 documentation
http://www.microsoft.com/technet/pr...rt10/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,DataFiel
d2) Values (1,2)
INSERT INTO TestTable (DataField1,DataFiel
d2) Values (3,4)
INSERT INTO TestTable (DataField1,DataFiel
d2) 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
*/

Erland Sommarskog

2005-12-22, 8:24 pm

V T (moreaboutit@hotmail
.com) writes:
> SQL Server 2000 documentation
> http://www.microsoft.com/technet/pr...rt10/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,DataField
2) Values (0,1,2)
INSERT INTO TestView (KeyField, DataField1,DataField
2) Values (0,3,4)
INSERT INTO TestView (KeyField, DataField1,DataField
2) 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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

V T

2005-12-23, 9:23 am

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:Xns9734E7666413
9Yazorman@127.0.0.1...
> V T (moreaboutit@hotmail
.com) writes:
http://www.microsoft.com/technet/pr...rt10/c3761.mspx[color=darkred]
>
> 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,DataField
2) Values (0,1,2)
> INSERT INTO TestView (KeyField, DataField1,DataField
2) Values (0,3,4)
> INSERT INTO TestView (KeyField, DataField1,DataField
2) 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/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Erland Sommarskog

2005-12-23, 8:24 pm

V T (moreaboutit@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Sponsored Links