Muokkaa

Jaa


Create DML Triggers

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

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.

Before You Begin

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.

How to Create a DML Trigger

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 AdventureWorks2022 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.

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 AdventureWorks2022;  
    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;