Menggunakan tabel yang disisipkan dan dihapus

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

Pernyataan pemicu DML menggunakan dua tabel khusus: tabel yang dihapus dan disisipkan . SQL Server secara otomatis membuat dan mengelola tabel ini. Anda dapat menggunakan tabel sementara dan residen memori ini untuk menguji efek modifikasi data tertentu dan untuk mengatur kondisi untuk tindakan pemicu DML. Anda tidak dapat langsung mengubah data dalam tabel atau melakukan operasi bahasa definisi data (DDL) pada tabel, seperti CREATE INDEX.

Memahami tabel yang disisipkan dan dihapus

Dalam pemicu DML, tabel yang disisipkan dan dihapus terutama digunakan untuk melakukan hal berikut:

  • Perluas integritas referensial antara tabel.

  • Sisipkan atau perbarui data dalam tabel dasar yang mendasari sebuah tampilan.

  • Uji kesalahan dan ambil tindakan berdasarkan kesalahan.

  • Temukan perbedaan antara status tabel sebelum dan sesudah modifikasi data dan ambil tindakan berdasarkan perbedaan tersebut.

Tabel yang dihapus menyimpan salinan baris yang terpengaruh dalam tabel pemicu sebelum diubah oleh pernyataan DELETE atau UPDATE (tabel pemicu adalah tabel tempat pemicu DML berjalan). Selama eksekusi pernyataan DELETE atau UPDATE, baris yang terpengaruh terlebih dahulu disalin dari tabel pemicu dan ditransfer ke tabel yang dihapus.

Tabel yang disisipkan menyimpan salinan baris baru atau yang diubah setelah pernyataan INSERT atau UPDATE. Selama eksekusi pernyataan INSERT atau UPDATE, baris baru atau yang diubah dalam tabel pemicu disalin ke tabel yang disisipkan. Baris dalam tabel yang disisipkan adalah salinan baris baru atau yang diperbarui dalam tabel pemicu.

Transaksi pembaruan mirip dengan operasi penghapusan diikuti oleh operasi penyisipan. Selama eksekusi pernyataan UPDATE, urutan peristiwa berikut terjadi:

  1. Baris asli disalin dari tabel pemicu ke tabel yang dihapus.
  2. Tabel pemicu diperbarui dengan nilai baru dari pernyataan UPDATE.
  3. Baris yang diperbarui dalam tabel pemicu disalin ke tabel yang disisipkan.

Ini memungkinkan Anda membandingkan konten baris sebelum pembaruan (dalam tabel yang dihapus) dengan nilai baris baru setelah pembaruan (dalam tabel yang disisipkan).

Saat Anda mengatur kondisi pemicu, gunakan tabel yang disisipkan dan dihapus dengan tepat untuk tindakan yang mengaktifkan pemicu. Meskipun mereferensikan tabel yang dihapus saat menguji INSERT atau tabel yang disisipkan saat menguji DELETE tidak menyebabkan kesalahan apa pun, tabel pengujian pemicu ini tidak berisi baris apa pun dalam kasus ini.

Catatan

Jika tindakan pemicu bergantung pada jumlah baris efek modifikasi data, gunakan pengujian (seperti pemeriksaan @@ROWCOUNT) untuk modifikasi data multirow (INSERT, DELETE, atau UPDATE berdasarkan pernyataan SELECT), dan ambil tindakan yang sesuai. Untuk informasi selengkapnya, lihat Membuat Pemicu DML untuk Menangani Beberapa Baris Data.

SQL Server tidak mengizinkan referensi kolom teks, ntext, atau gambar dalam tabel yang disisipkan dan dihapus untuk pemicu AFTER. Namun, jenis data ini hanya disertakan untuk tujuan kompatibilitas mundur. Penyimpanan yang disukai untuk data besar adalah menggunakan jenis data varchar(max), nvarchar(max), dan varbinary(max ). Pemicu AFTER dan INSTEAD OF mendukung data varchar(max), nvarchar(max), dan varbinary(max) dalam tabel yang disisipkan dan dihapus. Untuk informasi selengkapnya, lihat CREATE TRIGGER (Transact-SQL).

Contoh: Gunakan tabel yang disisipkan dalam pemicu untuk menerapkan aturan bisnis

Karena batasan CHECK hanya dapat mereferensikan kolom tempat batasan tingkat kolom atau tingkat tabel ditentukan, batasan lintas tabel apa pun (dalam hal ini, aturan bisnis) harus didefinisikan sebagai pemicu.

Contoh berikut membuat pemicu DML. Pemicu ini memeriksa untuk memastikan peringkat kredit vendor baik saat ada upaya untuk memasukkan pesanan pembelian baru ke dalam tabel PurchaseOrderHeader. Untuk mendapatkan peringkat kredit vendor yang sesuai dengan pesanan pembelian yang baru saja dimasukkan, Vendor tabel harus direferensikan dan digabungkan dengan tabel yang disisipkan. Jika peringkat kredit terlalu rendah, pesan ditampilkan dan penyisipan tidak dijalankan.

USE AdventureWorks2022;
GO

IF OBJECT_ID('Purchasing.LowCredit', 'TR') IS NOT NULL
    DROP TRIGGER Purchasing.LowCredit;
GO

