Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

Are Triggers a "legacy" Feature? (Database Weekly, Nov 08 2008) Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 8:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:48 AM
Points: 861, Visits: 2,359
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.
Post #1421666
Posted Tuesday, February 19, 2013 8:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 7:12 AM
Points: 1, Visits: 13
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?
Post #1421685
Posted Tuesday, February 19, 2013 8:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 1:19 AM
Points: 4, Visits: 42


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.
Post #1421691
Posted Tuesday, February 19, 2013 8:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:42 PM
Points: 165, Visits: 267
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.
Post #1421695
Posted Tuesday, February 19, 2013 8:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:42 PM
Points: 165, Visits: 267
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.
Post #1421697
Posted Tuesday, February 19, 2013 9:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:05 PM
Points: 1,334, Visits: 3,068
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....


"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #1421714
Posted Tuesday, February 19, 2013 10:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:00 AM
Points: 1,595, Visits: 4,585
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.
Post #1421751
Posted Tuesday, February 19, 2013 10:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:18 PM
Points: 2,266, Visits: 1,320
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!
Post #1421755
Posted Tuesday, February 19, 2013 10:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:00 AM
Points: 1,595, Visits: 4,585
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.
Post #1421770
Posted Tuesday, February 19, 2013 11:09 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:43 AM
Points: 371, Visits: 954
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
Post #1421778
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse