Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://learn.microsoft.com/en-us/sql/relational-databases/triggers/create-dml-triggers below:

Create DML Triggers - SQL Server

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.

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

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

You can use one of the following methods:

• SQL Server Management Studio
• Transact-SQL

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.

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;
   

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4