-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).
CREATE TRIGGER Purchasing.LowCredit
ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
    IF (ROWCOUNT_BIG() = 0)
    RETURN;
    IF EXISTS (SELECT 1
        FROM inserted AS i
        INNER JOIN Purchasing.Vendor AS v
            ON v.BusinessEntityID = i.VendorID
            WHERE v.CreditRating = 5)
BEGIN
    RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
    ROLLBACK;
    RETURN;
END
GO

-- This statement attempts to insert a row into the PurchaseOrderHeader table  
-- for a vendor that has a below average credit rating.  
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.  

INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,
    VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
VALUES (2, 3, 261, 1652, 4, GETDATE(), GETDATE(), 44594.55, 3567.564, 1114.8638);
GO

Gunakan tabel yang disisipkan dan dihapus di ALIH-ALIH pemicu

Tabel yang disisipkan dan dihapus yang diteruskan ke trigger SEBAGAI GANTINYA yang ditentukan pada tabel mengikuti aturan yang sama dengan tabel yang disisipkan dan dihapus yang diteruskan ke trigger AFTER. Format tabel yang disisipkan dan dihapus sama dengan format tabel tempat trigger INSTEAD OF ditetapkan. Setiap kolom dalam tabel yang dimasukkan dan dihapus dipetakan langsung ke sebuah kolom dalam tabel dasar.

Aturan berikut mengenai kapan pernyataan INSERT atau UPDATE yang merujuk tabel dengan pemicu ALIH-ALIH HARUS menyediakan nilai untuk kolom sama seperti jika tabel tidak memiliki pemicu ALIH-ALIH:

  • Nilai tidak dapat ditentukan untuk kolom komputasi atau kolom dengan timestamp.

  • Nilai tidak dapat ditentukan untuk kolom dengan properti IDENTITY, kecuali IDENTITY_INSERT AKTIF untuk tabel tersebut. Ketika IDENTITY_INSERT AKTIF, pernyataan INSERT harus memberikan nilai.

  • Pernyataan INSERT harus menyediakan nilai untuk semua kolom NOT NULL yang tidak memiliki batasan DEFAULT.

  • Untuk kolom apa pun kecuali kolom terhitung, identitas, atau timestamp, nilai bersifat opsional untuk kolom apa pun yang memungkinkan NULL, atau kolom NOT NULL apa pun yang memiliki definisi DEFAULT.

Saat pernyataan INSERT, UPDATE, atau DELETE mereferensikan tampilan yang memiliki trigger INSTEAD OF, Mesin Database memanggil trigger tersebut dan bukannya mengambil tindakan langsung terhadap tabel mana pun. Pemicu harus menggunakan informasi yang disajikan dalam tabel yang disisipkan dan dihapus untuk membangun pernyataan apa pun yang diperlukan untuk menerapkan tindakan yang diminta dalam tabel dasar, bahkan ketika format informasi dalam tabel yang disisipkan dan dihapus yang dibangun untuk tampilan berbeda dari format data dalam tabel dasar.

Format tabel yang disisipkan dan dihapus yang diberikan kepada pemicu INSTEAD OF yang ditetapkan pada tampilan sesuai dengan daftar elemen SELECT dari pernyataan SELECT yang ditentukan untuk tampilan. Contohnya:

USE AdventureWorks2022;  
GO  
CREATE VIEW dbo.EmployeeNames (BusinessEntityID, LName, FName)  
AS  
SELECT e.BusinessEntityID, p.LastName, p.FirstName  
FROM HumanResources.Employee AS e   
JOIN Person.Person AS p  
ON e.BusinessEntityID = p.BusinessEntityID;

Hasil yang ditetapkan untuk tampilan ini memiliki tiga kolom: kolom int dan dua kolom nvarchar . Tabel yang disisipkan dan dihapus diteruskan ke pemicu INSTEAD OF yang ditentukan pada tampilan juga memiliki kolom int bernama BusinessEntityID, kolom nvarchar bernama LName, dan kolom nvarchar bernama FName.

Daftar pilihan dalam sebuah tampilan juga dapat berisi ekspresi yang tidak secara langsung dipetakan ke satu kolom tabel dasar. Beberapa ekspresi tampilan, seperti pemanggilan konstanta atau fungsi, mungkin tidak mereferensikan kolom apa pun dan dapat diabaikan. Ekspresi kompleks dapat mereferensikan beberapa kolom, namun tabel yang disisipkan dan dihapus hanya memiliki satu nilai untuk setiap baris yang disisipkan. Masalah yang sama berlaku untuk ekspresi sederhana dalam tampilan jika mereferensikan kolom komputasi yang memiliki ekspresi kompleks. Trigger INSTEAD OF pada tampilan harus menangani jenis ekspresi ini.

Pertimbangan performa

Karena tabel yang disisipkan dan dihapus bersifat virtual dan berada di dalam memori, properti seperti statistik atau indeks tidak tersedia. Meskipun beberapa informasi kardinalitas terekspos dari tabel ini, Anda harus berhati-hati saat mempertimbangkan jumlah baris yang akan disimpan sementara di sana. Menyisipkan sejumlah besar baris dalam tabel ini dan mengkueri atau menggabungkannya dengan tabel lain dapat mengakibatkan rencana kueri sub-optimal dan eksekusi kueri yang lambat. Pastikan untuk merancang dan menguji aplikasi Anda dengan cermat untuk memenuhi kebutuhan performa kueri Anda.

Langkah berikutnya

Untuk informasi selengkapnya, lihat gambaran umum Pemicu DML.

  • Last updated on