Some people think triggers are the best thing ever while others think triggers are the ultimate evil. I've never been a fan of triggers and could yammer on about why but there are lots of great articles already readily available that outline why triggers can be dangerous (check out one of my favorites by Tom Kyte from 2008).
In the past week or so, I was asked to look at what initially appeared to be a simple SQL script that was intended to be used to do some database maintenance related to application changes being made in an upcoming release. It needed to insert about 1.5 million rows into a table and was taking approximately 30 minutes. The script contained a small PL/SQL code block that inserted rows into a table something like this:
BEGIN FOR REC IN (SELECT A.col FROM A, B, C, D, E, F WHERE <joins and filter conditions>) LOOP INSERT INTO X (col1, col2, col3, col4, col5, col6, col7) VALUES (SEQ_GET_NEXT.NEXTVAL,'ABCDEFG',REC.col, 'XXXXXXXXX','YYYYYYYYY','ZZZZZZZZZ','PPPPPPPPP'); END LOOP;END;/
As you might imagine, my first thought was to get rid of the PL/SQL and stop doing the INSERTs row-by-row and just do a single INSERT like this:
Easy, right? Well, the time dropped by about half, down to near 16 minutes, but 16 minutes to insert 1.5 million rows. But, if I created my own test tables and inserted the rows in my table, the time was under 5 minutes. Huh?
Guess where the time was going? Trigger code. It took me longer than I'd like to admit for that realization to hit me, but eventually it did. The trigger code has all manner of logic in it. It not only writes out rows to audit tables, but also queues up transactions for additional processes to consume and more. As it turned out, some of that logic didn't need to be applied to this mass maintenance insert, but some of it did. But, since the logic that was extraneous for this effort was embedded along side other logic that was still needed, the whole kit-n-kaboodle had to execute. The window of time set aside to do this bit of maintenance was limited and this insert I'm discussing was only one of several that needed to be accomplished in the specified timeframe. In the end, all the work got done, but just barely...and trigger code execution was the highest consumer of response time overall.
I am a big proponent of using the right tool for the job. But, unfortunately (as Tom mentions in his 2008 article), triggers are often a misused tool. So, I just wanted to tee up something for you to keep in mind the next time you, or a developer you know, starts to think about implementing some logic in a trigger. Even when it may not prove to be an issue at first glance, you never know when it may bite you down the road.