Triggers On Views? What For?

October 31, 2012

Filed under: SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 12:00 pm

What’s up with triggers on views? What kind of patchwork monster is this?

I’m sharing something that I learned this week. It’s something I learned while talking to a friend at work. He wondered whether SQL Server had any feature similar to the rule system over at Postgres. (I thought no at first, but keep reading). You know, I continue to learn tons while talking to friends at work even though I’m the in-house SQL Server specialist. I don’t mind at all.

Aside: Have I ever mentioned how amazing the people I work with are? I work for a great company and we’re hiring like crazy.

Back to the post. Here’s what I learned:

I knew that insert/update/delete statements could be executed on views if the view was simple enough. If it was simple enough we call the view “updatable”.
I thought that triggers couldn’t be defined on views but
I was wrong and …
I learned that not only are triggers allowed on views but INSTEAD-OF triggers will work on any kind of view, updatable or not.

Example

Here’s what I mean.

create table A (id int primary key)
create table B (id int primary key)
go
 
create view AB as
    select id, 'a' as tablename from A
    union all
    select id, 'b' as tablename from B
go

If I were to try to insert rows into the view, I get this error message:

'UNION ALL view ~ is not updatable because a partitioning column was not found.'

Other views give other errors, but the message is the same: “Your view is too complicated to be updatable”. That’s okay. I never really expected inserts to work on anything but the simplest views so I’m not too disappointed.

But like I said, here’s how you would make this view updatable – or rather insertable – with an INSTEAD OF trigger:

create trigger t_AB
on AB
instead of insert
as
    if exists (select 1 from inserted where isnull(tablename,'') not in ('a','b'))
         raiserror (N'column ''tablename'' must be either ''a'' or ''b''' 16, 1);
    else
    begin
        insert A (id) select id from inserted where tablename = 'a'
        insert B (id) select id from inserted where tablename = 'b' 
    end
GO

Boom! No more this-view-is-not-updatable errors.

But When Would I Use This?

I thought of the perfect use case. This strategy helps with SCHEMA REFACTORING. Say your schema contains this table:

create table ANIMALS 
(
    Name varchar(100) primary key,
    IsVertebrate bit not null,
    VertebraeCount int
)

and you’ve decided to change it to look like this:

create table ANIMALS_BASE
(
    Name varchar(100) primary key
)
 
create table VERTEBRATES
(
    Name varchar(100) primary key
        references ANIMALS_BASE(Name),
    VertebraeCount int not null
)

After you’ve done that, you can create a view called ANIMALS with triggers to accommodate applications that still expect the old schema. That would look something like:

CREATE VIEW ANIMALS
AS
    SELECT AB.Name, 
        CASE WHEN V.Name IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END as IsVertebrate,
        V.VertebraeCount
    FROM ANIMALS_BASE AB
    LEFT JOIN VERTEBRATES V
        ON AB.Name = V.Name

Next create the insert, delete and update triggers. I only show the insert trigger here. The delete and update shouldn’t be too hard to write either.

create trigger t_ANIMALS_i
on ANIMALS
instead of insert
as
    INSERT ANIMALS_BASE (Name) SELECT Name FROM inserted;
    INSERT VERTEBRATES (Name, VertebraeCount)
        SELECT Name, VertebraeCount 
        FROM inserted 
        WHERE IsVertebrate = 1;

Here’s a few ways that this helps:

You might want to use this strategy when you don’t have control over every application using your database.
This helps with migrations because applications are now free to expect the new or old schema. The applications can transition gradually to the new way of doing things.

Even if you believe every application uses only the new schema. You can still make sure gradually by logging whenever someone uses the old schema. Just add some logging statement to the definition of the triggers. You can log or track trigger use using any way you’re comfortable with. One suggestion is to use the user-settable performance counter via sp_increment_counter1 then Poof! You’ve got your own deprecated-features performance counter just like Microsoft.

-- Comments (22)

22 Comments »

Awesome post. Well explained, plus a use-case. Thanks for taking the time to do this.

Comment by JJEugene — November 1, 2012 @ 1:02 pm
Thanks for the feedback JJEugene. It was a fun post to write.

Comment by Michael J. Swart — November 1, 2012 @ 1:37 pm
One catch that I’ve been trying to work around this week – if this is for a IIS/.Net web page using a SqlDataSource (or maybe it’s called a sqlDataControl), the object will NOT use views properly. Behind the scenes, it executes sys.columns to get a field listing, then updates the UNDERLYING TABLE DIRECTLY. Our dev had no idea it could even do that, can’t turn it off,, so what should have been a simple table/view flip has turned into a nightmare as we replace views with tables that use AFTER triggers, and changing other SPs to keep everything in sync. It’s a CF.

TL;DR : __make__ your web devs use SPs. Don’t let them insert into a table directly.

Comment by Mbourgon — November 2, 2012 @ 1:32 am
Hey there MBourgon,

