DML and DDL Triggers in in Microsoft SQL Server 2008

  • 11/12/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.

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.