Menggunakan tabel yang disisipkan dan dihapus

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

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 antar tabel.

  • Sisipkan atau perbarui data dalam tabel dasar yang mendasar 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 untuk vendor baik ketika upaya dilakukan untuk memasukkan pesanan pembelian baru ke PurchaseOrderHeader dalam tabel. 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   
           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 TRANSACTION;  
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 diteruskan ke ALIH-ALIH pemicu yang ditentukan pada tabel mengikuti aturan yang sama dengan tabel yang disisipkan dan dihapus yang diteruskan ke pemicu AFTER. Format tabel yang disisipkan dan dihapus sama dengan format tabel tempat pemicu ALIH-ALIH ditentukan. Setiap kolom dalam tabel yang disisipkan dan dihapus dipetakan langsung ke 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 atau kolom komputasi dengan jenis data tanda waktu.

  • 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 komputasi, identitas, atau tanda waktu, 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 pemicu ALIH-ALIH, Mesin Database memanggil pemicu alih-alih mengambil tindakan langsung terhadap tabel apa 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 diteruskan ke pemicu ALIH-ALIH ditentukan pada tampilan cocok dengan daftar pemilihan 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 ALIH-ALIH yang ditentukan pada tampilan juga memiliki kolom int bernama BusinessEntityID, kolom nvarchar bernama LName, dan kolom nvarchar bernama FName.

Daftar pilih tampilan juga dapat berisi ekspresi yang tidak 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. ALIH-ALIH pemicu pada tampilan harus menangani jenis ekspresi ini.

Pertimbangan performa

Karena tabel yang disisipkan dan dihapus bersifat virtual, tabel residen 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-optimial 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.