November 7, 2008 at 1:26 pm
Comments posted to this topic are about the item Are Triggers a "legacy" Feature? (Database Weekly, Nov 08 2008)
November 8, 2008 at 4:20 am
Triggers do still have one legitimate use that constraints cannot handle. If the logical design includes super and sub-types, and the physical implementation is the one table per child choice - then the only way to guarantee that the primary key remains unique across the tables is via a trigger. Constraints only perform their function within the context of a single table. So a trigger PK check across tables is the only answer.
November 8, 2008 at 6:14 am
I agree with avoiding them by proper design, with one exception: generating audit files; I don't want to trust the application layer to do that.
The other time I've found them indispensible is when dealing with 3rd party software, either to add functionality, or to fix the vendor's mistakes. Just don't tell the vendor, they'll blame the triggers for all their application bugs!
November 8, 2008 at 6:19 am
Triggers have uses, but I'm not sure that business logic is a good one.
For me, auditing is the main use. In 2008 Change data capture and change data tracking can also be used for that, but not everyone is using 2008.
Other thing that they can be used for (especially the DDL triggers) is rolling back unauthorised changes, though if you have that kind of changes been made either security is lax or the DBA is careless.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2008 at 8:48 am
I agree that putting application-specific logic in a trigger is a bad idea and a maintenance nightmare. However, like the previous posters I believe that triggers have great value for data-specific logic (i.e. auditing, etc.). An example of this I like to use is a "LastModified" field on the row. If you rely on an application to update this field, there is a possibility that the system can be subverted by accessing the table directly; using a trigger to update this field helps to avoid this.
November 8, 2008 at 9:18 am
Absolutely -- I do see a use for triggers in auditing.
My argument was based on the use of triggers to implement business logic and to do the sort of data integrity checking that should be enforced by keys and constraints. I should have made that a bit more explicit.
Cheers,
Tony.
November 8, 2008 at 10:32 am
The role of the trigger may certainly be changing, but they're certainly not legacy by any means. They're often the easiest way to audit (I have a DDL trigger that generates audit triggers on new tables in certain schemas), and they're handy to use with a CTE to prevent cycles from being inserted into hierarchies.
Triggers have many uses beyond the annoying stuff that results from their mis-use.
November 8, 2008 at 11:15 am
Triggers can act as mines but mines proved to be useful in history even as first or last lines of defense.
If one put all the checking into a stored procedure then he/she is not protected from others, who
a) begin developing a stored procedure which later/in the beginning does something similar like the first one, but this time the developer forgets about/never knew the business rule so does not implement the data protection
b) thinks that just this time, just in this utility has the right to directly operate on the table data instead of the stored procedures
Triggers can save one's data but should throw meaningful exceptions in order to ease the work of the developers.
All the above about complex checking, not about data altering triggers.
November 9, 2008 at 12:35 am
Triggers aren't always bad, it just depends on how they are used. We use triggers to activate interfaces we've built on vendor provided software - without the trigger, we woldl be dead in the water. We also use them for auditing purposes. I would avoid using triggers for business logic unless there's no other way to achieve the goal.
November 9, 2008 at 1:52 am
I disagree that triggers are outdated. Even with the new functionality in 2008, there are occasions with vendor software where a business requirement simply cannot be met other than to use some form of audit trigger, either because the information cannot be manipulated through the provided API or any other exposed means, because the proprietry language is simply incapable or limited and the alternative would take far longer to develop.
November 9, 2008 at 1:10 pm
The main reason developers want the database to be a "dumb" data store is for portability and costs. If you use all the features of SQL Server, you are "locked in" and on the upgrade path for a long time. Smaller clients may not want to pay the cost or have another DBMS other than SQL Server.
November 9, 2008 at 4:25 pm
I like your article but there is one place where we've found triggers invaluable: Where you have a third-party system (eg Great Plains) that you must integrate with. In that kind of a situation, business rules may call for certain data changes to be "caught" and further processing to be performed at that point. The native stored procs that come with the product may be encrypted (as are some in GP) or you may simply not want to mess with them for fear of ruining your upgrade path. In this situation, triggers make perfect sense. But I agree you wouldn't normally be relying on them.
November 10, 2008 at 1:23 am
They are the only sensible solution for auditing. They cannot easily be bypassed even when modifying data directly with interactive SQL and I would not design a system without auditing using triggers. This type of design has saved us many times.
November 10, 2008 at 1:54 am
As well as being a developer I have a number of bought-in packages to DBA and they all use dumb database designs despite only being available with SQL Server database engine!
This causes slower and more convoluted processing and there are some fine examples of bad design but we have to live with it. It's on these that I have used triggers to monitor e.g. where are blank data fields being added to the table or what is deleting records...
For our own in-house software there's no need - the stored procedures can handle auditing as there is no direct updating.
November 10, 2008 at 6:47 am
The answer to the question, are triggers useful, is as always it depends. I currently work with an application riddled with triggers (some with RBAR) and in an earlier contract I also used triggers. In that application, a user had various permissions and capabilities managed through part of an application and stored in a SQL table. When a user was added her permissions were added (set to off) and when she was deleted from the system all her permissions had to be deleted. When a new page was added permissions for all known users had to be added - all of this was done through triggers.
Also, when a table is maintained from several sources - online via a web site, through a load process, etc, and a dependent table, let's say the inventory record for a product record needs to be added then triggers are the most fail-safe method.
Viewing 15 posts - 1 through 15 (of 67 total)
You must be logged in to reply to this topic. Login to reply