A Fine Slice Of SQL Server
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 closedDeepak 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 closedI will leave the finer points of the problems with MERGE statements to the Michaels and Aarons of the SQL Server world.
This is just a… But why? post about them, because I’m honestly a bit puzzled by this missing implementation detail.
Read on to see what has Erik wishing could be true.
Comments closedThom 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 closedDavid 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 closedSemab 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 closedRecently someone asked my wife Deborah if she knew if my sp_helpExpandView utility procedure could also work with triggers. Great question – I rarely work with triggers, especially nowadays, so I had no idea… and finally found some time and motivation to dig into it deeper.
Read on for the high-level answer as well as a deeper dive into the issue.
Comments closedErik 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 closedLaetitia 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 closedRyan 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