 |
|
| SQL Server Tips: |
|
|
|
MICROSOFT SQL SERVER
Optimize database triggers in SQL Server 2000
Michelle Gutzait, Contributor
05.01.2006
Rating: -4.00- (out of 5)
|
One of my clients called me. The frantic communication went something like this:
"Our applications started to do crazy things in the database," said the client. "We don't understand what is happening. New rows are added to one table and another table is changed. We successfully update a row, and when we refresh the application form, different data appears. Also, it takes too long to update a single row. Is it a virus? Can it be that SQL Server has a bug in it?"
I examined their database. Almost every table had at least one trigger attached to it. I tried to follow the logic of only one update, and I was terrified. It took me hours to understand the logic of this "spaghetti" database. At least the "recursive triggers" option was disabled. Imagine what could happen if it wasn't? I would have lost several more hours.
This is the ultimate dilemma: Should you have triggers or should you not? What are the implications? Will triggers affect performance and by how much? In this tip, I pinpoint some considerations for using triggers and specifically focus on performance.
How to optimize database triggers
 Home: Introduction
Part 1: Why use triggers?
Part 2: How to design and implement triggers
Part 3: Example: INSTEAD OF triggers versus AFTER triggers
Part 4: Example: One trigger versus two for the same logic
Part 5: Example: Nested triggers
Part 6: Example: Heavy insertion process from OLTP and triggers
Part 7: Example: Cursors in triggers
| ABOUT THE AUTHOR: |
|
Michelle Gutzait
Michelle Gutzait works as a senior database consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the past 10 years, she has worked exclusively with SQL Server. Her skills include database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, and administrative and infrastructure tools development, reporting services and more.
Copyright 2006 TechTarget |
More from SearchSQLServer.com
Ask the Experts: DDL vs. DML triggers
SQL Server Clinic: T-SQL performance problems and solutions
Topic: Look up additional stored procedures and development tips
|
|
Rate this Tip
|
To rate tips, you must be a member of SearchSQLServer.com.
Register now
to start rating these tips. Log in if you are already a member.
|
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
|
|
|
|
|
|
|
|
|
|
| TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of . |
|
| | |
All Rights Reserved, , TechTarget |
|
|
|
|
|
