Membuat Pemicu DML untuk Menangani Beberapa Baris Data

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceDatabase SQL di Microsoft Fabric

Saat Anda menulis kode untuk pemicu DML, pertimbangkan bahwa pernyataan yang menyebabkan pemicu diaktifkan dapat menjadi satu pernyataan yang memengaruhi beberapa baris data, bukan satu baris. Perilaku ini umum untuk pemicu UPDATE dan DELETE karena pernyataan ini sering memengaruhi beberapa baris. Perilaku ini kurang umum untuk pemicu INSERT karena pernyataan INSERT dasar hanya menambahkan satu baris. Namun, karena pemicu INSERT dapat diaktifkan oleh pernyataan INSERT INTO (table_name) SELECT, penyisipan banyak baris dapat menyebabkan pemanggilan pemicu tunggal.

Pertimbangan baris ganda sangat penting ketika fungsi pemicu DML adalah secara otomatis menghitung ulang nilai ringkasan dari satu tabel dan menyimpan hasilnya di tabel lain untuk penghitungan berkelanjutan.

Catatan

Kami tidak merekomendasikan penggunaan kursor dalam pemicu karena berpotensi mengurangi performa. Untuk merancang pemicu yang memengaruhi beberapa baris, gunakan logika berbasis rowset alih-alih kursor.

Contoh

Pemicu DML dalam contoh berikut dirancang untuk menyimpan jumlah kumulatif dari sebuah kolom di tabel lain dari database sampel AdventureWorks2025.

A. Menyimpan total kumulatif untuk penyisipan satu baris

Versi pertama dari pemicu DML berfungsi dengan baik untuk sisipan satu baris saat baris data dimuat ke dalam tabel PurchaseOrderDetail. Pernyataan INSERT mengaktifkan pemicu DML, dan baris baru dimuat ke dalam tabel yang disisipkan selama durasi eksekusi pemicu. Pernyataan UPDATE membaca LineTotal nilai kolom untuk baris dan menambahkan nilai tersebut ke nilai yang ada di SubTotal kolom dalam PurchaseOrderHeader tabel. Klausa WHERE memastikan bahwa baris yang diperbarui dalam PurchaseOrderDetail tabel cocok dengan PurchaseOrderID baris dalam tabel yang disisipkan .

-- Trigger is valid for single-row inserts.  
USE AdventureWorks2022;  
GO  
CREATE TRIGGER NewPODetail  
ON Purchasing.PurchaseOrderDetail  
AFTER INSERT AS  
   UPDATE PurchaseOrderHeader  
   SET SubTotal = SubTotal + LineTotal  
   FROM inserted  
   WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID ;  

B. Menyimpan total kumulatif untuk penyisipan banyak baris atau satu baris

Untuk sisipan multirow, pemicu DML dalam contoh A mungkin tidak beroperasi dengan benar; ekspresi di sebelah kanan ekspresi penetapan dalam pernyataan UPDATE (SubTotal + LineTotal) hanya dapat berupa satu nilai, bukan daftar nilai. Oleh karena itu, efek pemicu adalah mengambil nilai dari baris tunggal mana pun dalam tabel yang disisipkan dan menambahkan nilai tersebut ke nilai yang ada dalam SubTotal tabel untuk nilai tertentuPurchaseOrderHeader.PurchaseOrderID Operasi ini mungkin tidak memiliki efek yang diharapkan jika satu PurchaseOrderID nilai terjadi lebih dari satu kali dalam tabel yang disisipkan .

Untuk memperbarui PurchaseOrderHeader tabel dengan benar, pemicu harus memungkinkan adanya beberapa baris dalam tabel inserted. Anda dapat melakukan ini dengan menggunakan SUM fungsi yang menghitung total LineTotal untuk sekelompok baris dalam tabel yang disisipkan untuk setiap PurchaseOrderID. Fungsi SUM ini disertakan dalam subkueri berkorelasi ( SELECT pernyataan dalam tanda kurung). Subkueri ini mengembalikan nilai tunggal untuk masing-masing PurchaseOrderID dalam tabel disisipkan yang cocok atau berkorelasi dengan PurchaseOrderID dalam tabel PurchaseOrderHeader.

-- Trigger is valid for multirow and single-row inserts.  
USE AdventureWorks2022;  
GO  
CREATE TRIGGER NewPODetail2  
ON Purchasing.PurchaseOrderDetail  
AFTER INSERT AS  
   UPDATE Purchasing.PurchaseOrderHeader  
   SET SubTotal = SubTotal +   
      (SELECT SUM(LineTotal)  
      FROM inserted  
      WHERE PurchaseOrderHeader.PurchaseOrderID  
       = inserted.PurchaseOrderID)  
   WHERE PurchaseOrderHeader.PurchaseOrderID IN  
      (SELECT PurchaseOrderID FROM inserted);  

Pemicu ini juga berfungsi dengan benar dalam sisipan baris tunggal; jumlah kolom nilai LineTotal merupakan jumlah dari satu baris. Namun, pemicu ini menyebabkan subkueri berkorelasi dan operator IN yang digunakan dalam klausul WHERE memerlukan pemrosesan tambahan dari SQL Server. Ini tidak diperlukan untuk penyisipan satu baris.

C. Menyimpan subtotal yang terus berkembang berdasarkan jenis penyisipan

Anda dapat mengubah pemicu untuk menggunakan metode optimal untuk jumlah baris. Misalnya, fungsi @@ROWCOUNT dapat digunakan dalam logika pemicu untuk membedakan antara sisipan tunggal dan multi-baris.

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

Lihat Juga

Pemicu DML