31 Oct Sql server tutorial 15 Creating SQL Triggers
Key Skills & Concepts
- Understand SQL Triggers
- Create SQL Triggers
- Create Insert Triggers
- Create Update Triggers
- Create Delete Triggers
- Create Instead Of Triggers
Up to this point in the book, you have learned to create a number of schema objects that
you can access or invoke by using SQL statements. For example, you learned how to
create tables, views, and SQL-invoked routines. In each case, once you create these
objects, you need to take some sort of action to interact directly with them, such as
executing a SELECT statement to retrieve data from a table or using a CALL statement to
invoke a procedure. However, SQL supports objects that perform actions automatically.
These schema objects, which are known as triggers, respond to modifications made to
data within a table. If a specified modification is made, the trigger is automatically
inzvoked, or fired, causing an additional action to occur. As a result, you never directly
invoke the trigger—taking an action defined in the trigger implicitly causes the invocation.
In this chapter, we’ll explore triggers and how they’re used when table data is modified.
We’ll also look at examples of how to create the three basic types of triggers—insert,
update, and delete—and how they can be defined to extend your database’s functionality
and help to ensure the integrity of the data.
Understand SQL Triggers
If you’ve worked around any SQL products before, you’ve no doubt seen triggers
implemented in one of your organization’s databases, or at least heard the term tossed
about. Most relational database management systems (RDBMSs) implemented triggers in
their products long ago, although it wasn’t until SQL:1999 that triggers were added to the
standard. The result of the products preceding the standard is that trigger implementations
are highly proprietary among the SQL products, and thus support different types of
functionality and are implemented in different ways. For example, MySQL, DB2, SQL
Server, and Oracle currently support triggers, but SQL Server triggers are somewhat
limited in scope compared to the SQL standard, whereas Oracle triggers are more robust—
yet neither product implements triggers according to the specifications of the SQL
standard. Despite this, there are a number of similarities among the products (such as the
use of a CREATE TRIGGER statement to create a trigger), and the implementations of
triggers in the various products share some basic characteristics, particularly that of being
able to fire automatically to perform an action secondary to the primary action that
invoked the trigger. However, MySQL added support for triggers more recently (starting
with release 5.1), so MySQL triggers closely match the syntax and capability spelled out
in the SQL standard.
The functionality supported by triggers is sometimes referred to as active database. In fact, this term is used to
describe one of the optional packages that are included in the SQL standard. The package—PKG008—defines
how triggers are implemented in SQL. (A package is a set of features to which a product can claim conformance
in addition to Core SQL.) For more information about SQL conformance, see Chapter 1.
Before we get into the specifics of how to implement triggers, let’s take a look at the
trigger itself, which, as I said, is a schema object (in the same sense as a table, view, or
SQL-invoked routine). A trigger definition defines the characteristics of the trigger and
what actions are taken when the trigger is invoked. These actions, which are specified in
one or more SQL statements (referred to as the triggered SQL statements), can include
such events as updating tables, deleting data, invoking procedures, or performing most
tasks that you can perform with SQL statements. Any limitations placed on those
statements are usually the ones placed by the SQL implementation.
Triggers are invoked when you insert data, update data, or delete data. By defining one
or more triggers on a table or view, you can specify which data-modification actions will
cause the trigger to fire. The trigger is never invoked unless the specified action is taken.
SQL supports three types of triggers: insert, update, and delete. As their names suggest,
the insert, update, and delete types correspond with the applicable data modification
statements made against tables. For example, an insert trigger is fired when the INSERT
statement is executed against the specified table. The SQL:2008 standard introduced the
instead of trigger, which is defined on a view rather than a table, along with the specific
action to be taken in place of (instead of) the action defined by the data modification
statement on the view. For example, an instead of trigger is fired when an UPDATE
statement is executed against the specified view, but unlike triggers defined on tables, the
view is not actually updated—the actions in the trigger take the place of the update of the