Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQLServerCentral.com » Editorials » Are Triggers a "legacy" Feature?...
Add to briefcase
68 posts, Page 1 of 7
12345»»»

Are Triggers a "legacy" Feature? (Database Weekly, Nov 08 2008) Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Tony Davis
Posted Friday, November 07, 2008 1:26 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Wednesday, May 08, 2013 10:31 AM
Points: 511, Visits: 945
Comments posted to this topic are about the item Are Triggers a "legacy" Feature? (Database Weekly, Nov 08 2008)
Post #599262
Bert Scalzo
Posted Saturday, November 08, 2008 4:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 08, 2008 12:31 PM
Points: 7, Visits: 5
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.
Post #599416
Chris Denker-441894
Posted Saturday, November 08, 2008 6:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 07, 2013 9:00 AM
Points: 236, Visits: 230
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!
Post #599431
GilaMonster
Posted Saturday, November 08, 2008 6:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:50 PM
Points: 37,651, Visits: 29,902
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #599432
justink
Posted Saturday, November 08, 2008 8:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 10, 2008 11:39 AM
Points: 2, Visits: 14
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.
Post #599444
Tony Davis
Posted Saturday, November 08, 2008 9:18 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Wednesday, May 08, 2013 10:31 AM
Points: 511, Visits: 945
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.

Post #599447
Sean Terry
Posted Saturday, November 08, 2008 10:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 3:19 PM
Points: 35, Visits: 311
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.
Post #599459
Ádám Péter
Posted Saturday, November 08, 2008 11:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 5:54 AM
Points: 1, Visits: 74
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.
Post #599463
Ross McMicken
Posted Sunday, November 09, 2008 12:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 12:39 PM
Points: 346, Visits: 1,816
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.
Post #599522
Nathan Chapman
Posted Sunday, November 09, 2008 1:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 5:56 PM
Points: 47, Visits: 379
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.
Post #599533
« Prev Topic | Next Topic »

Add to briefcase
68 posts, Page 1 of 7
12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.