Membuat Pemicu DML untuk Menangani Beberapa Baris Data

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance

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 multirow sangat penting ketika fungsi pemicu DML adalah menghitung ulang nilai ringkasan secara otomatis dari satu tabel dan menyimpan hasilnya di tabel lain untuk tinggi yang sedang berlangsung.

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 total kolom yang sedang berjalan di tabel lain dari AdventureWorks2022 database sampel.

J. Menyimpan total yang sedang berjalan untuk sisipan satu baris

Versi pertama pemicu DML berfungsi dengan baik untuk sisipan satu baris saat baris data dimuat ke PurchaseOrderDetail dalam tabel. 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 yang sedang berjalan untuk penyisipan multirow atau baris tunggal

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 SubTotal dalam PurchaseOrderHeader tabel untuk nilai tertentuPurchaseOrderID. 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 peluang beberapa baris dalam tabel yang disisipkan . 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 yang disisipkan yang cocok atau berkorelasi dengan PurchaseOrderID dalam PurchaseOrderHeader tabel.

-- 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 LineTotal kolom nilai adalah jumlah dari satu baris. Namun, dengan pemicu ini, subkueri yang berkorelasi dan IN operator yang digunakan dalam WHERE klausul memerlukan pemrosesan tambahan dari SQL Server. Ini tidak perlu untuk sisipan baris tunggal.

C. Menyimpan total yang sedang berjalan berdasarkan jenis penyisipan

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

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