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 12345»»»

Are Triggers a "legacy" Feature? (Database Weekly, Nov 08 2008) Expand / Collapse
Author
Message
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, April 16, 2014 9:53 AM
Points: 569, Visits: 985
Comments posted to this topic are about the item Are Triggers a "legacy" Feature? (Database Weekly, Nov 08 2008)
Post #599262
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: Tuesday, November 12, 2013 5:53 PM
Points: 7, Visits: 7
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
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: Friday, January 03, 2014 4:35 PM
Points: 237, Visits: 237
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
Posted Saturday, November 08, 2008 6:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
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
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
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, April 16, 2014 9:53 AM
Points: 569, Visits: 985
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
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, August 28, 2013 1:49 PM
Points: 35, Visits: 315
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
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: Tuesday, December 17, 2013 12:14 AM
Points: 1, Visits: 80
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
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: Today @ 3:43 PM
Points: 358, Visits: 1,932
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
Posted Sunday, November 09, 2008 1:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 19, 2013 4:56 PM
Points: 50, Visits: 411
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 12345»»»

Permissions Expand / Collapse