Triggers Usage

[manmaria]

I have a realtime database application and we are using lots of triggers. We are planning to convert the data into sql server 2008. Can I still use these triggers? or any alternatives?

TIA

 

[bborissov]

Sure, you could use them.
The triggers are one of the most important thinks for Database Referential Integrity

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SQLSister]

Of course it may depend on what the code in your triggers is doing. I'm sure though that most, if not all, should work. However, I would test and use the opportunity to review and improve performance of the triggers such as getting rid of any cursors and making sure all triggers can correctly handle multiple row inserts. Triggers can be a real performance hog if done incorrectly, as long as you have to test all of them anyway to make sure nothing is broken, you should take the extra time to make sure they are the best triggers they can be. Some might even benefit from some of the new features available in 2008.

"NOTHING is more important in a database than integrity." ESquared

 

[bborissov]

Grrrr,
Cursors in a TRIGGER????
Grrrrrrrrrrr!
(more Grrrrr!)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[manmaria]

How about the performance using the triggers? We have triggers 3 times more than the number of tables. i.e Almost 3 triggers per table on average.
TIA

 

[manmaria]

Sorry, I was writing the response and in between attended few meetings without look into the other responses. So triggers might be a performance hog if we have cursors in the trigger defn.
TIA

 

[bborissov]

You should test it.
Generally, trigger slow down the INSERT/DELETE/UPDATE (depends for what that trigger is defined).
BUT they save you a lot much time when you need to fix the errors in Database after some incorrect records in your table.
As I said the triggers are designed to keep your database integrity. And that is much more important than some milliseconds loss when you need to INSERT/UPDATE/DELETE record. You don't ask about indexes, right? The indexes also slow down INSERT/UPDATE/DELETE operations esp. if they are clustered.
But I am with SQLSister here, rethink all of triggers.
Remove ALL unnecessary ones, try to optimize them.
Run their code in SSMS (just replace INSERTED and DELETED tables with some Table variables) and check the execution plans.



Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SQLSister]

Almost for sure cursors are a performace hog if you use them in a trigger. We replaced one and reduced the time to insert 40,000 records from 40+ minutes to around 40 secs. Triggers are often written without really thinking through the database implications (for instance in our case, the web programmers wrote the trigger never realizing there were people who might insert 40,000 records at once) and then left in place for years without review.

Email notifications in a trigger are usually a bad idea (do you really want to hold a lock onthe table while the app creates and sends an email? better to send to a table and have a job that runs every five minutes or so).

Going to a new version is a perfect time to refactor.

If you have that many triggers, you may be doing auditing. Consider if there are newer, better ways in SQL Server 2008 to do that or if what you have right now is sufficient. Another cool thing is you can create triggers now that will audit who made schema changes. If you are heavy into auditing, this too might be something you want to implement.

"NOTHING is more important in a database than integrity." ESquared