Press "Enter" to skip to content

Category: Triggers

Using a Trigger to Auto-Refresh View Metadata

Published 2023-03-23 by Kevin Feasel

Aaron Bertrand keeps metadata in sync:

As much as we tell people to use SCHEMABINDING and avoid SELECT *, there is still a wide range of reasons people do not. A well-documented problem with SELECT * in views, specifically, is that the system caches the metadata about the view from the time the view was created, not when the view is queried. If the underlying table later changes, the view doesn’t reflect the updated schema without refreshing, altering, or recreating the view. Wouldn’t it be great if you could stop worrying about that scenario and have the system automatically keep the metadata in sync?

It’s almost entirely not apropos, but the first thing I thought of when I read the title and Problem statement was Goethe’s line about Mephistopheles: “Oft evil will shall evil mar.” Make of that what you will.

Leave a Comment

Updating Views with INSTEAD OF Triggers

Published 2022-04-26 by Kevin Feasel

Chad Callihan performs sleight of hand:

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?

Read on for the answer.

Comments closed

Preventing Triggers from Firing for a Single Process

Published 2022-03-09 by Kevin Feasel

Andy Mallon builds a trigger guard:

I recently saw a question on DBA Stack Exchange (it has since been deleted by the author), who had a “special process” that ran regularly, and as part of that process, they disabled the trigger, did some stuff, and re-enabled it. During that process, the step that disables the trigger would deadlock, and cause problems. So the asker was wondering how to catch & handle the deadlock during the DISABLE TRIGGER step.

Yeah, disabling the trigger, not so great. Read on for one interesting way of doing it, as well as a few other methods in the comments.

Comments closed

Logging Database Scoped Configuration Changes

Published 2021-11-26 by Kevin Feasel

Jonathan Kehayias wants to know what changes you’ve made:

The introduction of DATABASE SCOPED CONFIGURATIONS in SQL Server 2016 enabled different configuration settings at the individual database level. However, there is no logging of changes to the database scoped settings by default in SQL Server, making it nearly impossible to track down when a change was made and by who. After recently working on a client problem where performance issues were attributed to a DATABASE SCOPED CONFIGURATION of MAXDOP = 1 multiple times, I decided to create a DDL trigger for the ALTER_DATABASE_SCOPED_CONFIGURATION events in SQL Server to have it log the change to the ERRORLOG file similar to the one I wrote years ago for logging Extended Event session changes.

Click through for the definition of that trigger.

Comments closed

Using Triggers for Change Tracking

Published 2021-09-07 by Kevin Feasel

Chad Callihan shows how you can roll your own change tracking with triggers:

Have you ever come across triggers being used to audit table changes? In this post, we’ll look at a few examples of DML triggers in action.

Click through for examples around tracking updates and last edit times, as well as creating audit tables for operations.

If you do go this route, Chad has an important warning. In addition, I appreciate that the statements in this example support multi-row updates. That’s something people often forget when writing triggers.

Comments closed

Inserted and Deleted Scans

Published 2021-04-16 by Kevin Feasel

Hugo Kornelis has a two-parter for us. First up is the inserted scan operator:

The Inserted Scan operator is only found in execution plans for code in triggers. It is used for queries that read data from the inserted pseudo-table. Its counterpart, Deleted Scan, reads from the deleted pseudo-table.

This pseudo-table contains a copy of all the rows that were inserted in AFTER INSERT triggers, or the new content of the data in all affected rows in AFTER UPDATE triggers. In INSTEAD OF INSERT or INSTEAD OF UPDATE triggers, the data in the inserted pseudo-table is the data that would have been inserted, or the data as it would have been after the update. In AFTER DELETE and INSTEAD OF DELETE triggers, using the inserted pseudo-table is allowed but returns no data.

Then, the deleted scan:

This pseudo-table contains a copy of all rows that has just been deleted in AFTER DELETE triggers, or the original data of all affected rows in AFTER UPDATE triggers. In INSTEAD OF DELETE or INSTEAD OF UPDATE triggers, the data in the deleted pseudo-table is the current data in the rows that would have been deleted or updated. In AFTER INSERT and INSTEAD OF INSERT triggers, using the deleted pseudo-table is allowed but returns no data.

Click through to see how they work.

Comments closed

Capturing Extended Properties when a Table is Dropped

Published 2021-03-08 by Kevin Feasel

Thomas Rushton attempts to preserve extended property history:

Custom extended properties can be a useful tool when creating a self-documenting database. However, in an environment where tables are routinely dropped and recreated by software/systems not within your control, it may be desirable to have these stored so they can be repopulated when the table is recreated.

Read on to learn about the results of each test. Also read on for a use of the term “faff” in its appropriate context.

Comments closed

Improving Triggers

Published 2021-01-29 by Kevin Feasel

Ed Pollack tries to improve our usage of triggers:

SQL Server triggers are a subject of both functionality and controversy on any database platform. They can provide immense utility at a low cost, but are often misused by developers, DBAs, and architects alike, resulting in performance bottlenecks or maintainability challenges.

This article briefly reviews DML (data manipulation language) triggers and delves into a discussion about how triggers can be efficiently and effectively used as well as when triggers can dig developers into holes that are hard to escape.

Read the whole thing. Triggers are rather useful tools when used correctly, but it’s easy to misuse them on accident.

Comments closed

Triggers Should Handle Multi-Row Scenarios

Published 2020-09-04 by Kevin Feasel

Kenneth Fisher has a public service announcement:

While I was helping him with part of it I noticed that the trigger was only set up to handle single row updates. When I told him it needed to be able to handle multiple row updates he asked me in all seriousness, “Why? Since only one value can be changed at a time why should I worry about multiple rows being changed at once?”

Read on for Kenneth’s response, which approximates to Au contraire mon frère. Unhandled assumptions often become bugs.

Comments closed