SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Are Triggers a "legacy" Feature? (Database Weekly, Nov 08 2008)


Are Triggers a "legacy" Feature? (Database Weekly, Nov 08 2008)

Author
Message
Nadrek
Nadrek
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4484 Visits: 2741
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.
RSheahan
RSheahan
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 17
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?
william.sisson
william.sisson
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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.
GeorgeCopeland
GeorgeCopeland
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1921 Visits: 893
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.
GeorgeCopeland
GeorgeCopeland
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1921 Visits: 893
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.
TravisDBA
TravisDBA
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3306 Visits: 3069
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"
Eric M Russell
Eric M Russell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28692 Visits: 11495
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Miles Neale
Miles Neale
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4218 Visits: 1695
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!
Eric M Russell
Eric M Russell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28692 Visits: 11495
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
jfogel
jfogel
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1005 Visits: 1176
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search