Digressing a bit here, but you’ve hit on one of the reasons that triggers make me uneasy. Triggers are not bad for their own sake, but they’re a symptom that something else is wrong. In your case as you’ve found out, you’re already very very aware of what’s wrong. It’s just that you don’t have control to do anything about it. And that’s always frustrating.

Comment by Michael J. Swart — November 2, 2012 @ 9:13 am
[…] Triggers On Views? What For? – Michael J. Swart (Blog|Twitter) explains in this excellent walk-through. […]

Pingback by Something for the Weekend - SQL Server Links 02/11/12 — November 2, 2012 @ 10:21 am
Yup – wanted to post it somewhere in hopes somebody could use it as an object lesson of why not to allow it to do direct inserts into tables.

The reason it was done that way originally makes sense: they didn’t want to have to write 50 stored procedures to access the data
In retrospect, if I’d known of the web page, we could have come up with a Stored Procedure that did this, albeit with either dynamic SQL or with the SP hitting a view that consolidated the tables.

There is a stackoverflow case on it, albeit with no responses. http://stackoverflow.com/questions/13077537/how-to-make-sqldatasource-insert-into-view-instead-of-the-underlying-table

Triggers aren’t always a bad thing. But they can have significant gotchas.

Comment by mbourgon — November 2, 2012 @ 11:03 am
FWIW: PostgreSQL 9.1 introduced INSTEAD OF triggers on views and triggers on views are the recommended approach from 9.1+ over rules for updating views just because they are more predictable.

Comment by Regina — November 16, 2012 @ 7:05 pm
Dear Frankenstein,

I love your post– and I also think instead of triggers are an awesome option, especially for partitioned views. (The rules to make the insert-able by normal means are not always so attractive).

What a great way to explain it.

Kendra

Comment by Kendra Little — November 8, 2013 @ 6:59 pm
Dear Frankie,

I know it has been time but you are missing the comma before the 16. Great explanation BTW.

Comment by Nelson G — April 4, 2014 @ 4:35 pm
Found out about triggers on views myself today (thanks to the Brent Ozar Unlimited team and sp_Blitz). I probably heard about them back in the day, but never had any inclination to use them and that information was wiped from memory!

Great idea around the schema refactoring!

Comment by Clive Strong — February 23, 2015 @ 10:26 am
That’s great to hear Clive! Thanks for stopping by.

Comment by Michael J. Swart — February 23, 2015 @ 10:40 am
Hi Frank,
I have a situation where I have Table A and Table B and View C created by joining table A and tale B.
I have written a instead of trigger D on view C.
I do not insert/update/delete on the view directly.

My requirement is:
For any insert/update in Table A/B should directly insert/update the view and hence trigger should be invoked for this insert/update action on view.
I am unable to see this happen.
Can you please help me in this.

Thanks in advance.

Comment by AJVK — April 21, 2015 @ 4:46 am
Hi AJVK,

You say any insert/update that occurs on table A or B should directly insert/update the view. I wonder if it’s clear that when using SQL Server, views don’t need to be maintained this way. Views are always up to date with their base tables.

If you never perform insert/update/delete statements against the view, then you don’t need a trigger on that view.

If you have further questions, I think an example would be helpful (sample create table / create view statements) and the perfect place for that is on dba stackexchange. If you post more questions there, let me know.

Comment by Michael J. Swart — April 21, 2015 @ 9:28 am
Another thumbs up for a very clear explanation. This is exactly what I’m going use in changing a schema from one where a field containing comma delimited lists to a bona fide many-to-many type arrangement. I can allow users to continue editing with comma delimited lists using a view, while the instead of trigger can do the string splits, etc.

Comment by Karl — June 5, 2015 @ 12:52 pm
Nice! Glad to hear it Karl!

Comment by Michael J. Swart — June 17, 2015 @ 11:02 am
Very nice article with simple examples to understand the concept. Thumbs up!

Comment by Fahim — November 12, 2015 @ 9:28 pm
Hi,

I have created a view from several tables.

I want to insert into table b from the view last capture record.

Am getting error message “The object ‘VIEWA’ does not exist or is invalid for this operation.

Please advise

Comment by JAMES — May 26, 2019 @ 8:21 am
Hi JAMES,
This kind of question is perfect for dba.stackexchange.
When you ask a question there, remember to paste as much code as you can share (the definition of the tables, views etc…)

Comment by Michael J. Swart — May 27, 2019 @ 9:05 am
Very Helpful!!

Comment by Jose — June 11, 2019 @ 8:03 am
Hi Frank, We have 2 types of DML triggers. After Trigger and Instead of Trigger. Can we create after trigger on views ?

Comment by Ajith — August 25, 2019 @ 10:25 am
Why do I get this error ‘ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword’ when I already write instead of insert

Comment by Diya — November 17, 2020 @ 8:05 am
That’s an error from an Oracle database, all my blog posts are written about SQL Server. They both understand SQL but there are many differences.

Comment by Michael J. Swart — November 17, 2020 @ 8:13 am

RSS feed for comments on this post. TrackBack URL

Michael J. Swart

October 31, 2012