Insert into view regrouping multiple tables?

Question

For exercise I have created the following database, I have created a view that shows information from the [user] table and the [address] table

Go
DROP VIEW IF EXISTS user_all
GO
CREATE VIEW user_all([name], [sex], [date_of_birth], [account_type], [street], [number], [locality], [city], [country_code], [style_ref]) AS
SELECT [user].[user_name], [user].[user_sex], [user].[date_of_birth],[user].[account_type],
    [address].[street], [address].[number], [address].[locality], [address].[city], [address].[country_code],
    [user_dance_style].[style_ref]   
FROM [user]
JOIN [address]
ON [user].[id_address] = [address].[id_address]
JOIN [user_dance_style]
ON [user].[id_user] = [user_dance_style].[id_user]
JOIN [style]
ON [user_dance_style].[style_ref] = [style].[style_ref]

I would like to do an insert into this view:

INSERT INTO user_all
    SELECT 'fabrice', 'm', '1982-10-03', '2', 'pl du miroir', '8', 'jette', 'bruxelles', 'be', '3';

GO

which gives an error:

Msg 4405, Level 16, State 1, Line 1 View or function 'user_all' is not updatable because the modification affects multiple base tables.

I found this tutorial but I must be missing something because I'm unable to achieve the desired result.

How can I insert in a view regrouping information from multiple tables?

USE [master]
DROP DATABASE IF EXISTS [dance_partner];
GO

CREATE DATABASE [dance_partner];
GO

USE [dance_partner];
GO

CREATE TABLE [user](
    [id_user] INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    [user_name] VARCHAR(45) UNIQUE,
    [User_Sex] CHAR(1),
    [date_of_birth] DATE,
    [account_type] INT,
    [id_address] INT,
);

GO

CREATE TABLE [address](
    [id_address] INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    [street] VARCHAR(255) NOT NULL,
    [number] INT NOT NULL,
    [locality] VARCHAR(255) NOT NULL,
    [city] VARCHAR(255) NOT NULL,
    [country_code] CHAR(2) NOT NULL
);

GO

CREATE TABLE [membership](
    [account_type] INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    [membership_name] VARCHAR(45),
    [membership_price] DECIMAL(4,2)
);

GO

CREATE TABLE [style](
    [style_ref] INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    [style_name] VARCHAR(45)
);
GO

CREATE TABLE [dance](
    [id_dance] INT NOT NULL IDENTITY(1,1),
    [dancer_1_id_user] INT,
    [dancer_2_id_user] INT,
    [dance_dtg] DATETIME,
    [style_ref] INT,
    FOREIGN KEY (dancer_1_id_user) REFERENCES [user] (id_user),
    FOREIGN KEY (dancer_2_id_user) REFERENCES [user] (id_user),
    FOREIGN KEY (style_ref) REFERENCES [style] (style_ref)  
);

GO

CREATE TABLE [user_dance_style](
    [id_user] INT,
    [style_ref] INT
    FOREIGN KEY (id_user) REFERENCES [user] (id_user),
    FOREIGN KEY (style_ref) REFERENCES [style] (style_ref)
)


ALTER TABLE [user]
   ADD CONSTRAINT fk_user_memebership FOREIGN KEY (account_type)
      REFERENCES membership (account_type),
      CONSTRAINT fk_user_address FOREIGN KEY (id_address)
      REFERENCES address (id_address);

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

INSERT INTO [membership] ([membership_name], [membership_price])
VALUES 
('free', '0'),
('regular', '15'),
('premium', '30')
GO

INSERT INTO [style]([style_name])
VALUES
('Salsa'),
('Bachata'),
('Kizomba')

GO

INSERT INTO [user] ([user_name], [User_Sex], [date_of_birth], [account_type], [id_address])
VALUES 
('sara', 'f', '1990-04-23', '1', '1'),
('elenor', 'f', '1989-02-18', '1', '2'),
('eva', 'f', '1987-01-04','1','3'),
('mike', 'm', '1985-05-02', '1', '4'),
('phil', 'm', '1985-03-01', '1', '5'),
('laurent', 'm', '1986-02-14', '2', '6'),
('nidia', 'f', '1985-01-16', '2', '7'),
('franz', 'm', '1990-03-17', '2', '8'),
('stephan', 'm', '1991-05-23', '2', '9'),
('sandra', 'f', '1993-03-25', '3', '10'),
('virginie', 'f', '1999-05-03', '3', '11'),
('claire', 'f', '1992-02-24', '3', '12'),
('laurence', 'f', '1991-04-26', '3', '13'),
('pierre', 'm', '1987-02-14', '3', '14'),
('thierry', 'm', '1989-01-04', '3', '15'),
('nancy', 'f', '1950-04-15', '1', '16'),
('cédric', 'm', '1980-02-02', '1', '17')

GO

