Membuat Pemicu DML untuk Menangani Beberapa Baris Data
Berlaku untuk: SQL Server
Azure 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;