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
