Michael J. Swart

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.

Consider the Following

Did you know you can do science anywhere. Science is about learning how Nature behaves. Well what if we replace Mother Nature with SQL Server? SQL Server’s not as pretty, but we can still learn things. It’s 2012! When we wonder about things – even databases – we don’t have to guess.

The Question

To keep things simple, I’m going to follow the scientific method here and this is step one. The question. All good science starts with a question. The question itself doesn’t matter. I just want to talk about the process.

So I take the question from this DBA Stackexchange question: Why are NULLS sorted first?

Good question! The SQL Standard does not actually prescribe any behavior here (says Wikipedia). So the actual behavior is left up to the RDBMS vendor. The likely answer to the question is that NULLS are sorted that way because Microsoft had to pick something and they probably just made a choice based on what other RDBMS’s were doing or other implementation considerations. Or who knows, maybe they just flipped a coin.

But that leads me to this question: Are they always sorted first? If I reverse the order of a sort by using ORDER BY column DESC do Null values still show up first? If I look at the manual, Microsoft tells me “Null values are treated as the lowest possible values” so that ORDER BY column DESC will place Null values at the end.

But the way the stack overflow question is worded makes me wonder a little and I’d like to find out for sure. So to repeat:

“If I sort the results of a query using ORDER BY column DESC do Null values come last?”

Hypothesis / Prediction

I’m placing my bets with Microsoft’s documentation and so I answer YES.

I predict that any query written with “ORDER BY column DESC” will place the Null values at the end. If I’m wrong, then it would seem like null values always first is the behavior here (Note: NULLS FIRST is actually mentioned in the SQL Standards as an extension to the ORDER BY clause, but it’s not implemented by SQL Server. This also lets me believe I’m not crazy for wondering.)

Test

That’s simple enough:

select *
from (values (1),(2),(3),(null)) as v(value)
order by value desc

The prediction says that the fourth value in the results will be NULL. And tada! The prediction is correct.

Analysis

There’s nothing surprising here, but maybe it raises some new questions. Like:

• What do other vendors do? (Turns out they do the same. This lends weight to Microsoft picking this behavior because it was common practice)
• What’s up with this NULLS FIRST keyword?

There’s a publish part here. The default assumption (that Microsoft documentation is often correct) is still valid so there’s nothing surprising to publish. Probably not worth a blog post (despite what you’re reading).

Boiled Down

Normally the thought processes aren’t so structured, but as long as the question comes first and the guess is checked against experiment, it counts.

It seems like common sense. And maybe I could have picked a better example. Science helps satisfy our curiosity. But it also can save us from kidding ourselves. Without this kind of thinking we risk turning into superstitious pigeons. I once saw someone perform a task (release and renew his ip address) three times out of habit “just so it takes”. That way lies madness.