Press "Enter" to skip to content

Category: Triggers

Capturing Database Object Changes in SQL Server

Published 2024-10-14 by Kevin Feasel

Sebastiao Pereira creates a trigger:

Data Definition Language (DDL) is a group of SQL statements that can be executed to manage database objects. The idea is to create a database trigger that tracks and logs all changes to database schemas, including modifications to stored procedures, tables, views, and other schema objects and storing any event in one table.

Read on for an example of a database-level trigger. These are much less likely to cause performance problems compared to table-level triggers, though you could certainly cause issues via poor trigger definition.

Comments closed

The Brokenness of TABLESAMPLE

Published 2024-09-10 by Kevin Feasel

Paul White walks us through some issues:

Initial testing went well, which was a pleasant surprise. Soon enough though, errors started to appear in the tool’s output. That’s not entirely unexpected since ensuring consistent results under high concurrency tends to expose all sorts of niggly edge cases. It’s still an annoyance because debugging edge cases in trigger code can be tricky and laborious.

What was a surprise though was the nature of the error messages.

Read on for the full story. Paul has also created a feedback issue covering a problem with the function.

Comments closed

Retaining SQL Agent Job History in a Managed Instance

Published 2024-06-19 by Kevin Feasel

Andy Brownsword gets around the limitations:

In a Managed Instance, the SQL Agent job history is fixed at 1000 records or 100 records per job. This isn’t configurable like a regular SQL Server install. So how can we maintain a history of these if we want to retain those records?

There are 3 approaches which could be worth considering. Two of these have been well covered by others and the final one I’ll demonstrate here:

Click through for those three techniques.

Comments closed

Trigger Order: Oracle vs Postgres

Published 2024-05-28 by Kevin Feasel

Deepak Mahto talks trigger ordering:

Database triggers allow encapsulation of multiple functionalities that are automatically invoked on specific events or DML operations like INSERT, UPDATE, or DELETE. The invocation of triggers can be controlled as BEFORE or AFTER the triggering events, either for each change or per statement. In migrating from Oracle to PostgreSQL, it is important to be aware of triggers conversion gotchas.

In this blog, we discuss the default order of processing when multiple triggers are defined on the same table for the same events with the same characteristics. We will explore how this works in Oracle, how to alter the order, and how it operates in PostgreSQL.

This is good to know, but I’d also consider it a major code smell to have multiple triggers on the same table.

Comments closed

Avoid ROLLBACK in Triggers

Published 2024-04-15 by Kevin Feasel

Thom Andrews shares a bit of advice:

A problem I’ve seen raised on numerous occasions is users that are getting a non-descript error when they are doing some kind of DML/DDL operation, and they don’t know why they are getting the error, and what is causing it. That error is:

Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

Read on to learn more about good alternatives, remembering that you’re in the context of some other call when a trigger fires.

Comments closed

After Login Triggers in Oracle

Published 2023-12-27 by Kevin Feasel

David Fitzjarrell performs some auditing:

In a world of ever-increasing access account security has become a major concern. More often than not accounts used for automated processes gather more and more privileges, on the assumption that they are used only by managed, automated processes and execute vetted code, The argument is that these automated processes can’t go rogue and do damage with these elevated privileges, which is true, sort of. In and of itself the database does not police where these account connections originate, which can pose a significant security risk should this vetted code, or passwords for these privileged accounts, become known.

Oracle has, for decades, provided after logon triggers, which can be utilized to ‘police’ accounts by reporting where they originate and, if necessary, disallowing such logons. Of course it will be necessary to record such connection ‘violations’ in a table so that they may be investigated. A “complete” script to do this is shown below:

Click through for that script, as well as additional thoughts from David.

Comments closed

Triggers in Postgres

Published 2023-12-15 by Kevin Feasel

Semab Tariq explains how triggers work in PostgreSQL:

In the dynamic world of relational databases, where precision and efficiency are paramount, PostgreSQL emerges as a robust solution. At the heart of its functionality lies a feature that adds a layer of intelligence and automation to database operations – Triggers.

What exactly are triggers? They are predefined actions that the database executes automatically when a specific event occurs, such as an insertion, update, or deletion of records in a table. Think of triggers as the silent guardians behind the scenes, ensuring data integrity, enforcing business rules, and automating repetitive tasks.

These trigger examples also work for SQL Server, and my advice for triggers probably would be the same between platforms: use triggers sparingly because they can cause performance problems and it can be easy to forget that they exist when you’re troubleshooting why some operation isn’t working the way you expect.

Comments closed

Selective Fire for Trigger Execution

Published 2023-09-18 by Kevin Feasel

Erik Darling holds trigger fire until he sees the whites in their eyes:

I was helping a client with an issue recently where they wanted to allow certain admin users to override changes currently in a table, but not allow anyone else to make changes.

The thing is, users had to be allowed to make other changes to the table, so it wasn’t something that could be handled easily with security features.

The example I’m going to show here is simplified a bit to get the code across to you, so keep that in mind.

This is a somewhat wacky scenario, but Erik does get it working.

Comments closed