Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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.
For a list of limitations and restrictions related to creating DML triggers, see CREATE TRIGGER (Transact-SQL).
Requires ALTER permission on the table or view on which the trigger is being created.
You can use one of the following:
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
Expand Databases, expand the AdventureWorks2022
database, expand Tables and then expand the table Purchasing.PurchaseOrderHeader.
Right-click Triggers, and then select New Trigger.
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.
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. |
Click OK.
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;
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.
To create the DML trigger, from the Query menu, click Execute. The DML trigger is created as an object in the database.
To see the DML trigger listed in Object Explorer, right-click Triggers and select Refresh.
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
From the File menu, click New Query.
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;
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Use events and triggers in Dynamics 365 Business Central - Training
Do you want to know how to access different table and page triggers? Do you also want to learn how to develop by using event-based architecture? If so, this module will explain how to use events and triggers in Microsoft Dynamics 365 Business Central.