SQL Server/T-SQL Tutorial/View/Updatable View
Содержание
DELETE and insert from a view
<source lang="sql">
7> create table Billings ( 8> BankerID INTEGER, 9> BillingNumber INTEGER, 10> BillingDate datetime, 11> BillingTotal INTEGER, 12> TermsID INTEGER, 13> BillingDueDate datetime , 14> PaymentTotal INTEGER, 15> CreditTotal INTEGER 16> 17> ); 18> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> create table Bankers( 4> BankerID Integer, 5> BankerName VARCHAR(20), 6> BankerContactLName VARCHAR(20), 7> BankerContactFName VARCHAR(20), 8> BankerCity VARCHAR(20), 9> BankerState VARCHAR(20), 10> BankerZipCode VARCHAR(20), 11> BankerPhone VARCHAR(20) 12> ) 13> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> 4> CREATE VIEW IBM_Billings 5> AS 6> SELECT BillingNumber, BillingDate, BillingTotal 7> FROM Billings 8> WHERE BankerID = (SELECT BankerID FROM Bankers WHERE BankerName = "IBM") 9> GO 4> INSERT INTO IBM_Billings (BillingNumber, BillingDate, BillingTotal) 5> VALUES ("8", "2002-07-31", 417) 6> GO (1 rows affected) 1> 2> 3> DELETE FROM IBM_Billings 4> WHERE BillingNumber = "3" 5> GO (0 rows affected) 1> 2> drop view IBM_Billings; 3> GO 1> 2> drop table Bankers; 3> GO 1> 2> 3> drop table Billings; 4> GO 1></source>
Insert into a not-updatable view
<source lang="sql">
3> CREATE TABLE one 4> ( 5> col11 int NOT NULL, 6> col12 int NOT NULL 7> ) 8> 9> CREATE TABLE two 10> ( 11> col21 int NOT NULL, 12> col22 int NOT NULL 13> ) 14> GO 1> 2> CREATE VIEW one_two 3> AS 4> (SELECT col11, col12, col21, col22 5> FROM one LEFT JOIN two ON (col11=col21)) 6> GO 1> 2> INSERT one_two (col11, col12) 3> VALUES (1, 2) 4> GO (1 rows affected) 1> SELECT * FROM one_two 2> GO col11 col12 col21 col22
----------- ----------- -----------
1 2 NULL NULL
(1 rows affected) 1> 2> INSERT one_two (col11, col12, col21, col22) 3> VALUES (1, 2, NULL, NULL) 4> GO Msg 4405, Level 16, State 1, Server J\SQLEXPRESS, Line 2 View or function "one_two" is not updatable because the modification affects multiple base tables. 1> drop view one_two; 2> drop table one; 3> drop table two; 4> GO 1></source>
Insert into a view
<source lang="sql">
8> create table Billings ( 9> BankerID INTEGER, 10> BillingNumber INTEGER, 11> BillingDate datetime, 12> BillingTotal INTEGER, 13> TermsID INTEGER, 14> BillingDueDate datetime , 15> PaymentTotal INTEGER, 16> CreditTotal INTEGER 17> 18> ); 19> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> create table Bankers( 4> BankerID Integer, 5> BankerName VARCHAR(20), 6> BankerContactLName VARCHAR(20), 7> BankerContactFName VARCHAR(20), 8> BankerCity VARCHAR(20), 9> BankerState VARCHAR(20), 10> BankerZipCode VARCHAR(20), 11> BankerPhone VARCHAR(20) 12> ) 13> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> 4> CREATE VIEW IBM_Billings 5> AS 6> SELECT BillingNumber, BillingDate, BillingTotal 7> FROM Billings 8> WHERE BankerID = (SELECT BankerID FROM Bankers WHERE BankerName = "IBM") 9> GO 1> 4> INSERT INTO IBM_Billings (BillingNumber, BillingDate, BillingTotal) 5> VALUES ("8", "2002-07-31", 417) 6> GO (1 rows affected) 1> 2> 3> drop view IBM_Billings; 4> GO 1> 2> drop table Bankers; 3> GO 1> 2> 3> drop table Billings; 4> GO</source>
Modifying One Column at a Time through a View
<source lang="sql">
4> CREATE TABLE employee( 5> id INTEGER NOT NULL PRIMARY KEY, 6> first_name VARCHAR(10), 7> last_name VARCHAR(10), 8> salary DECIMAL(10,2), 9> start_Date DATETIME, 10> region VARCHAR(10), 11> city VARCHAR(20), 12> managerid INTEGER 13> ); 14> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> 3> 4> CREATE VIEW MyView 5> AS 6> SELECT 7> id 8> FROM 9> Employee 10> GO 1> 2> 3> UPDATE MyView 4> SET id = 19 5> WHERE 6> id = 1 7> SELECT 8> * 9> FROM 10> MyView 11> 12> drop view MyView 13> GO (1 rows affected) id
2
3
4
5
6
7
8
9
19
(9 rows affected) 1> 2> 3> drop table employee; 4> GO</source>
The attempted insert or update failed
<source lang="sql">
because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. 4> CREATE TABLE authors( 5> au_id varchar(11), 6> au_lname varchar(40) NOT NULL, 7> au_fname varchar(20) NOT NULL, 8> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 9> address varchar(40) NULL, 10> city varchar(20) NULL, 11> state char(2) NULL, 12> zip char(5) NULL, 13> contract bit NOT NULL 14> ) 15> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 11> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE VIEW CA_authors AS 4> ( 5> SELECT * FROM authors 6> WHERE state="CA" 7> ) 8> WITH CHECK OPTION 9> GO 1> 2> SELECT * FROM CA_authors 3> GO au_id au_lname au_fname phone address city state zip contract
---------------------------------------- -------------------- ------------ ---------------------------------------- -------------------- ----- ----- --------
1 Joe Abra 111 111-1111 6 St. Berkeley CA 11111 1 2 Jack Majo 222 222-2222 3 St. Oakland CA 22222 1 8 Yellow Heather 888 888-8888 3 Pu Vacaville CA 88888 0 9 Gold Dep 999 999-9999 5 Av. Oakland CA 99999 0 10 Siler Dean 000 000-0000 4 Av. Oakland CA 00000 1 (5 rows affected) 1> UPDATE CA_authors SET state="IL" 2> GO Msg 550, Level 16, State 1, Server J\SQLEXPRESS, Line 1 The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. 1> SELECT * FROM CA_authors 2> GO au_id au_lname au_fname phone address city state zip contract
---------------------------------------- -------------------- ------------ ---------------------------------------- -------------------- ----- ----- --------
1 Joe Abra 111 111-1111 6 St. Berkeley CA 11111 1 2 Jack Majo 222 222-2222 3 St. Oakland CA 22222 1 8 Yellow Heather 888 888-8888 3 Pu Vacaville CA 88888 0 9 Gold Dep 999 999-9999 5 Av. Oakland CA 99999 0 10 Siler Dean 000 000-0000 4 Av. Oakland CA 00000 1 (5 rows affected) 1> drop VIEW CA_authors; 2> GO 1> 2> drop table authors; 3> GO</source>
WITH CHECK OPTION
<source lang="sql">
For views that filter results, the WITH CHECK OPTION keeps the results synchronized with the table(s). 6> 7> CREATE TABLE employee( 8> id INTEGER NOT NULL PRIMARY KEY, 9> first_name VARCHAR(10), 10> last_name VARCHAR(10), 11> salary DECIMAL(10,2), 12> start_Date DATETIME, 13> region VARCHAR(10), 14> city VARCHAR(20), 15> managerid INTEGER 16> ); 17> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> 3> 4> Create VIEW vwEmployee 5> As 6> SELECT ID, First_Name 7> From Employee 8> WITH CHECK OPTION 9> GO 1> 2> 3> 4> drop view vwEmployee 5> GO 1> 2> drop table employee; 3> GO</source>
