See the full index.
Today, I want to touch on a few ways that I see triggers being misused.
Using a trigger at all
A lot of people think that they need a trigger. They allow direct access to tables instead of forcing data access through stored procedures, and then later realize that they want to control a modified date column, or append rows to a DML audit table, or roll back on some condition. Since they have ad hoc queries in their application code, it is not always practical to go out and add additional code to existing queries.
Ideally, you should control data access via stored procedures, both for security reasons and so that you can control the DML that affects your tables. In a stored procedure, you can apply conversions to incoming values, and supply data for unspecified columns, instead of having to deal with it in a trigger. Already using stored procedures to update your table? Great! Just add the modified date column to your UPDATE statement, and you can disable your trigger. There will be cases where you won't have a choice but to use triggers. You can't always convert to stored procedures, and you can't always prevent certain people from bypassing your stored procedures and modifying the table directly. Keep in mind that triggers can be disabled by people with sufficient permissions, so it is not your be-all and end-all as an auditing tool.
Not preparing for multi-row updates
A lot of people coming from an Oracle or other background assume that a trigger will fire for every row that is affected. For SQL Server, this is not true; a trigger fires once per DML operation. I often see code examples that look like this:
CREATE TRIGGER dbo.foo_update ON dbo.foo FOR UPDATE AS BEGIN DECLARE @bar int; SET @bar = (SELECT bar FROM inserted); -- other stuff with @bar END GO
Now, if you do something like:
INSERT dbo.foo(bar) VALUES(1),(2);
You will receive a "subquery returned more than one value" error, and the original update will fail. Oops!
Similarly problematic:
CREATE TRIGGER dbo.foo_update ON dbo.foo FOR UPDATE AS BEGIN DECLARE @bar int; SELECT @bar = bar FROM inserted; -- other stuff with @bar, e.g. SELECT @bar; END GO
This will work fine for a single-row update. A multi-row update won't fail; only one (arbitrary) row will be displayed, because only one value can be assigned to the variable. But you won't be able to reliably predict which one (and SQL Server won't raise a warning, an error, or any kind of notification that it ignored the other rows). Here is an example of this "failing":
CREATE TABLE dbo.foo(id int PRIMARY KEY, score int, updated date); INSERT dbo.foo(id,score) VALUES(1,10),(2,20),(3,30); -- all three rows have NULL for updated GO CREATE TRIGGER dbo.foo_update ON dbo.foo FOR UPDATE AS BEGIN DECLARE @foo_id int; SELECT @foo_id = id FROM inserted; UPDATE dbo.foo SET updated = '20200101' WHERE id = @foo_id; END GO UPDATE dbo.foo SET score += 1 WHERE id IN (1,2); GO SELECT * FROM dbo.foo; /* results: id score updated ---- ------- ------------ 1 11 2020-01-01 -- this row was updated, and the trigger also updated it 2 21 NULL -- this row was updated, but the trigger didn't touch it 3 30 NULL -- this row wasn't touched by the update or the trigger */
You need to code your trigger to handle the affected rows as a set, instead of expecting the inserted or deleted pseudo-tables to contain exactly one row. Typically you do this using a query or join against inserted/deleted instead of variable assignment. In this example:
CREATE TRIGGER dbo.foo_update ON dbo.foo FOR UPDATE AS BEGIN UPDATE f SET updated = '20200101' FROM dbo.foo AS f INNER JOIN inserted AS i ON f.id = i.id; END GO
There is no straightforward way to immediately convert your thinking to a set-based frame of mind. I will say, if you find that the only way to solve this problem leads to a cursor in your trigger, stop what you're doing, build up a simple demonstration that explains what you are trying to do, and post your question on Database Administrators Stack Exchange.
MERGE WITH CAUTION
Note that there is potentially some confusing trigger behavior in SQL Server 2008 when using the MERGE command; a trigger that handles multiple operations can fire multiple times. Hugo Kornelis filed a bug on Connect; it was closed as "by design." For a lot more on the problems with MERGE and why I try to block all usage:
Performing inappropriate actions
A lot of times I've gone out of my way to convince people to stop performing quite expensive operations in their triggers. The most common action I have seen is sending an e-mail. I am not sure why people think it is a good idea to send e-mail from within a trigger; since the module is tied to the transaction that caused it to fire, now that transaction has to wait for the return of whatever process you're calling to dispatch the e-mail (even calls that are quasi-asynchronous, like database mail). Similarly troublesome actions include functionality outside of SQL Server, such as COM / OLE automation objects (sp_OA*), extended procedures, and linked server queries. Anything that takes you outside of SQL Server's direct and local control can quickly become problematic.
So what can you do to get around this? Well you can certainly consider using Service Broker, which will allow you to perform any of these actions asynchronously; this allows your trigger to return control to the calling session immediately. See these posts from Jonathan Kehayias: Part 1 and Part 2. As an alternative to Service Broker, you could set up your own simple queue table (see guidance from Remus Rusanu), and have a background job that runs periodically and checks for any new tasks you've placed on this queue. Again, this means your trigger only has to perform the insert into the queue table, and not force the calling transaction to wait for any subsequent actions that need to take place.
Summary
Triggers are not free, and can lead to various other potential problems in your application. I have touched on a few of them here, but there are several others you will come across if you perform your due diligence. Alex Kuznetsov went over several potential problems with triggers in great detail in his Defensive Database Programming series earlier this year. Personally, I try to avoid them by solving problems in other ways; however, if you have to have them, it is important to understand some of the limitations and gotchas they can introduce to your environment.
See the full index.
I realize this blog post is many months old but it contains a reference to something not mentioned again at least not that is searched; the queue table.
Is there any existing template/sample for a basic queue table including related SP's and such that would be required to make the QT (Queue Table) work? I use triggers to catch potential problems before they sit too long and become harder to fix. I could buld one from scratch but I find it hard to believce theres not a design already out there for a QT on SQL Server similiar to the number of Index management scripts out there.
Thanks
As the current self-proclaimed king of the unprofessional, using jerk will not get you invited back to the meetings. I expect to see much more serious language. We have strict quotas that must be maintained. Without at least three ass hats, shit for brains, etc per week you'll be tossed from the club.
Jerk.
Ha ha ha! Thanks for the laugh, Brent!
Kevin is right. Instead of jerks, you should use more hip phrases like asshat, dorkwad, or Charlie Sheen's latest contribution, troll.
Aaron, as long as you're using old language, nobody is going to take you seriously. You're such an amateur.
Good article, but any use of the word 'jerks' just reads as very unprofessional.
Michael, can you give an example where you've been torn? Obviously you need a query at some point but I assume you are talking about cases where two procedures might need to run similar or identical queries?
When you refer to using procs as access methods are you referring to all instances or just those external to that instance of SQL Server? Over time I have definitely shifted from merely preferring proc access to requiring proc access for external access but am torn when it comes to queries within procs. I run into occassional situations where I wish I had encapsulated but I surely do not want to go to the extreme.
Wow, thin skin, Mr. Anonymous?
The jerks i was referring to, if you had bothered to read the context, are the ones who work in an environment where there is a procedure-only policy in place, they just choose to knowingly circumvent the policy because they can, don't care, are lazy, what have you.
I'll say it another way to make it clear: I was not in any way calling people who don't use stored procedures jerks; I was talking about the people who don't find it useful to follow existing conventions in a particular environment.
I'm sorry the concept was lost on you and that you misinterpreted my intent. If that makes you call me names and find less value in my content, so be it. But if you ever work for me, be prepared to stand down.
I may have pretty strong opinions about things like this, but that's the beauty of a blog: I'm allowed to state my opinions. At least I put my name behind mine. Perhaps I should disable anonymous comments to bypass having to defend my opinions from drive-by shooters like yourself.
Cheers,
Aaron
I started reading with interest, until I got to the word "jerks" crossed out. You called for this: jerk you! Sproc-only data access is your jerking opinion, and it's obviously not the only correct opinion since there are a ton of people not sharing it with you (otherwise, non-sproc access wouldn't even exist). So, before you call people names, next time use your jerking old-fashioned dba brain to think for a second. Jerk!
HardCode, like I said:
"There will be cases where you won't have a choice but to use triggers."
In the case you are talking about, I think it is still possible to avoid triggers (e.g. by denying all write access to the base tables and forcing modifications through stored procedures), but the conversion may take longer than the time the enforcement needs to start happening, and aside from that, may not be worth it.
My comments in the first section are not meant to imply, "nobody should ever use triggers," more along the lines of, "a lot of people use triggers when they don't need to."
Aaron, how would you weigh triggers to enter records into an audit table of the target table? We use triggers for this, because we have to capture all changes made to all records for regulatory reasons.
Tom, I agree. However I treated the case in triggers because:
(a) treating bad habits people have wrt transactions is a much lengthier topic. This had enough weight with triggers alone to form a decent post and generated some good dialog as well.
(b) I find that the "sending an e-mail from a trigger" requirement is extremely more common than the "sending an e-mail from within a transaction" requirement. I don't doubt that the latter exists, in fact I am sure it is quite rampant. But when I have my community support hat on, I see all too often the request about how to send e-mail from a trigger, or why is my trigger so slow because I'm sending an e-mail, or how can I send 30 e-mails from my trigger without using a cursor?
Inapprpiate actions is not just an issue with triggers; as a general rule these actions should never be performed when any locks are held – never performed within a transaction. Dumping these actions off onto an asynchronous queue handler is not much help if that queue handler holds an exclusive lock on the queue while performing that action (I've seen this done: begin transaction, take the date for 1 email fromk the queue, delete the data, construct the email, send it, if send failed roll back else commit: criminally stupid, in my opinion).SO performing inappropriate actions within a trigger is just another instance of performing inapproopriate actions within a transaction and shouldn't be regarded as something affecting triggers only.
I still don't like it, for a few reasons.
1. if you should ever switch from the built-in mail solution to something else (I had to back in the early 2005 days), you will have to ensure it uses a similar methodology, or potentially re-write your triggers. You'll have to change all your triggers regardless, because you've hard-coded the delivery mechanism in each one!
2. using your own queue table, you can quickly and easily adjust the frequency of e-mails relative to the number of trigger invocations. Imagine a row is updated 500 times, you're going to send 500 e-mails, when perhaps only the last one is relevant. With a queue table you can easily summarize all the new rows since the last poll, and send a single e-mail with either counts or each line item depending on the requirements.
A very low cost up front design can provide both flexibility and scalability without losing a single thing.
I don't like either sending emails from inside the trigger, but recently when I mentioned this to a colleague he replied that the sp_send_dbmal just post the message into the queue. I still don't like this approach but what do you have to say about this?
I guess what I was stating when I talked about controlling the DML that affects your tables is that you should be forcing your applications and users to use the API (the stored procedures).
I agree with Thomas, that there are some cases where you can't *force* the use of the API, you can only "encourage." My point at the end of the article is that, in those cases where you have to have them, do your homework.
Over my many years of development, I have discovered that stored procedures are not always sufficient to protect data integrity. Sometimes, you have to bake the rules into a trigger to prevent the amateur DBA from screwing the data. Typically, this amateur DBA has been with the company for some time, or in egregious cases, is the CEO/Owner themselves. Using triggers to enforce unusual and important data integrity rules is about the only place where I use triggers.
"Ideally, you should control data access via stored procedures, both for security reasons and so that you can control the DML that affects your tables."
Those are good things, absolutely. But they miss the value of using something like stored procedures to access the database.
You can use stored procedures to create an "API". This helps reduce the intimate coupling between an application and the database. With an API in place, if you need to redesign how some data is stored, you can often do so without having to redesign the API.
Reducing coupling is a software design principle. It's fundamental to well designed software. But for some reason, people think it's okay for applications to poke around directly in a database.
Just like any other form of code, database code is best when it is well designed. And the interface between an application and the database is important. A well-designed API is critical to the longevity of an application.
So when people ask me why do I not recommend triggers, my point is usually around the need to break a design fundamental. You don't go poking around directly in tables any more than you go calling code buried in the middle of a module. You use the API. And with a good API, there is almost never a need for triggers.
>Brian, I agree completely.
Excellent. I was hoping that's what you meant. Between that and the CHAR/VARCHAR comments about space savings, i am very happy you posted these. Perhaps a summary at the end with one line (and a link) for each? A quick sheet, that's all.
>However, in some cases people don't have the ability to make such
>sweeping changes overnight, because existing applications need to
>continue to function.
Aah, instead of Aaron Bertand thou shall be knownst as Aaron the Benevolent.
Brian, I agree completely. However, in some cases people don't have the ability to make such sweeping changes overnight, because existing applications need to continue to function.
I think it's a bad habit to use TRIGGERs at all. Better to lock down the TABLE and only GRANT EXECUTE on PROCEDUREs that define all actions that need to be taken.
I use TRIGGERs for logon security, db maintenance (where required), and things that just are not part of the application, per se.
Aaron,
Forgive me Master, I am but learning, and in my learning, I thought I was being brilliant.
I have a web page that actually runs on a file share, with a form. (They took away my server, I had to do something!) The page has VBScript on it, that collects form data and Inserts it into table on our SQL Server. Things need to be done with that data, and immediately!
The table has a trigger.
I thought I was doing so well! This thing not only writes an email, it goes out to the filesystem to find any files uploaded to the form (handled on the web page script) to attach them. Then, after the send_dbmail, (blind copying me, so I know it ran – I said brilliant, right?) it deletes the file (no error checking, you can always delete a file, right? Yeah.)
After doing whatever it does there (*usually* good things), it then EXECs a SP that again goes out to the filesystem, and, through OLE Automation, (sp_OA*, do you mention that above? I *must* be doing things right!) opens not one, but two spreadsheets, inserts information to them, saves them, and finally returns control to the webpage, which puts up a message box that life is good, reassuring the poor user who has been sitting there staring at a web page for several seconds after clicking Submit.
Is this bad? Is this what you mean by trigger abuse? But I meant no harm! I thought I was doing what was right! I thought… never mind what I thought, I am a self taught Accidental DBA/Developer, my thinking is skewed. I'll go fix it. Thanks for pointing out the error of my ways.
Dyfhid