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.

devx-admin

devx-admin

Share the Post:
USA Companies

Top Software Development Companies in USA

Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies

Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in

India Web Development

Top Web Development Companies in India

In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India,

USA Web Development

Top Web Development Companies in USA

Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner

Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor

USA Companies

Top Software Development Companies in USA

Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies in the USA. Through a

Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in and explore the leaders in

India Web Development

Top Web Development Companies in India

In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India, and kickstart your journey to

USA Web Development

Top Web Development Companies in USA

Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner for your online project. Your

Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the state. A Senate committee meeting

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor supply chain and enhance its

Revolutionized Low-Code

You Should Use Low-Code Platforms for Apps

As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with minimal coding. These platforms not

Cybersecurity Strategy

Five Powerful Strategies to Bolster Your Cybersecurity

In today’s increasingly digital landscape, businesses of all sizes must prioritize cyber security measures to defend against potential dangers. Cyber security professionals suggest five simple technological strategies to help companies

Global Layoffs

Tech Layoffs Are Getting Worse Globally

Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019 tech firms, as per data

Huawei Electric Dazzle

Huawei Dazzles with Electric Vehicles and Wireless Earbuds

During a prominent unveiling event, Huawei, the Chinese telecommunications powerhouse, kept quiet about its enigmatic new 5G phone and alleged cutting-edge chip development. Instead, Huawei astounded the audience by presenting

Cybersecurity Banking Revolution

Digital Banking Needs Cybersecurity

The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to customers around the world. Rising

FinTech Leadership

Terry Clune’s Fintech Empire

Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created eight fintech firms, attracting renowned

The Role Of AI Within A Web Design Agency?

In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used in design, coding, content writing

Generative AI Revolution

Is Generative AI the Next Internet?

The increasing demand for Generative AI models has led to a surge in its adoption across diverse sectors, with healthcare, automotive, and financial services being among the top beneficiaries. These

Microsoft Laptop

The New Surface Laptop Studio 2 Is Nuts

The Surface Laptop Studio 2 is a dynamic and robust all-in-one laptop designed for creators and professionals alike. It features a 14.4″ touchscreen and a cutting-edge design that is over

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

Cisco Splunk Deal

Cisco Splunk Deal Sparks Tech Acquisition Frenzy

Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the

Iran Drone Expansion

Iran’s Jet-Propelled Drone Reshapes Power Balance

Iran has recently unveiled a jet-propelled variant of its Shahed series drone, marking a significant advancement in the nation’s drone technology. The new drone is poised to reshape the regional

Solar Geoengineering

Did the Overshoot Commission Shoot Down Geoengineering?

The Overshoot Commission has recently released a comprehensive report that discusses the controversial topic of Solar Geoengineering, also known as Solar Radiation Modification (SRM). The Commission’s primary objective is to

Remote Learning

Revolutionizing Remote Learning for Success

School districts are preparing to reveal a substantial technological upgrade designed to significantly improve remote learning experiences for both educators and students amid the ongoing pandemic. This major investment, which

Revolutionary SABERS Transforming

SABERS Batteries Transforming Industries

Scientists John Connell and Yi Lin from NASA’s Solid-state Architecture Batteries for Enhanced Rechargeability and Safety (SABERS) project are working on experimental solid-state battery packs that could dramatically change the

Build a Website

How Much Does It Cost to Build a Website?

Are you wondering how much it costs to build a website? The approximated cost is based on several factors, including which add-ons and platforms you choose. For example, a self-hosted