DML and DDL Triggers in in Microsoft SQL Server 2008

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.

The generic syntax for creating a trigger is:

CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

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

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

   CREATE TRIGGER tiud_orderdetail ON Orders.OrderDetail
   FOR INSERT, UPDATE, DELETE
   AS
   
   UPDATE a
   SET a.FinalShipDate = c.FinalShipDate
   FROM Orders.OrderHeader a INNER JOIN
       (SELECT od1.OrderID, MAX(od1.ShipDate) FinalShipDate
       FROM Orders.OrderDetail od1 INNER JOIN
           (SELECT od2.OrderID
           FROM Orders.OrderDetail od2 INNER JOIN inserted i ON od2.OrderID = i.OrderID
           WHERE od2.ShipDate IS NOT NULL
           EXCEPT
           SELECT od3.OrderID
           FROM Orders.OrderDetail od3 INNER JOIN inserted i ON od3.OrderID = i.OrderID
           WHERE od3.ShipDate IS NULL) b
       ON od1.OrderID = b.OrderID
       GROUP BY od1.OrderID) c
   ON a.OrderID = c.OrderID
   GO

2. Validate your newly created trigger by setting the ShipDate column for all order detail rows for an order.

In the following exercise, you will create a DML trigger that enforces referential integrity between the SQL2008SBS and SQL2008SBSFS databases.

Create a DML Trigger

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

   USE SQL2008SBSFS
   GO
   
   CREATE TRIGGER tiu_productdocuments ON Products.ProductDocument
   FOR INSERT, UPDATE
   AS
   IF EXISTS (SELECT 1 FROM SQL2008SBS.Products.Product a
               INNER JOIN inserted b ON a.ProductID = b.ProductID)
   BEGIN
       RETURN
   END
   ELSE
   BEGIN
       ROLLBACK TRANSACTION
       RAISERROR('Violation of foreign key',16,1)
   END
   GO
   
   USE SQL2008SBS
   GO
   
   CREATE TRIGGER td_product ON Products.Product
   FOR DELETE
   AS
   IF EXISTS (SELECT 1 FROM SQL2008SBSFS.Products.ProductDocument a
               INNER JOIN deleted b ON a.ProductID = b.ProductID)
   BEGIN
       ROLLBACK TRANSACTION
       RAISERROR('You must first delete all documents for this product',16,1)
   END
   ELSE
   BEGIN
       RETURN
   END
   GO

2. Validate your newly created trigger by attempting to insert a document with a ProductID that does not exist.