Export (0) Print
Expand All

Create DML Triggers

This topic describes how to create a Transact-SQL DML trigger by using SQL Server Management Studio and by using the Transact-SQL CREATE TRIGGER statement.

Limitations and Restrictions

For a list of limitations and restrictions related to creating DML triggers, see CREATE TRIGGER (Transact-SQL).

Permissions

Requires ALTER permission on the table or view on which the trigger is being created.

You can use one of the following:

Using SQL Server Management Studio

  1. In Object Explorer, connect to an instance of Database Engine and then expand that instance.

  2. Expand Databases, expand the AdventureWorks2012 database, expand Tables and then expand the table Purchasing.PurchaseOrderHeader.

  3. Right-click Triggers, and then select New Trigger.

  4. On the Query menu, click Specify Values for Template Parameters. Alternatively, you can press (Ctrl-Shift-M) to open the Specify Values for Template Parameters dialog box.

  5. In the Specify Values for Template Parameters dialog box, enter the following values for the parameters shown.

    Parameter

    Value

    Author

    Your name

    Create Date

    Today's date

    Description

    Checks the vendor credit rating before allowing a new purchase order with the vendor to be inserted.

    Schema_Name

    Purchasing

    Trigger_Name

    NewPODetail2

    Table_Name

    PurchaseOrderDetail

    Data_Modification_Statement

    Remove UPDATE and DELETE from the list.

  6. Click OK.

  7. In the Query Editor, replace the comment -- Insert statements for trigger here with the following statement:

    IF @@ROWCOUNT = 1
    BEGIN
       UPDATE Purchasing.PurchaseOrderHeader
       SET SubTotal = SubTotal + LineTotal
       FROM inserted
       WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID
    
    END
    ELSE
    BEGIN
          UPDATE Purchasing.PurchaseOrderHeader
       SET SubTotal = SubTotal + 
          (SELECT SUM(LineTotal)
          FROM inserted
          WHERE PurchaseOrderHeader.PurchaseOrderID
           = inserted.PurchaseOrderID)
       WHERE PurchaseOrderHeader.PurchaseOrderID IN
          (SELECT PurchaseOrderID FROM inserted)
    END;
    
  8. To verify the syntax is valid, on the Query menu, click Parse. If an error message is returned, compare the statement with the information above and correct as needed and repeat this step.

  9. To create the DML trigger, from the Query menu, click Execute. The DML trigger is created as an object in the database.

  10. To see the DML trigger listed in Object Explorer, right-click Triggers and select Refresh.

[Top]

Using Transact-SQL

  1. In Object Explorer, connect to an instance of Database Engine and then expand that instance.

  2. From the File menu, click New Query.

  3. Copy and paste the following example into the query window and click Execute. This example creates the same stored DML trigger as above.

    -- Trigger valid for multirow and single row inserts
    -- and optimal for single row inserts.
    USE AdventureWorks2012;
    GO
    CREATE TRIGGER NewPODetail3
    ON Purchasing.PurchaseOrderDetail
    FOR INSERT AS
    IF @@ROWCOUNT = 1
    BEGIN
       UPDATE Purchasing.PurchaseOrderHeader
       SET SubTotal = SubTotal + LineTotal
       FROM inserted
       WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID
    
    END
    ELSE
    BEGIN
          UPDATE Purchasing.PurchaseOrderHeader
       SET SubTotal = SubTotal + 
          (SELECT SUM(LineTotal)
          FROM inserted
          WHERE PurchaseOrderHeader.PurchaseOrderID
           = inserted.PurchaseOrderID)
       WHERE PurchaseOrderHeader.PurchaseOrderID IN
          (SELECT PurchaseOrderID FROM inserted)
    END;
    
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft