Create DML triggers

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

This article describes how to create a Transact-SQL Data Manipulation Language (DML) trigger with SQL Server Management Studio, or the Transact-SQL CREATE TRIGGER statement.

The code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

Limitations

For a list of limitations and restrictions related to creating DML triggers, see CREATE TRIGGER.

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 methods:

• SQL Server Management Studio
• Transact-SQL

Use 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, select 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. Select 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, select Parse. If an error message is returned, compare the statement with the previous code block, correct as needed, and repeat this step.

9. To create the DML trigger, from the Query menu, select 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.

Use Transact-SQL

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

2. From the File menu, select New Query.

3. Copy and paste the following example into the query window and select Execute. This example creates the same stored DML trigger as before. The trigger is 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;