How to Insert into a View and Detete from a View when it is not allowed.

At first, prepare tables and views:

USE tempdb;

GO

CREATE TABLE tbl_TestView_1(ID INT, F1 char(1));

GO

CREATE TABLE tbl_TestView_2(ID INT, F2 char(1));

GO

INSERT INTO tbl_TestView_1(ID, F1) VALUES(1,'A');

GO

INSERT INTO tbl_TestView_2(ID, F2) VALUES(1,'B');

GO

CREATE VIEW vw_TestView as

SELECT t1.ID, t1.F1, t2.F2

FROM tbl_TestView_1 as t1

INNER JOIN tbl_TestView_2 as t2

ON t1.ID = t2.ID;

GO

SELECT * FROM vw_TestView;
GO

Now try to Insert some values Into the View or Delete values from it:

INSERT INTO vw_TestView(ID, F1, F2)

VALUES(2, 'C','D'),(2, 'C','H');

GO

DELETE FROM vw_TestView

WHERE F1 = 'A';

GO

Here is the result:

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

Now will make a trick - create an INSTEAD OF Trigger:

CREATE TRIGGER trg_TestView on vw_TestView

INSTEAD OF INSERT

AS

BEGIN

INSERT INTO tbl_TestView_1(ID, F1)

SELECT DISTINCT ID, F1 FROM inserted;

INSERT INTO tbl_TestView_2(ID, F2)

SELECT DISTINCT ID, F2 FROM inserted;

END;

GO

Try to Insert:

INSERT INTO vw_TestView(ID, F1, F2)

VALUES(2, 'C','D'),(2, 'C','H');

GO

SELECT * FROM vw_TestView; 

GO

Here is the result:
ID          F1   F2
----------- ---- ----
1           A    B
2           C    D
2           C    H

 

Now modify trigger for Deletion:

ALTER TRIGGER trg_TestView on vw_TestView

INSTEAD OF INSERT, DELETE

AS

BEGIN

DELETE t FROM tbl_TestView_1 as t

INNER JOIN deleted as d

ON t.ID = d.ID and t.F1 = d.F1;

DELETE t FROM tbl_TestView_2 as t

INNER JOIN deleted as d

ON t.ID = d.ID and t.F2 = d.F2;

INSERT INTO tbl_TestView_1(ID, F1)

SELECT DISTINCT ID, F1 FROM inserted;

INSERT INTO tbl_TestView_2(ID, F2)

SELECT DISTINCT ID, F2 FROM inserted;

END;
GO

Try to Delete:

DELETE FROM vw_TestView 

WHERE F1 = 'A';

GO

SELECT * FROM vw_TestView; 

GO

Here is the result:
ID          F1   F2
----------- ---- ----
2           C    D
2           C    H

For a case when we do not need to delete records from the Parent table if the child table still have associated records we can modify trigger in this way:

ALTER TRIGGER trg_TestView on vw_TestView

INSTEAD OF INSERT, DELETE

AS

BEGIN

DELETE t FROM tbl_TestView_1 as t

INNER JOIN deleted as d ON t.ID = d.ID

WHERE NOT Exists (

SELECT TOP 1 1

FROM tbl_TestView_2 as t2

WHERE t2.ID = d.ID and t2.F2 != d.F2

);

DELETE t FROM tbl_TestView_2 as t

INNER JOIN deleted as d

ON t.ID = d.ID and t.F2 = d.F2;

INSERT INTO tbl_TestView_1(ID, F1)

SELECT DISTINCT ID, F1 FROM inserted;

INSERT INTO tbl_TestView_2(ID, F2)

SELECT DISTINCT ID, F2 FROM inserted;

END;

GO

Try to Delete:

DELETE FROM vw_TestView 

WHERE F2 = 'H';

GO

SELECT * FROM vw_TestView; 

GO

Here is the result:
ID          F1   F2
----------- ---- ----
2           C    D

Do not know if anybody will use this, but it is a fun feature.

Do not forget to cleanup at the end:

DROP TRIGGER trg_TestView;

GO

DROP VIEW vw_TestView;

GO

DROP TABLE tbl_TestView_1;

GO

DROP TABLE tbl_TestView_2;

GO