Working with Triggers and Transactions pdf

Working with

Triggers and

Transactions

Chapter 8

In a relational database, data in a table is related to other

tables. Therefore, while manipulating data in one table, you

need to verify and validate its effect on data in the related

tables. In addition, you might need to manipulate data in a

table after inserting or updating data in another table. You

also need to ensure that if an error occurs while updating the

data in a table, the changes are reverted. This helps in

maintaining data integrity. The SQL Server allows you to

implement triggers and transactions to maintain data

integrity.

This chapter explains different types of triggers that can be

created in SQL Server 2005. Next, it discusses how to

implement triggers to enforce data integrity. Further, it

explains how to implement transactions.

In this chapter, you will learn to:

 Implement triggers

 Implement transactions

Objectives

¤NIIT Working with Triggers and Transactions 8.3

At times, while performing data manipulation on a database object, you might also need

to perform another manipulation on another object. For example, in an organization, the

employees use the Online Leave Approval system to apply for leaves. When an employee

applies for a leave, the leave details are stored in the LeaveDetails table. In addition, a

new record is added to the LeavesForApproval table. When the supervisors log on to the

system, all the leaves pending for their approval are retrieved from the

LeavesForApproval table and displayed to them.

To perform such operations, the SQL Server allows you to implement triggers. A trigger

is a block of code that constitutes a set of T-SQL statements activated in response to

certain actions, such as insert or delete. Triggers are used to ensure data integrity before or

after performing data manipulations. Before you implement a trigger, it is important to

know the different types of triggers that can be created by using SQL Server 2005.

In the SQL Server, various kinds of triggers can be used for different types of data

manipulation operations. The SQL Server supports the following types of triggers:

„ Data Modification Language (DML) triggers

„ Data Definition Language (DDL) triggers

DML Triggers

A DML trigger is fired when data in the underlying table is affected by DML statements,

such as INSERT, UPDATE, or DELETE. These triggers help in maintaining consistent,

reliable, and correct data in tables. They enable the performance of complex actions and

cascade these actions to other dependent tables. Cascading is the process of reflecting the

changes made in a table in the other related tables.

The DML triggers have the following characteristics:

„ Fired automatically by the SQL Server whenever any data modification statement is

issued.

„ Cannot be explicitly invoked or executed, as in the case of the stored procedures.

„ Prevents incorrect, unauthorized, and inconsistent changes in data.

„ Cannot return data to the user.

„ Can be nested up to 32 levels. The nesting of triggers occurs when a trigger performs

an action that initiates another trigger.

Implementing Triggers

Identifying Types of Triggers