SQL Create view one two

use pubs
go

if exists (SELECT * FROM sysobjects WHERE name='one' and type='U')
        DROP TABLE one
go

if exists (SELECT * FROM sysobjects WHERE name='two' and type='U')
        DROP TABLE two
go

if exists (SELECT * FROM sysobjects WHERE name='one_two' and type='V')
        DROP VIEW one_two
go

if exists (SELECT * FROM sysobjects WHERE name='one_two_equijoin' and type='V')
        DROP VIEW one_two_equijoin
go

CREATE TABLE one
(
col11    int    NOT NULL,
col12    int    NOT NULL
)

CREATE TABLE two 
(
col21    int    NOT NULL,
col22    int    NOT NULL,
)
GO

CREATE VIEW one_two
AS
(SELECT col11, col12, col21, col22
FROM one LEFT JOIN two ON (col11=col21))
GO

-- This insert succeeds.
INSERT one_two (col11, col12)
VALUES (1, 2)
GO



CREATE VIEW one_two_equijoin
AS
(SELECT col11, col12, col21, col22
FROM one JOIN two ON (col11=col21))
WITH CHECK OPTION
GO

-- This insert fails because the CHECK OPTION is not satisfied.
INSERT one_two_equijoin (col11, col12)
VALUES (1, 2)
GO

INSERT two VALUES (1, 2)
GO

-- Now that matching rows are in TWO, the same insert now succeeds.
INSERT one_two_equijoin (col11, col12)
VALUES (1, 2)
GO

SELECT * FROM one
SELECT * FROM two
SELECT * FROM one_two
SELECT * FROM one_two_equijoin
GO

-- But trying to insert all columns always fails with error 4405.
INSERT one_two (col11,col12,col21,col22)
VALUES (1,2,NULL,NULL)
go

-- And trying to delete from a view that is a join always fails.
DELETE one_two
go


if exists (SELECT * FROM sysobjects WHERE name='AU_VIEW' and type='V')
        DROP VIEW AU_VIEW
go


CREATE VIEW AU_VIEW
as
SELECT * FROM authors
go

-- Temporarily suspend constraints
ALTER TABLE titles
        NOCHECK CONSTRAINT ALL
GO

ALTER TABLE titleauthor
        NOCHECK CONSTRAINT ALL
GO

ALTER TABLE authors
        NOCHECK CONSTRAINT ALL
GO


BEGIN TRAN
DELETE AU_VIEW
-- Deleting through view deletes rows in underlying table.
SELECT * FROM authors
ROLLBACK TRAN
go

CREATE VIEW AU_VIEW2
(au_lname,au_fname,full_name)
as
SELECT au_lname, au_fname,au_lname+au_fname from authors
go

-- This view is not updatable because the full_name column is derived.
UPDATE AU_VIEW2
SET au_fname='John',au_lname='Smith',full_name='j'
SELECT * FROM AU_VIEW2
go




-- Re-enable suspended constraints
ALTER TABLE titles
        CHECK CONSTRAINT ALL
GO

ALTER TABLE titleauthor
        CHECK CONSTRAINT ALL
GO

ALTER TABLE authors
        CHECK CONSTRAINT ALL
GO