Press "Enter" to skip to content

Category: Triggers

Against Triggers in PostgreSQL

Published 2023-04-10 by Kevin Feasel

Laetitia Avrot is not a fan of triggers:

My opinion comes from years of practicing as a production DBA, then as a database consultant. As such a professional, my opinion is biased because I am never called when it works! I’ve always been called when there are problems (big problems, usually) so that I see the worst developers can do and never the best. I try to be aware of that bias, but it’s not that easy.

I am sympathetic to Laetitia’s argument but ultimately don’t agree, at least in the general case. Some of these thoughts and alternatives are Postgres-specific, so I don’t have an opinion on those.

Comments closed

A Love-Hate Relationship with Triggers

Published 2023-04-10 by Kevin Feasel

Ryan Booz shares some thoughts on triggers:

By design, plain ANSI SQL is declarative (“hey database, this is the data I want, you figure out how to do it”), not procedural (“Hey database, I want this data and I want you to retrieve it like this”). Early on, there wasn’t a standard design for how to add on additional procedural-like features, although that later came with the definition of SQL/PSM sometime in the mid-90s.

However, through the late 80s and most of the 90s, database vendors were trying to keep pace with very quickly changing requirements and needs in the database space. Even though triggers weren’t officially added until the SQL:99 standard, databases like Oracle had already released their own procedural languages and features. Triggers may have been deferred in the SQL-92 standard, but the Standards team couldn’t ignore them (or the complexity that triggers add to transactional consistency).

Click through for a bit more background, some of the pros and cons of triggers, and a few cases where triggers can make sense.

Comments closed

Audit Operations with Triggers in PostgreSQL

Published 2023-04-07 by Kevin Feasel

Ryan Lambert creates a trigger:

I rarely use triggers. I don’t hate triggers, I just think they should be used sparingly. Like any specialized tool, you should not expect to use triggers for every occasion where they could be used. However… there is one notable use where case I really like triggers: audit tables. Part of the magic of using triggers for auditing data changes in Postgres is the JSON/JSONB support available.

I think this attitude toward triggers is correct. Triggers are really useful, but they come with a few drawbacks, such as tools not making it readily obvious which tables have triggers associated with them, the effects of triggers being “hidden” until execution, and execution problems (e.g., performance issues, potentially modifying data incorrectly, assuming triggers will only work with one row at a time, etc.).

Comments closed

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.

Comments closed

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