TechNet
Export (0) Print
Expand All

Create DML Triggers

 

Applies To: SQL Server 2016

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.

    ParameterValue
    AuthorYour name
    Create DateToday's date
    DescriptionChecks the vendor credit rating before allowing a new purchase order with the vendor to be inserted.
    Schema_NamePurchasing
    Trigger_NameNewPODetail2
    Table_NamePurchaseOrderDetail
    Data_Modification_StatementRemove 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.

Before You Begin

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;  
    
    

Community Additions

ADD
Show:
© 2016 Microsoft