February 19, 2013 at 8:13 am
william.sisson (2/19/2013)
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.
1) If you insist on "begin" and "end" as separate columns, what about a function based table constraint, where the function checks for overlapping ranges?
2) Alternately, you move on to the "DateOfChange" technique where you have only the "Begin" type column, and you record "Customer A begin" and then either "Customer B begin" or "Empty begin" in another record. LAG/LEAD made this much easier in 2012, but it can still be done without much trouble in earlier versions.
February 19, 2013 at 8:47 am
I use triggers to make updatable views, which buffer downstream developers from complex schema.
Is there a way better than through triggers to provide this functionality?
February 19, 2013 at 8:50 am
1) If you insist on "begin" and "end" as separate columns, what about a function based table constraint, where the function checks for overlapping ranges?
Because function based table constraints are not set orientated and triggers are. I think you will run into some concurrency problems with check constraints using functions.
February 19, 2013 at 8:57 am
Bert Scalzo (11/8/2008)
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.
Sorry, but that use of triggers is also a legacy one. If you have a requirement for unique keys across tables, then use GUIDs.
February 19, 2013 at 8:59 am
A database should be stitched up tight as a drum with constraints that ensure referential integrity. Otherwise they should be dumb as a stone. And you will put a trigger on my database design over my cold, dead body.
February 19, 2013 at 9:19 am
GilaMonster (11/8/2008)
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.
I agree Gail, I have often found that people are using triggers for things which they probably shouldn't be using them for… Plus, it's too easy for most people to just plain forget or overlook that they are even there. Even though you can find them rather easily....:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
February 19, 2013 at 10:17 am
Last year at PASS I heard one person proudly proclaim that we "like our databases dumb!" He, like many others, wanted to avoid putting any logic in the database in favour of placing it all in a middle-tier layer. This, they argue, leads to a more scalable and portable architecture.
LOL! I spend a lot of time cleaning up "dumb" databases where the middle tier or ETL is assumed to contain all the logic needed for data quality and referential integrity. Those Entity Framework and heterogeneous architecture guys should stick to the .NET conferences where the audience doesn't know any better.
However, I do agree that triggers are most often misused, especially in scenarios where a check constraint would have been a better choice.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 19, 2013 at 10:29 am
Eric M Russell (2/19/2013)Those Entity Framework and heterogeneous architecture guys should stick to the .NET conferences where the audience doesn't know any better.
While I agree that there can be a lot of cleanup in some databases, and that some .net developers using the Entity Framework create systems and databases that are challenging, that does not justify the attitude in the above statement. Such is the attitude that hampers the industry, can kill teamwork and possibly a project, and can make ones career difficult.
Not all gray hairs are Dinosaurs!
February 19, 2013 at 10:53 am
Miles Neale (2/19/2013)
Eric M Russell (2/19/2013)Those Entity Framework and heterogeneous architecture guys should stick to the .NET conferences where the audience doesn't know any better.
While I agree that there can be a lot of cleanup in some databases, and that some .net developers using the Entity Framework create systems and databases that are challenging, that does not justify the attitude in the above statement. Such is the attitude that hampers the industry, can kill teamwork and possibly a project, and can make ones career difficult.
I apologize for the tone. It is technically possible to architect Entity Framework or heterogeneous database applications that perform well and are well maintained. I'm open to the possibility. However, I think there is more to be gained by avoiding them than advocating them.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 19, 2013 at 11:09 am
I despise triggers because far too often they are doing things others are not aware of and they will spend too much time trying to find the cause only to eventually get to the root cause of the problem and that is the trigger. Before I will create a trigger for a business process a convincing case mast be made as to why something like that should happen and 99.9% of the reasons do not make the cut. The only time in recent memory where I found a trigger useful was trying to pin down how a critical value for a single record was changing in a particular table. The users denied it, we couldn't find a cause in the application code so I created a trigger to detect if that record changed and if so send an email alert to the principles involved. It worked great and the best part is that we dropped it when done.
Cheers
February 19, 2013 at 11:50 am
jfogel (2/19/2013)
... because far too often they are doing things others are not aware of ...
But that is the whole point of a trigger!
To implement some feature that works transparent and automatic, on top of and without complicating, all those other things going on! For this you should love triggers that do not have unrelated side-effects. I do agree however to keep usage of triggers to what is absolutely nececary. If a feature can be done declarative or in a procedure where said transparancy is no requirement, go for that instead!
Unfortunatly since 2005 no trigger is guaranteed transparent due to the introduced output clause (bad implementation). Anyone using triggers to audit, log or debug using triggers for an application they do not know in full, might break the application doing so unknowingly!
The horror for me is to see everything (RI and interaction), wrapped in stored procedures as then the database is used as dumb storage (in an SQL way). With the same inflexibility and torrents of code that come with stuffing everything in a middle tier. Need to do some reporting...well, add yet anoter procedure. Need another filter parameter for some listview,...complicate things further in a procedure or add another specialised one. It is the path that never ends to generate more complexity and never leverages the concept of the relational database.
February 19, 2013 at 12:29 pm
I get that but my point is that sometimes you don't want this and if a person is performing an action without knowing or forgetting there is a trigger they run the risk of triggering (pun intended) other processes they had no intention of kicking off such as 10,000 emails were sent because they inserted that many records. Of course things like that are rare but once is enough. Yes, triggers can be useful but in my experience they are trouble when others don't realize they are there.
Cheers
February 19, 2013 at 12:39 pm
Eric M Russell (2/19/2013) I think there is more to be gained by avoiding them than advocating them.
Complete agreement here. I think I have written only a few in my career. For the most part i have done everything I could to avoid them and have been successful. There are better ways of doing most things.
Not all gray hairs are Dinosaurs!
February 19, 2013 at 12:41 pm
If you don't know the trigger is there, it is probably due to poor, missing, or incomplete application/database documentation.
February 19, 2013 at 12:47 pm
You assume people would bother to read such things. We all know everyone and every level reads the docs 🙂
Cheers
Viewing 15 posts - 31 through 45 (of 67 total)
You must be logged in to reply to this topic. Login to reply