November 10, 2008 at 8:38 am
Triggers may have uses, but they are verboten in my shop because of the maintenance difficulties you mention. We are a small dev shop with no DBA. Somebody mentioned data-specific uses such as auditing. If we had those requirements we would use a third party tool.
As for the "dumb database" issue, I am certainly an advocate, especially for a dev team such as ours. There are many whiz-bang advanced features of SQL Server, but I do not consider anyone in my company competent to implement or support them. I also agree that systems where the business logic is in the middle tier make them more scalable, but I also think it makes them more maintainable. I have worked on both kinds, and dumb database systems are less atomic and easier to understand.
However, I also believe that dumb databases should have lots of standard and normal constraints. Databases without key and foreign key constraints are not dumb databases, they are stupid databases
November 10, 2008 at 10:02 am
Legacy? No way. Without triggers some features in our software would not be possible. I like and use triggers a lot. Where necessary, of course.
Otherwise users must not have access to tables and all updates must be performed with stored procedures, or use a midware that does the job, both are IMHO maintenance nightmare.
Dumb databases? I hate that. It's all in advertising and marketing muscle. Some systems use database as storage only. No constraints, no procedures, no views, no triggers. This is silly. And they claim 100% portability to any sql server. You don't an sql server at all for such purpose. Constraints are not fully portable, so they're not used.
Most of people that hate triggers, hate them for at least one of few reasons:
1. they don't know how to properly use, design and maintain database logic
2. they inherited a nightmare of a database from someone who doesn't know how to properly use, design and maintain database logic 🙂
3. hmmm
The problem with triggers is that they have vendor specific features and syntax, so if you work with multiple servers, you have to keep knowledge about each separately, but the same problem is with stored procedures too.
November 10, 2008 at 11:35 am
Dumb databases? I hate that. It's all in advertising and marketing muscle. Some systems use database as storage only. No constraints, no procedures, no views, no triggers. This is silly. And they claim 100% portability to any sql server. You don't an sql server at all for such purpose. Constraints are not fully portable, so they're not used.
I could not agree with this more. Portability to multiple databases is almost never an advantage for business software. Most customers in most verticles don't care. Most such claims are hype with no real requirements behind them (and they probably don't work very well, either). And no constraints? At all? Once again, this is not dumb database, it is stupid database.
November 10, 2008 at 12:00 pm
I definitely fall on the "Constraint Yourself" side of the argument. I've been designing databases for 15 years now and am always amazed at the number of people in this business who profess to use Relational Databases but are vehemently opposed to constraints. Constraints are an integral part of what a RDBMS is---and that includes column constraints, table constraints, null constraints, domain constraints, PK constraints, unique constraints, FK constraints AND TRIGGERS. Triggers are part of how an RDBMS enforces data integrity; they are not there to perform business processes.
Support for stored procedures and triggers that implement business process are features that DBMS vendors include in their products to sell their product, but they are not part of the features and requirements that define what an RDBMS is.
Using an RDBMS without including the appropriate constraints is like a town building a firestation but not buying any trucks or hiring any firefighters and then claiming to have a Fire Department. All you've got is a shell with potential that is not being used and cannot possibly be expected to perform the function it was designed for.
We had "dumb databases" in the 60's. That is one of the main reasons that Codd theorized and defined the RDBMS.
November 10, 2008 at 12:19 pm
With 2005 and 2008, they aren't necessarily required for auditing. Actually you could argue that Profiler/trace means they weren't needed previously either. They do many auditing easier in many cases, but they aren't needed.
I rarely use triggers. It's too easy to think that something ALWAYS needs to happen when it mostly needs to happen. I think that you should use triggers when you absolutely need to make sure some data modification needs to force an action INSIDE the transaction. When you can't be sure an application will do this.
Sending emails, alerting, other things aren't necessarily the items for triggers. Business logic not usually unless it's core to the data, not the process.
November 10, 2008 at 2:12 pm
I agree that triggers tend to have a very limited practical use for many of the reasons already specified. I have seen too many performance issues caused by triggers updating columns that are indexed as well. But many of us have all inherited problem databases which I think Tony was stating at the beginning of his article. Bottom line, stupid triggers work as designed -- stupidly.
In regards to 'dumb databases' and portability, I'll offer a different point of view. If you are the software vendor and you want your application to work on SQL Server and Oracle, and any other RDBMS you would need to design and code it to be portable. You might choose to skip constraints to stay away from vendor specific syntax (I know, you hit vendor specific syntax in other places, but humor me). This way your code would be portable to whichever RDBMS you next customer has installed already. In other words, the portability isn't for a given shop that might switch RDBMS', but for a vendor that sells to customer A with SQL Server and customer B with Oracle etc.
After all, if you had a perfect design and implementation there would be no direct access to the tables and all of the stored procedures and user screens would be coded with edit checks to prevent any 'bad' data from getting into the database in the first place. I'm guessing that like me most of you don't live in a perfect world 😀
November 10, 2008 at 2:58 pm
There will always be a need for triggers but it is something that should be used sparingly and only when you truly have no other option.
In our company we do so much custom one off stuff that many times there are tables holding data that never get tools to manage the data. As a result the few times we need to update data it's done by hand using SSMS. It is standard practice for myself to always put an UpdatedDate column on tables I create and then add a trigger on updates to have that column updated. It helps in a small way keep track of the changes so when stuff breaks we have a timeframe for when data was manipulated. It's not a complete audit but better than nothing.
November 11, 2008 at 3:04 am
DBA_Rob (11/10/2008)
I agree that triggers tend to have a very limited practical use for many of the reasons already specified. I have seen too many performance issues caused by triggers updating columns that are indexed as well. But many of us have all inherited problem databases which I think Tony was stating at the beginning of his article. Bottom line, stupid triggers work as designed -- stupidly.
Ok, but this is not problem with triggers, but the problem with poorly written triggers.
In regards to 'dumb databases' and portability, I'll offer a different point of view. If you are the software vendor and you want your application to work on SQL Server and Oracle, and any other RDBMS you would need to design and code it to be portable. You might choose to skip constraints to stay away from vendor specific syntax (I know, you hit vendor specific syntax in other places, but humor me). This way your code would be portable to whichever RDBMS you next customer has installed already. In other words, the portability isn't for a given shop that might switch RDBMS', but for a vendor that sells to customer A with SQL Server and customer B with Oracle etc.
Right and you have performance that sucks.
Our software is portable. We use all needed features of server, like constraints, stored procedures, views, triggers,... The differences between servers are coded in stored procedures, so client works the same with many servers, while utilizing the full power of each.
The downside is that you have to rewrite the sql part for each new server, but it pays off extremely well.
November 12, 2008 at 8:50 am
I agree with Robert. Portability doesn't mean writing the lowest common denominator. The code shouldn't be lifted from one platform and dropped on another. It should be slightly altered to fit the other platform.
Sound like work? It should be. Otherwise, in my opinion, you're ripping off your customers, not providing them a quality job.
February 19, 2013 at 12:32 am
There is much more to constraint handling than referential integrity constraints and at present triggers are the most viable way to implement these kinds of constraints.
For example for hotel room bookings you need to ensure that the same room cannot be booked for overlapping time periods. You need a constraint to do this that cannot be enforced using referential integrity or check constraints.
The advantage of using a trigger to implement this constraint is that the constraint will be enforced however you update the database, either from an application, from a batch operation or from an adhoc update of the data from SQL.
I would agree that you should use the built in integrity constraints in the database whenever possible but there are a lot of constraints that simply cannot be defined using the built in declarative methods.
Triggers have their problems of course and it would be of enormous benefit to everyone if Microsoft were to implement the CREATE ASSERTION statement (in the SQL standard since 92) so that all constraints could be implemented declaratively.
February 19, 2013 at 1:12 am
I mainly use triggers to modify the behavior of third-party db and to alert users by e-mail that some value has been set. e.g. changing terms of payment is permitted, but users should know it.
February 19, 2013 at 2:54 am
Another Like/Thumbs up for the use of auditing with triggers. We also find them incredibly useful for sending emails as soon as a record is inserted into a particular table. We run a lot of campaigns and it's very useful for the client to know as soon as someone has registered (providing some basic info from the said record) rather than logging into a system, especially as these emails generally go straight to someone's phone.
February 19, 2013 at 3:00 am
Steve Jones - SSC Editor (11/12/2008)
I agree with Robert. Portability doesn't mean writing the lowest common denominator. The code shouldn't be lifted from one platform and dropped on another. It should be slightly altered to fit the other platform.Sound like work? It should be. Otherwise, in my opinion, you're ripping off your customers, not providing them a quality job.
Trigger implementations are very different in different SQL DBMS implementations and hard to port.
This is one of the arguments used for putting logic in the middle tier.
It is also a strong argument for urging Microsoft (and other SQL DBMS vendors) to implement CREATE ASSERTION. Constraints would then be standard across all SQL platforms, much safer than in application code in the middle tier and much easier to implement than in triggers.
The vendors claim there is no demand for assertions, but how many people have even heard of them?
February 19, 2013 at 4:19 am
Triggers are downright unavoidable for some comon problems. Unless you wrap all database actions in an interface represented by procedures (DB or otherwise) and consider the DB just dumb storage without full referential integrity of course. But then you cannot fully leverage the flexibility a database offers.
Where I used triggers in the recent past:
Table A has two foreign key relations to table B, both you want to have “set null” or “set default” cascading behavior.
With DRI you cannot, you have to choose one foreign key relation, SQL server won’t let you have two.
There is no nice way around this, but to introduce an instead-of trigger and do everything manually.
With a trigger all normal database manipulation actions are still working as before, which compared to wrapping access up in stored procedures is superior.
Instead of insert triggers have a slight but dumb issue in that for an insert to happen you have to specify each and every column...even the nullable ones and ones with defaults!
Table A contains data that is entered once a month, and Table B has closely related data that has to be entered daily.
Exports and imports work on the day resolution and record modification dates and record existence have to be kept in synch.
Entering a data in a month record needs to introduce or update all the related day records too, or else the export can’t work.
The reverse is also needed, a day record insert/update must create a create/update the corresponding month record as well.
DRI cannot do correlated inserts, nor correlated updates, these have to be programmed explicitly in either trigger or procedure. The issue I have with procedures in general is that you dispense with the normal abilities of the database and have to route all access through explicitly coded procedures. If you want something simple, odds are it is not in one of the procedures and you have to code even more! It’s like going back to a stone age of development with all the extra complexities!
Unfortunately the existence of and need for triggers seem to be forgotten by the SQL developer team, these last 7 years!
A case in point for this argument is that the output clause in an insert/update cannot be streamed back to the client directly if a trigger exists on the table being modified. You can use the output clause to stream to a table and then do a select on that table afterwards however. But then you lost all the elegancy of the construct. There are more common shortcomings with the output clause, but that is for another day.
Another case is cascading foreign constrains conflicting with instead-of triggers.
This lack of transparent functioning of these new features with the absolutely still needed triggers is making use of these new features a serious problem as you cannot use any without knowing in full advance what is used now and needed into the future.
Adding s trigger to solve a problem for an application that sometimes uses the output clause that you don’t know about, will break the application even when you did nothing wrong and your added code is working properly!
The reverse is also very true, making these following features tainted and generally unavailable if you did not design and implement database and application from the ground up yourself:
•DRI (cascading behavior part)
•Triggers
•Output clause on insert/update that steams not to a table
If you abstain from using triggers, then you cannot implement RI properly, even for some very common cases/needs!
If you ask me, this is a big mess and Microsoft needs to get their functional design in order and real quick too!
They have been introducing broken features for 3 releases now, with no end in sight! Given the slow pace of change in database feature usage, they messed up their product for years to come. The only saving grace seems to be the very slow adoption of new features in the field and the willingness of DBAs to spend days, even weeks, on kludge workarounds for even the simplest of problems.
My current MO is to avoid using both triggers and procedures as much as possible. Where I work this is possible because we control both the applications and the database from the ground up, others are likely much more constraint! Still, this sometimes means I have to lessen RI more then i wish for. The upside is I do not have to feel so constraint in using new SQL constructs.
I hate lessening on RI and this is the reason I judge hard. Somone as Microsoft responsible for QA really needs to wake up hard!
February 19, 2013 at 4:33 am
Carlo Romagnano (2/19/2013)
I mainly use triggers to modify the behavior of third-party db and to alert users by e-mail that some value has been set. e.g. changing terms of payment is permitted, but users should know it.
Since 2005 you cannot do this anymore, unless you know for 100% sure the application never will use the output clause in a conflicing manner! See my previous post as to what this issue exactly is.
Viewing 15 posts - 16 through 30 (of 67 total)
You must be logged in to reply to this topic. Login to reply