Using INSTEAD OF Trigger to Update View

I ran into a strange issue recently and thought it would make for a great blog post. I was notified of a standard application process failing with the following SQL error:

Msg 4405, Level 16, State 1, Line 19
View or function ‘XXXXX’ is not updatable because the modification affects multiple base tables.

I recognized the error but was baffled as to why it was showing up in this particular area. I’d updated or modified this view plenty of times in the past so what’s going on?

After some research, comparing, and head scratching, I discovered that this particular view was missing triggers that are normally in place to let updates complete. Once I reimplemented the usual triggers, specifically the update trigger, all was well.

Let’s look at a short example using an INSTEAD OF trigger to update a view.

Good vs. Evil

First, we’ll create a couple of tables to hold hero and villain names:

CREATE TABLE Hero (
	Id INT PRIMARY KEY
	,H_Name VARCHAR(20) NOT NULL
	);

CREATE TABLE Villain (
	Id INT PRIMARY KEY
	,V_Name VARCHAR(20) NOT NULL
	);

Let’s start with this famous pair:

INSERT Hero VALUES (1,'Batman');
INSERT Villain VALUES( 1,'Joker');

And we’ll add a view to see them together:

CREATE VIEW Rivals
AS
SELECT h.*
	,v.V_Name
FROM Hero h
JOIN Villain v ON h.id = v.id;

What if we want our first pair to be Superman and Lex Luthor instead of Batman and Joker? If we try an update statement, we’ll get an error:

UPDATE Rivals
SET H_Name = 'Superman'
	,V_Name = 'Lex Luthor'
WHERE Id = 1;
GO

Msg 4405, Level 16, State 1, Line 19
View or function ‘Rivals’ is not updatable because the modification affects multiple base tables.

Dude of Steel

How do we get passed this error? One solution is to add an INSTEAD OF UPDATE trigger. We’ll use this trigger to update Hero with the new Hero name and Villain with the new Villain name:

CREATE TRIGGER InsteadRivals ON Rivals
INSTEAD OF UPDATE
AS
BEGIN
	UPDATE h
	SET h.H_Name = i.H_Name
	FROM INSERTED i
	JOIN Hero h ON i.Id = h.Id

	PRINT 'Updated Hero Table'

	UPDATE v
	SET v.V_Name = i.V_Name
	FROM INSERTED i
	JOIN Villain v ON i.Id = v.Id

	PRINT 'Updated Villain Table'
END
GO

Now we can run our update again see the changes made:

UPDATE Rivals
SET H_Name = 'Superman'
	,V_Name = 'Lex Luthor'
WHERE Id = 1;
GO

Our update now succeeds and we can see our new rivals:

SELECT * FROM Rivals;
GO

Thanks for reading!