Click here to monitor SSC
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
Tony Davis
Tony Davis
Mr or Mrs. 500
Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)

Group: Administrators
Points: 591 Visits: 1148
Comments posted to this topic are about the item Are Triggers a "legacy" Feature? (Database Weekly, Nov 08 2008)
Bert Scalzo
Bert Scalzo
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 9
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.
Chris Denker-441894
Chris Denker-441894
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 287
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!
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44388
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, MVP, M.Sc (Comp Sci)
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


justink
justink
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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.
Tony Davis
Tony Davis
Mr or Mrs. 500
Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)

Group: Administrators
Points: 591 Visits: 1148
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.

Sean Terry
Sean Terry
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 347
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.
Ádám Péter
Ádám Péter
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 96
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.
Ross McMicken
Ross McMicken
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 2195
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.
Nathan Chapman
Nathan Chapman
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

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