Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
theboyholty
Posting Yak Master
226 Posts |
 Posted - 2011-06-16 : 06:00:09
|
| I've just been learning about triggers in SQL Server 2008 and I can see that they could potentially be quite useful, but I'm also aware that a lof of people have the opinion that the faster you run away from them, the better.So what's so bad about triggers? Or indeed, what is good about them?I'm not trying to start a fight here, I'm just curious.---------------------------------------------------------------------------------http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-06-16 : 06:31:03
|
| They're often written badly.A trigger runs within the transaction that starts when the table was inserted/updated/deleted, so a long-running trigger means long locks and probably perf problems. Also means that if the trigger fails, the operation that fired the trigger rolls backKeep your triggers short. Write them assuming there can be any number of rows in inserted/deleted. Don't use cursors in triggers. Don't try to call out to executables/non-SQL processes. Don't do things in triggers that should have been validation pre-data change. Keep them short.--Gail ShawSQL Server MVP |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-06-16 : 07:57:49
|
| My opinion: The *only* thing that triggers are good for is to maintain audit columns in the table, i.e. making sure Inserted-, Updated-, InsertedBy-, UpdatedBy-columns properly populated. The problem I have with triggers is that things happen in the database "automatically". If you i.e create a trigger to a table today that does some stuff and then in 2 years when you do a manual update to the table because a user messed up some data input suddenly you've changed a whole lot more than you intended to. I prefer that when I do stuff to a table, it does what I write in the insert/update/delete and nothing more. Think about being a new member in the dev team of the system you're making. It would be a pain in the a$$ trying to figure out what's going on if triggers are fired "all over the place"- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
|
|
|
Discussion: What is wrong (or right) with TRIGGERs