DML and DDL Triggers in in Microsoft SQL Server 2008

Learn how to create DML triggers that execute when you add, modify, or remove rows in a table, in this lesson from Microsoft SQL Server 2008 Step by Step. You will also learn how to create DDL triggers that execute when DDL commands are executed or users log in to an instance.

After completing this chapter, you
will be able to

Create DML triggers

Create DDL triggers

Triggers provide a means to allow you to
automatically execute code when an action occurs. Two types of triggers
are available in Microsoft SQL Server 2008: DML and DDL. In this lesson,
you will learn how to create DML triggers that execute when you add,
modify, or remove rows in a table. You will also learn how to create DDL
triggers that execute when DDL commands are executed or users log in to
an instance.

DML Triggers

Although functions and stored procedures are stand-alone
objects, you can’t directly execute a trigger. DML
triggers are created against a table or a view, and are
defined for a specific event—INSERT, UPDATE, or
DELETE. When you execute the event a trigger is
defined for, SQL Server automatically executes the code within the
trigger, also known as “firing” the trigger.

When a trigger is defined as AFTER, the trigger fires after the
modification has passed all constraints. If a modification fails a
constraint check, such as a check, primary key, or foreign key, the
trigger is not executed. AFTER triggers are only defined for tables. You can define multiple AFTER triggers for the same action.

A trigger defined with the INSTEAD OF clause causes the trigger
code to be executed as a replacement for INSERT,
UPDATE, or DELETE. You can define a
single INSTEAD OF trigger for a given action. Although INSTEAD OF
triggers can be created against both tables and views, INSTEAD OF
triggers are almost always created against views.

Regardless of the number of rows that are affected, a trigger
only fires once for an action.

As explained in Chapter 10, “Data Manipulation,” SQL Server
makes a pair of tables named inserted and deleted available when
changes are executed.

In the following exercise, you will create a DML trigger that
populates the FinalShipDate column in the Orders.OrderHeader table
when the ShipDate column has been populated for all rows in the
Orders.OrderDetail table for an OrderID.

Create a DML Trigger

Execute the following code against the SQL2008SBS database
(the code is from the Chapter14\code1.sql file in the book’s
accompanying samples):