The Trouble with Triggers, Part I

The Trouble with Triggers, Part I

hat is a trigger? How does it work? When should one use them, and how does one debug them? Last, what are the enhancements that were made in SQL Server version 7? Let’s explore some of these issues.

What Is a Trigger?
A trigger is a piece of code that SQL Server executes in response to a data modification statement; that is, an insert, update, or delete. The statement to create a trigger is:

  CREATE TRIGGER [owner.]trigger_nameON [owner.]table_nameFOR {INSERT, UPDATE, DELETE}[WITH ENCRYPTION]AS sql_statements

Unlike rules or defaults that can be attached to more than one column by using sp_bindrule or sp_bindefault, a trigger can only be attached to one table.

Each table can only have one trigger for each of the three possible events (insert, update, or delete); that is, you can’t have two triggers on a table responsible for handling a delete. A single trigger can handle from one to all of the events. In all, a table can have as few as zero triggers or as many as three.

When Is a Trigger Executed?
A trigger is implicitly part of the transaction that causes the data modification. However, SQL Server executes the trigger only after the table constraints have been checked.

Why Would I Want to Use One?
One of the most important uses for triggers used to be to enforce referential integrity. Correct me if I’m wrong, but SQL Server version 4.2 did not support referential integrity constraints such as foreign keys. Instead, you could create a trigger that, upon the insert of a record, looked for the corresponding value of the foreign key in the parent table. If the value wasn’t found, the transaction could be rolled back.

However, even though we now have support for foreign keys, sometimes triggers come in handy. For example, consider a system that uses an account number as the primary key in a table and contains another table that stores the transactions for each account such as debits and credits. Suppose that on occasion the account number must be changed. You can’t change the account number in the parent table (accounts) without first updating the value of the account number in the child table (transactions). A trigger is perfect for this sort of situation. (By the way, this situation is typically referred to as “cascading updates.”) In an update trigger on the account table, you place code that checks for the value that will change and updates all the corresponding records in the transaction table.

Similarly, a trigger is handy for keeping summary tables up to date. Continuing with our account table as an example, imagine a system that has many transactions coming in throughout the day for various accounts. It is important at any time for the users of this system to know what is the total value of the debits and credits transacted throughout the day. Depending on the number of transactions and the response time required, summarizing the information might take too long from the time when the user requests it. Instead, as each debit or credit is inserted into the transaction table, a trigger can be executed that contains code to update a summary table with the value of the transaction just entered.

Triggers are also useful for keeping audit logs of changes to sensitive data. The same trigger that updates the transaction table with the new value of the account can also insert a record into an audit table with the old account number, new account number, time changed, and the user name of the person that changed it.

In the future I’ll compare triggers to stored procedures and discuss how to code them. Look for examples for each of the situations we’ve discussed.

Share the Post:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as