Press "Enter" to skip to content

Category: Triggers

Accelerated Database Recovery and tempdb Usage

Jason Hall takes a look at how much tempdb space Accelerated Database Recovery might use:

You might have heard me talk about tempdb parasites in the past, or maybe you’ve read my blog post on the same topic, “Be Mindful of SQL Server Tempdb Use (aka Tempdb Parasites!).” I know that at least one person did, because they recently asked a great question.

In that blog post, I reviewed how triggers use the version store in tempdb for access to the “special” trigger tables we can use from within the trigger code to access the previous and new versions of data being modified. One person on Twitter was wondering if that is still the case for triggers on databases using Accelerated Database Recovery (ADR) in SQL Server 2019. I really wasn’t sure, so I decided to find out.

Click through for the answer.

Leave a Comment

Reducing Trigger Executions

Jared Poche writes up a fun scenario he discovered:

I’ve never been a fan of triggers. I don’t like the idea of them adding an additional tax on every operation. It’s easy to forget they are even there, consuming your cycles. I’ve even seen a few nasty death-by-a-thousand-cuts scenarios with them. But I found something out this week that makes me like them even less.

I was tuning a procedure that runs 284 million times a day.

Over a number of servers and databases, but yes, that number is correct. It takes 2.5ms to run on average, with 1.0ms of CPU time. I’ll spare you the math, but that means over 3 cores of SQL Server are doing nothing but running this procedure 24/7/365. Anything we can do to improve this will be significant, even if we just shave off half a millisecond.

The best stories start with “I was tuning a procedure [which] runs 284 million times a day.”

Comments closed

Creating Triggers with Cosmos DB

Hasan Savran shows how you can create a trigger in Cosmos DB:

You have options if you need to use any type of triggers in Cosmos DB. There are two types of triggers in Cosmos DB. First one which I will cover here is the regular triggers which can be executed before (Pre-Triggers) or after (Post-Triggers) an operation. This type of triggers is written in JavaScript and you need to register them to a collection just like stored procedures. Second type of triggers can be created by Azure Functions and you can find more information about them in my older posts.

     Pre-Triggers and Post-Triggers do not take any input parameters. Since Cosmos DB needs to work more work to execute triggers, you will end up with higher Request Units for your queries. They might name triggers, but both do not get executed automatically with every operation. You need to call them programmatically if you want to run them.  If trigger throws any error for any reason, transaction will roll back and data will not be saved to the database.

Naturally, triggers are going to have a performance impact on your system regardless of the choice of data platform.

Comments closed

Disabling All Triggers on a Database

Jess Pomfret is on a mission:

Sometimes it’s best not to ask why. However, if for some reason you have a number of triggers on tables within a database that you would like to temporarily disable, read on.

I came across a situation recently while automating a process to refresh test environments where this exact scenario came up.  As part of the process several scripts were run to obfuscate production data. While these ran all the UPDATE triggers were firing. Not only were the triggers adding a significant amount of time to the process, they were also updating dates and other values that we’d prefer kept their original values.

Jess has a tiny bit of Powershell code which does this work for you.

Comments closed

Notifications when Admins Connect to a SQL Server

Jon Shaulis builds a logon trigger to notify when sysadmins log into his systems:

I was helping someone set up some monitoring in their database and they were asking about being notified when someone with administrative privileges logs into SQL Server. While many of you know that I try to use the right tool for the right job, some may cringe when I say triggers can help out in this scenario.

Now, there are other methods here but the goal was to be notified when someone logs into your SQL Server with administrative privileges. Triggers will consistently fire when this event occurs and allows you to perform an action based on this event.

Just make sure you get the trigger right and don’t block everybody from logging in. That’s an awkward situation.

Comments closed

Triggers and Multi-Record Changes

Brent Ozar points out a common problem with trigger design:

When you declare variables and set them using one row from the INSERTED or DELETED virtual table, you have no idea which row you’re going to get. Even worse, sometimes this trigger will update one row, and sometimes it won’t – because it might happen to grab a row with a reputation under 1,000!

It’s an easy mistake to make and one which can have a major impact.

Comments closed

Enabling and Disabling Triggers

Kenneth Fisher shows how you can enable and disable triggers in SQL Server:

I tend to feel that a lot of people who use triggers don’t really understand them. That said, every now and again you have to deal with them. And in particular (for this post) you might need to disable and then re-enable them. Enabling and disable are identical commands in this case so I’m just going to use the DISABLEversion and you can just replace it with ENABLE as needed.

To be fair, the world might be a better place if we disabled a majority of triggers…

Comments closed

Triggers In Postgres

Ryan Booz explains how triggers work in Postgres from the standpoint of someone familiar with SQL Server:

They are implemented as Functions (Stored Procedures)
This caught me off guard at first. I’ve been working with and dealing with triggers in SQL Server since day 1. They are first-class citizens… objects that have their own code blocks and rules.

PostgreSQL approaches it differently. Any reusable code block, regardless of its true purpose is a Function of varying types. Triggers are no different. Therefore, you write the logic of your trigger in a Function and then call it by adding a trigger to the DML event of a table.

Click through for a few more tips on triggers.

Comments closed

T-SQL Tuesday Trigger Roundup

Steve Jones has a bunch of links about triggers this month:

This month was my turn to host T-SQL Tuesday. I chose Trigger Headaches or Happinessas the topic, and I am glad that there have been quite a few responses.

I started the review almost immediately,and here are a few highlights. I separated these based on how I first thought of them after reading the post. If you think I’ve mischaracterized one, let me know.

In case you don’t want to add, it’s 9 to 13, so triggers are a headache.

It’s a good roundup of cautionary stories, tips, and tricks.

Comments closed

Trigger Spirals

David Fowler tells a story of woe, one which is totally not his fault:

To do this, a trigger was created which would send all the details via a Service Broker message to another SQL Server, this SQL Server was used to hold details of the AD accounts and from there, changes were automatically propagated out to AD.

This was working well until one day when it was realised that any changes to account permissions in AD weren’t reflected in the personnel database.  To solve this, another trigger was created to send a Service Broker message back to the personnel database with details of the change.

This was where I came in, it was noticed that the system had started to run slower and slower, not only that but permissions seemed to be constantly changing for no obvious reason.  Were the machines finally waking up and taking over?

There’s a reasonable explanation here, for some definition of reasonable.

Comments closed