INSERT INTO [address] ([street], [number], [locality], [city], [country_code])
VALUES
('av de l''exposition', '13', 'laeken', 'bruxelles', 'be'),
('rue cans', '2', 'ixelles', 'bruxelles', 'be'),
('rue goffart', '32', 'ixelles', 'bruxelles', 'be'),
('ch de haecht', '17', 'schaerbeek', 'bruxelles', 'be'),
('rue metsys', '108', 'schaerbeek', 'bruxelles', 'be'),
('rue du pré', '223', 'jette', 'bruxelles', 'be'),
('rue sergent sorenser', '65', 'ganshoren', 'bruxelles', 'be'),
('rue d''aumale', '38', 'anderlecht', 'bruxelles', 'be'),
('av de fré', '363', 'uccle', 'bruxelles', 'be'),
('rue de lisbonne', '52', 'saint gilles', 'bruxelles', 'be'),
('av neptune', '24', 'forest', 'bruxelles', 'be'),
('av mozart', '76', 'forest', 'bruxelles', 'be'),
('rue emile delva', '92', 'laeken', 'bruxelles', 'be'),
('av de la chasse', '68', 'etterbeek', 'bruxelles', 'be'),
('rue leopold 1', '42', 'laeken', 'bruxelles', 'be'),
('av charle woeste', '68', 'jette', 'bruxelles', 'be'),
('ch de boondael', '12', 'ixelles', 'bruxelles', 'be')

GO

INSERT INTO [user_dance_style] ([id_user], [style_ref])
VALUES
(1, 1),(1, 2),(1, 3),(2, 1),(2, 2),(2, 3),(3, 1),(3, 2),(4, 1),(4, 2),
(4, 3),(5, 2),(5, 3),(6, 1),(7, 3),(8, 3),(9, 1),(9, 2),(9, 3),(10, 1),
(10, 2),(10, 3),(11, 3),(12, 2),(13, 2),(14, 1),(15, 3),(16, 1)

GO

INSERT INTO [dance]([dancer_1_id_user], [dancer_2_id_user], [dance_dtg], [style_ref])
VALUES
(1, 2, convert(datetime, '2019-11-24 10:34:09 PM',20), 3),
(4, 2, convert(datetime, '2019-11-24 10:50:00 PM',20), 3),
(3, 5, convert(datetime, '2019-11-24 10:35:00 PM',20), 2),
(6, 1, convert(datetime, '2019-11-24 10:37:00 PM',20), 1),
(7, 2, convert(datetime, '2019-11-24 10:37:00 PM',20), 3),
(8, 1, convert(datetime, '2019-12-03 11:20:03 PM',20), 3),
(9, 3, convert(datetime, '2019-12-23 10:45:00 AM',20), 1),
(10, 12, convert(datetime, '2019-12-26 11:20:00 AM',20), 2),
(11, 4, convert(datetime, '2020-01-02 08:45:00 AM',20), 3),
(12, 5, convert(datetime, '2020-01-02 11:10:04 AM',20), 2),
(13, 12, convert(datetime, '2020-02-04 09:25:00 PM',20), 2),
(14, 10, convert(datetime, '2020-02-25 10:45:00 AM',20), 1),
(2, 14, convert(datetime, '2020-02-25 08:45:00 PM',20), 1),
(5, 10, convert(datetime, '2020-03-01 11:15:06 AM',20), 2),
(17, 2, convert(datetime, '2020-03-04 03:15:06 AM',20), 1)

GO

Based on the article "...In order to insert (update & delete) data to views created using multiple tables, you need to use an ‘Instead of trigger’. I guess you are missing the trigger. — Andy3B, May 19 '20 at 13:02
@Andy3B And I do, especially Update and Delete - what is the point of your comment? — Arvo, May 19 '20 at 13:09
@Andy3B the reason why I'm trying to do this is that if I want to create a new user, I have to insert the data in the user table and address table. I want to do this all at once, hence I thought about creating the view regrouping all these data and then try to make an insert. What would be the easiest way to achieve this if doing it through a view is not correct? — Tanuki, May 19 '20 at 14:27
i always think on maintenance. any single change on any of the tables involved, would imply an update on all the dependent views. — Andy3B, May 19 '20 at 14:32

score 2 · Answer 1 · answered May 19 '20 at 12:56

2

From the SQL Server CREATE VIEW documentation:

Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

From the tutorial that you linked to:

In order to insert (update & delete) data to views created using multiple tables, you need to use an ‘Instead of trigger’.

You haven't added such a trigger to your view.

For what it's worth, aside from exercises for curiosity's sake, I've never seen anyone try to update through a view in practice.

answered May 19 '20 at 12:56

Eric Brandt

7,886
3
18
35

1

It's uncommon, but sometimes done for backwards-compatibility after a schema change. Or similarly to give an application a simplified view of a complex schema. – David Browne - Microsoft May 19 '20 at 13:02
Re "I've never seen anyone try to update through a view ..." - maybe you should contact me :) Happens sometimes, of course not blindly and not on random views. – Arvo May 19 '20 at 13:03
1

@Arvo, I was careful there to only claim personal experience! – Eric Brandt May 19 '20 at 13:05
Thank you for your reply @EricBrandt, yes, it is just for exercise. I' m trying with triggers based on the tutorial but there is something I'm doing wrong: ```` CREATE TRIGGER TRG_insert_user ON user_all INSTEAD OF INSERT AS BEGIN INSERT INTO [user] SELECT [name], [sex], [date_of_birth], [account_type], [street], [number], [locality], [city], [country_code], [style_ref] FROM INSERTED END GO ```` – Tanuki May 19 '20 at 13:10
@Tanuki, if you're trying the trigger now and running into trouble, that would be a separate question. – Eric Brandt May 19 '20 at 13:16

Insert into view regrouping multiple tables?

1 Answers1