Stored Procedure vs Triggers
-
In the conclusion, the author mentions the number of db objects required to support auditing in the two latter methods (900 and 450, respectively for a 150-table db). The implication seems to be that fewer objects = better (performance? maintenance?).
This kind of thinking, however, usually leads to bad db design... "fewer tables must be better, so let's use a single 'lookup' table for all our codes", or "let's denormalize so we don't have to do all those joins".
When making design decisions, be sure you know exactly the performance metrics you're making tradeoffs for. "Number of db objects" should be at or near the bottom of any such list.
TroyK
-
First, I'd like to thank G. Vijayakumar for a very thoughtful analysis and comparison on the operational differences between stored procedures and triggers.
Next, I'd like to remind all DBAs that in today's world (at least in the US), you not only have to consider the maintenance issues surrounding your database design, you have to consider the business issues, specifically compliance.
Having done my share of stored procedure & trigger coding, I can readily empathize with G. Vijayakumar on the difference between maintaining 450 versus 900 db code objects.
However, when I consider the compliance requirements of an audit trail, I have to ask which is the better approach: a stored procedure that modifies both the data table and the audit table, which can be altered at any time by someone who has ALTER PROCEDURE permissions, or a stored procedure which modifies the data table and then triggers a corresponding modification to the audit table, with trigger code that can be secured separately from the stored procedure code.
Let's say that G Vijayakumar's #3 plan was in place -- modify both user data and audit table via the same stored proc. What would happen if someone who wasn't fully aware of the audit requirements were to create a stored procedure that modified the user data, but failed to modify the corresponding audit table? G Vijayakumar's solution #3 depends on use rules -- whoever writes a stored proc that's going to modify user data has to remember to also modify the corresponding audit table(s). That's dangerous, because use rules are always violated -- not intentionally, usually accidentally, but when they're violated, they're violated and you're missing data out of the audit tables. Compliance auditors are not going to be happy about this, and the senior DBA is going to take a hit to his or her reputation, perhaps to the point of not being trusted with corporate data... not a good scenario.
That's my perspective... so I'm leaning towards solution #2, separating stored proc from trigger, so that 1) I can manage the CREATE and ALTER permissions on both, and 2) I can be assured that on ANY modification to the user data there'll be a record written to the audit tables.
Thanks again for a very useful article!
-
If that level of auditing were required, then I would recommend using a program that snifs the actual transaction log. That avoids both the ugliness of triggers and the insecurity of developers with ALTER PROCEDCURE permissions having access to the audit code. Of course, in order to achieve accurate data, users would need direct access to the tables and that creates a host of other security issues. Further, if you cannot trust your DBAs/database developers with ALTER TRIGGER but can trust them with ALTER PROCEDURE, then I think there are other issues at hand.
If you do not want users to have direct access to your tables, then I submit that you can't use triggers as the user affecting the change will not be available and that leaves putting the audit code in the stored procedures.
-
> If you do not want users to have direct access to your tables
> , then I submit that you can't use triggers as the user affecting
> the change will not be available and that leaves putting the audit
> code in the stored procedures.
Actually, I realize I was not clear here. If you have connection pooling amongst users and the users are not using Windows Auth to connect directly to SQL server *then* the user would not be available. If users are using Windows Auth but going through stored procs (and not going through a proxy user like e.g. COM+) then of course the username would be available.
-
IMHO, the count of DB Objects (150 * (3+3) ) does not really matter in a long run.
1. How often anybody changes the SCHEMA for a table, which means Trigger change should be rare too
2. The changes people (Business, Devs) usually make are "Business Logic", in that case making 1 additional object change should be trivial (if schema change involved)
3. As its understood by all smart/good devs, the Object should do exactly what it is supposed to do, which means your INSERT stored procedure do nothing other than INSERT into the TABLE (may be some business logic), and let SQL Server (Trigger in this case) handle the rest.
4. In a long run Number of Objects does not matter if you follow proper naming convention, what matters is DATA management over Object Management
5. What if you want to DISABLE the trigger, with your logic we will have to touch (150 * 3) Object, but having them as seperate Object will let your DISABLE by Switching a FLAG (I believe its Alter Table option)
6. Trigger has their meaning and place in DB world, so I believe it needs to be well though of before you implement
paul -
I see four issues raised in the article and the following discussion:
1. Efficiency: author seems to show that there is no difference (or at worst) small difference in efficiency between using triggers and stored procedures for logging transactions to the audit trail table(s), but perhaps I missed something on that.
2. Security: some people are very concerned about someone else maliciously manipulating their database (banking, eg.). The audit trail task should be completely independent from the insert/edit/delete, and be automatic. In that case triggers seem to be the safest way to have a solid audit trail.
3. Clarity: triggers are hidden from the readers of the code – unless the readers of the code know about the trigger’s existence when they look at the insert/edit/delete procedures, they would not be able to understand how the code works.
4. Maintainability: if multiple insert/edit/delete routines exist and triggers are not used, then each routine must include identical audit-trail code. Changing the audit-trail task requires going back and changing all versions, or else the system gets broken.
For those who do not need to worry about 1 or 2 (like me), then clarity and maintainability are the core issues. The time-tested solution to this is modularity: extract the audit-trail code to its own stored procedure, and include a call to the audit-trail procedure in every insert/edit/delete procedure. This way changes to the audit-trail task occurs in one place only, and changing it shouldn't affect the other code. Similarly, if the insert/edit/delete code is modular, then changes to it need to occur in one place only, even if you have several specialized routines. The cost is efficiency -- the extra overhead of a second procedure call each time -- but if this is not a big concern then the benefit of clarity will outweigh the cost.
- I can't say much about the technical content of this article because I just could not bring myself to read beyond the first couple of paragraphs - the English was that atrocious!I guess there is something to be said about the virtues of content over style, but when the reader has to fathom a guess at what the author is trying to convey every third word or so, then the task becomes an uphill struggle.I know that there is bound to be thoughts (if not yells) of "pedant" out there, but surely I am not the only voice for the correct use of English and clarity in the written word! Or does technical expertise transcend language barriers? Maybe there is a market for proof readers and ghost writers in IT?
-
I applaud the author's efforts to write in a language that isn't his mother tongue. It's come out far more clearly than I would have managed had I been attempting to write in French or Spanish, for example. I agree about the need for the correct use of English and clarity in the written word, but I think we should save our ire for those who should know better!
John
-
Its best to avoid triggers at all costs. We've been having problems with them, especially when related tables each have triggers. Besides performance issues, the triggers weren't written properly. By the way several people have worked on the database. Data would be validated at the presentation layer. Upon saving information the triggers would fire and that would cascade to many tables. At the end you have information that's mixed up. And you don't know where to start debugging.
-
Its best to avoid triggers at all costs. We've been having problems with them, especially when related tables each have triggers. Besides performance issues, the triggers weren't written properly. By the way several people have worked on the database. Data would be validated at the presentation layer. Upon saving information the triggers would fire and that would cascade to many tables. At the end you have information that's mixed up. And you don't know where to start debugging. You just have to be trigger-happy if you want to use triggers. Stay away from them.
-
Hi Vijaya Kumar,
recently i have read your article and it's helpful.And I have small doubt why are you used this,
(This works for Oracle but not MSSQL)
"I have used “Truncate” statement instead of “Delete” because I do not want to rollback my data."
when you Truncating records...B'cos Truncate also can be ROLLBACK...
Thanks
Tharindu Dhaneenja
Tharindu Dhaneenja.
MCTS,MCITP(SQL Server),OCA(Oracle)
http://www.databaseusergroup.com -
Pedrsonally I hate triggers, and avoid them where possible. For audit it is not possible, triggers are essential - how can I have reliable audit data using some method that doesn't automatically record the things that need auditing, some method that relies on the user whose actions I want to audit to call some audit procedure?
Tom
-
Can someone give examples of scenarios when to use a trigger and when to use a stored procedure.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply