Press "Enter" to skip to content

Category: Triggers

After Login Triggers in Oracle

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

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

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

Against Triggers in PostgreSQL

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

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

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

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

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

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