The Trouble with Triggers, Part II

The Trouble with Triggers, Part II

et’s talk about when and why we use triggers and the technical details of how to write a trigger.

To recap, a trigger is a stored piece of code that the database executes in response to the modification of data. People who program front-ends are familiar with the concept of “event driven” programming. A trigger is the database version of it. Three examples of when a trigger would be useful include: to implement “cascading updates,” create audit trails, or maintain summary tables.

Triggers can also be used to enforce rules that other referential integrity constructs cannot implement. A check constraint can only refer to columns of the same row. However, it cannot check values of other rows, tables, or even other databases. So, for example, one cannot in a check constraint ensure that the salary field of the employee table is less than the value of the maxsalary field in the department table. However, one can easily code this in a trigger. Similarly, in a trigger one can even obtain the max salary value from another database or, for example, compute it by obtaining the average salary for the department and adding 15% (this is an example of a rule based on other rows of the same table).

A trigger can also enforce a rule based on the previous value of the field. A rule such as “the new salary can be at most 15% above the old salary” cannot be implemented with any constraint types. As we shall see when we demonstrate how to code triggers, a trigger allows you to view both the old and new values of the data being modified.

Although a trigger can enforce business rules, one should realize that not everyone agrees about where to place business rules. In a three-tier approach (presentation, business rules, data), the business rules would typically not be implemented at the database level but in the middle tier. In addition, in many instances the examples given will be coded using a stored procedure. Instead of coding a trigger that creates an audit trail, the procedure that modifies the data will contain the code to maintain the audit trail.

The benefit of a trigger is most relevant in situations in a less controlled environment where the data is being accessed through many methods and front-ends. Code in a trigger will always be executed by the database when the data is modified. In a less than perfect world, a stored procedure might be avoided by the user coding the modification directly instead of executing the stored procedure designed for the task. (How many of you have “power users” who insist on full access so they can do their “own thing” in Access?)

Let’s now move on to coding a trigger.

Remember that when a trigger is executed, it is after the modification has taken place and referential integrity (primary key, foreign key, etc) has been checked, but before the transaction is committed.

While the trigger is executing, SQL Server makes two tables available. The structure of both tables (for example, the columns and data types) are exactly the same as the table being modified. The inserted table contains the values of the data being modified after the modification. The deleted table contains the values of the data before the modification. Following is a table that illustrates the role each table plays according to the event being handled.

Action Inserted Deleted
Insert Rows that were inserted Not used
Delete Not used Rows that were deleted
Update New values of rows that were modified Old values of rows that were modified

To gain a better understanding of how the tables are used, run the following little script from isql.

create table test_trigger(	empname char(30),	salary int)gocreate trigger tr_test_trigger  on test_trigger for insert,update,delete as	select * from inserted	select * from deletedgo--then insert some rows ..insert into test_trigger(empname,salary) values ("John Doe", 25000)insert into test_trigger(empname,salary) values ("Fred Flinstone", 30000)-- then update some rowsupdate test_triggerset salary = salary + 5000-- then delete some rowsdelete from test_trigger

After each modification, two result sets will be returned. The first will be the contents of the inserted table, and the second will be the contents of the deleted table.

In the next segment, I will show you how to write some of the examples we’ve talked about and will demonstrate an easy method of writing and debugging them. Lastly, we’ll discuss the enhancements in SQL Server version 7.

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

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