Using Microsoft SQL Server triggers

Intro | Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

Sign in for existing members

Continue Reading This Article

Enjoy this article as well as all of our content, including E-Guides, news, tips and more.

Step 2 of 2:

• You forgot to provide an Email Address.

  This email address doesn’t appear to be valid.

  This email address is already registered. Please login.

  You have exceeded the maximum character limit.

  Please provide a Corporate E-mail Address.

By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.

You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.

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 use triggers in SQL Server? What are the implications? Will database triggers affect SQL Server performance and by how much? In this tip, I pinpoint some considerations for using triggers in SQL Server.

Explore Microsoft SQL Server triggers

• Home Introduction
• Part 1 Why use triggers in SQL Server?
• Part 2 Designing and implementing triggers in SQL Server
• Part 3 Example: INSTEAD OF triggers vs. AFTER triggers
• Part 4 Example: One trigger vs. two for the same logic
• Part 5 Example: Nested triggers in SQL Server
• Part 6 Example: Heavy insertion process from OLTP and triggers
• Part 7 Example: Cursors in SQL Server triggers

ABOUT THE AUTHOR:

Michelle Gutzait works as a team member of the SQL Server Database Experts at www.pythian.com, a worldwide company providing Remote Administration services for databases, operating systems, SANs and networks. 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 SQL infrastructure and database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS/SSIS packages, administrative and infrastructure tools development, reporting services and more.