473,231 Members | 1,897 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

intead of update/insert trigger on view question

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
3 7350
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: imani_technology | last post by:
How would I write a trigger that updates the values of a Description column to upper case for even IDs and to lower case for odd IDs? I need this trigger to fire for INSERT and UPDATE events.
8
by: Jason | last post by:
I have a table that matches up Securities and Exchanges. Individual securities can belong on multiple exchanges. One of the columns, named PrimaryExchangeFlag, indicates if a particular exchange is...
1
by: Gent | last post by:
am using FOR UPDATE triggers to audit a table that has 67 fields. My problem is that this slows down the system significantly. I have narrowed down the problem to the size (Lines of code) that need...
1
by: shottarum | last post by:
I currently have 2 tables as follows: CREATE TABLE . ( mhan8 int, mhac02 varchar(5), mhmot varchar(5), mhupmj int )
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
1
by: Alessandro GARDICH | last post by:
Hi to all ... I'm looking to a way to auto update some fields of a row when other fileds are updated. the table structure are simple, CREATE TABLE param ( id int4 PRIMARY KEY, val int4,
1
by: JeremyGrand | last post by:
I've read what's available here, but can't seem to make this work right. I'm experimenting with components on a form, although I'd rather create the pieces & assemble them in code, but that's...
5
by: wpellett | last post by:
I can not get the SQL compiler to rewrite my SQL UPDATE statement to include columns being SET in a Stored Procedure being called from a BEFORE UPDATE trigger. Example: create table...
1
by: abhi81 | last post by:
Hello All, I have a table on which I have created a insert,Update and a Delete trigger. All these triggers write a entry to another audit table with the unique key for each table and the timestamp....
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.